A semi join keeps rows that have a match; an anti join keeps rows that have no match. Neither returns columns from the other table, and neither duplicates rows.

Semi join: customers who ordered

SQL

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders)
ORDER BY c.name;

Output

name
------
Ananya
Imran
Rahul
Sneha

name
------
Ananya
Imran
Rahul
Sneha

Compare with a plain join, which returns a customer once per order:

SQL

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name;

Output

name
------
Ananya
Ananya
Ananya
Imran
Rahul
Rahul
Sneha
Sneha

Anti join: customers who never ordered

SQL

-- 1. NOT EXISTS (recommended)
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 2. LEFT JOIN … IS NULL
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 3. NOT IN (only safe if the subquery can never return NULL)
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Output

name
-----
Kavya

name
-----
Kavya

name
-----
Kavya

SQL

-- products never sold
SELECT p.name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

Output

name
-------
Monitor

The NOT IN + NULL trap, for real

SQL

-- departments that no employee belongs to…
SELECT name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);          -- Dev's NULL dept_id → empty!

SELECT name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

Output

name
----

name
---------
Marketing

Form	Handles NULL	Duplicates	Notes
`EXISTS` / `NOT EXISTS`	Yes	Never	Clearest intent; optimizers turn it into a semi/anti join
`IN (subquery)`	Yes	Never	Fine for semi joins
`NOT IN (subquery)`	No	Never	Returns nothing if the subquery has a `NULL`
`LEFT JOIN … IS NULL`	Yes	Never (anti)	Common, portable; test a non-nullable column
`JOIN` + `DISTINCT`	Yes	Removed afterwards	Extra work; avoid for existence checks

EXISTS vs IN — which is faster?

Find customers who have never placed an order.