What is a semi-join and anti-join? Why are they needed and where are they applied?

Semi-join is an operation in relational databases that returns rows from the first table for which matching rows exist in the second table, but does not return data from the second table. In simpler terms, semi-join checks for the existence of matches but does not combine data.

Anti-join is the opposite of semi-join; it returns rows from the first table for which no matching rows exist in the second table.

Why are they needed?

• Semi-join is useful when you need to verify the existence of related records without pulling data from the second table, saving resources.
• Anti-join is used to find records that have no links, for example, to find "orphaned" records or to exclude related data.

Where to apply?

• In analytics for filtering data based on the presence or absence of links.
• When optimizing queries to reduce the volume of data transferred.

Example in SQL:

-- Semi-join: select customers who have orders
SELECT DISTINCT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Anti-join: select customers without orders
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);