Week 01
Self Joins, Anti-Joins & Existence Checks
What to Focus On
Some questions need two rows from the same table: an employee and their manager, or two customers from the same city. Others ask for a missing relationship: a customer with no orders or a product with no sales.
You already know the join syntax. Here the work is choosing what each side represents, avoiding duplicate pairs, and handling NULL correctly when checking for a missing match.
We will use an employee table first, then customers and orders. Pay particular attention to the difference between NOT EXISTS and NOT IN; a nullable column can change the result completely.
employees
| id | name | manager_id | salary |
|---|---|---|---|
| 1 | Anjali | NULL | 190000 |
| 2 | Rohit | 1 | 145000 |
| 3 | Meera | 1 | 120000 |
| 4 | Vikram | 2 | 160000 |
| 5 | Farhan | 2 | 95000 |
| 6 | Divya | 3 | 120000 |
| 7 | Sneha | 4 | 175000 |
manager_id is not a name, it is the id of another row in this same table. Anjali is the founder, so hers is NULL, SQL's way of saying "no value here".
Use the Same Table in Two Roles
To compare an employee's salary with their manager's, we need both records on the same result row. A self join uses employees twice: e for the employee and m for the manager. These aliases describe two roles in the query; they do not create copies of the stored table.
Step 1: just pair each person with their boss
Check the relationship first: e.manager_id should match the manager's m.id.
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;| employee | manager |
|---|---|
| Rohit | Anjali |
| Meera | Anjali |
| Vikram | Rohit |
| Farhan | Rohit |
| Divya | Meera |
| Sneha | Vikram |
7 employees went in, 6 rows came out. Anjali is missing, and that is not a bug.
Step 2: pull the salaries onto the same row
The pairing works, so add the two numbers you want to compare.
SELECT e.name AS employee, e.salary AS emp_salary,
m.name AS manager, m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;| employee | emp_salary | manager | mgr_salary |
|---|---|---|---|
| Rohit | 145000 | Anjali | 190000 |
| Meera | 120000 | Anjali | 190000 |
| Vikram | 160000 | Rohit | 145000 |
| Farhan | 95000 | Rohit | 145000 |
| Divya | 120000 | Meera | 120000 |
| Sneha | 175000 | Vikram | 160000 |
The comparison is now a plain left versus right check on one row. The two salaries are now available for comparison.
Step 3: keep only the rows you were asked for
Now filter on the two salary columns.
SELECT e.name AS employee, e.salary AS emp_salary,
m.name AS manager, m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.name;| employee | emp_salary | manager | mgr_salary |
|---|---|---|---|
| Sneha | 175000 | Vikram | 160000 |
| Vikram | 160000 | Rohit | 145000 |
Two names, sorted alphabetically. The requested output is alphabetical, so we order by employee name.
Read the query line by line.
FROM employees AS erefers to the employee whose salary we are checking.JOIN employees AS mrefers to the manager's row in the same table.ON m.id = e.manager_idattaches the manager row whoseidmatches.WHERE e.salary > m.salarykeeps employees who earn more than their manager.SELECT ...picks the four columns and renames them withAS.ORDER BY e.namesorts the survivors before the judge sees them.
Two people are missing, and both reasons matter. Divya earns 120000 and her manager Meera also earns 120000. > is strict, so equal is not more and Divya is out. Had the statement said "at least as much as", >= would bring her back.
Anjali produces no row at all. Her manager_id is NULL, and a comparison against NULL is never true, so the ON line finds nothing and the JOIN drops her. Here that is correct, she has no manager to out-earn.
- employees as e and m
- match ON e.manager_id = m.id
- compare salaries
- keep pairs where e.salary
- m.salary
Using the employees table above, a student writes WHERE e.salary >= m.salary for the ask "employees earning strictly more than their manager". What extra name appears?
When the question says "show everyone"
Sometimes the ask is different: "list every employee with their manager's name, print No Manager when there is none". Now Anjali must appear. Swap the JOIN for a LEFT JOIN, which keeps every left row and pads the right side with NULL.
Two small tools help. COALESCE(a, b) returns a unless it is NULL, in which case it returns b, so it replaces a blank with a label. CASE WHEN ... THEN ... ELSE ... END is SQL's if else, one value per row.
SELECT e.name AS EmployeeName,
COALESCE(m.name, 'No Manager') AS ManagerName,
CASE WHEN m.id IS NOT NULL AND e.salary > m.salary
THEN 'Yes' ELSE 'No' END AS EarnsMore
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;| EmployeeName | ManagerName | EarnsMore |
|---|---|---|
| Anjali | No Manager | No |
| Rohit | Anjali | No |
| Meera | Anjali | No |
| Vikram | Rohit | Yes |
| Farhan | Rohit | No |
| Divya | Meera | No |
| Sneha | Vikram | Yes |
All 7 rows survive, and Anjali's blank manager is now a readable label.
Note the m.id IS NOT NULL guard inside the CASE. Without it Anjali's comparison is 190000 > NULL, which is unknown, so the ELSE branch fires and she gets No. Right answer, wrong reason, and the variant asking for "Not Applicable" then fails.
What is an Anti-Join?
For a question like "customers who never ordered", we need to retain customers for whom no matching order exists.
An anti-join is exactly that: the rows of A that have no partner in B. "Customers who never ordered." "Products never sold."
In SQLite, write this using NOT EXISTS or LEFT JOIN ... IS NULL. You may also see NOT IN, but its behaviour differs when the subquery contains NULL.
customers
| id | name | city |
|---|---|---|
| 1 | Asha | Bengaluru |
| 2 | Bilal | Hyderabad |
| 3 | Chitra | Pune |
| 4 | Deepak | Bengaluru |
| 5 | Nithya | Chennai |
orders
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 420 |
| 102 | 1 | 610 |
| 103 | 1 | 275 |
| 104 | NULL | 150 |
| 105 | 3 | 330 |
| 106 | 5 | 480 |
Order 104 is a guest checkout, so its customer_id is NULL. Keep it in mind when comparing NOT IN with NOT EXISTS.
The Pattern
- Decide the direction. Whichever table's rows must survive even when nothing matches goes in
FROM. LEFT JOINthe other table on the key.- Filter with
WHERE <right table>.<never null column> IS NULL, which keeps exactly the padded rows. - Choose that column carefully: the primary key is safe, a nullable column like
amountis not. - Aggregate afterwards if the ask is "how many", then
ORDER BYexactly as stated.
Step 1: inspect the join before filtering. Check which rows the final filter should retain.
SELECT c.id, c.name, o.id AS o_id, o.customer_id AS o_cust
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;| c.id | c.name | o_id | o_cust |
|---|---|---|---|
| 1 | Asha | 101 | 1 |
| 1 | Asha | 102 | 1 |
| 1 | Asha | 103 | 1 |
| 2 | Bilal | NULL | NULL |
| 3 | Chitra | 105 | 3 |
| 4 | Deepak | NULL | NULL |
| 5 | Nithya | 106 | 5 |
Bilal and Deepak are the padded rows, where LEFT JOIN had nothing to attach and filled in NULL. They are the answer, already visible. Asha appears three times because she placed three orders, and order 104 appears nowhere, since a NULL key matches no customer.
Step 2: keep only the padded rows.
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;| name |
|---|
| Bilal |
| Deepak |
One column, two rows, alphabetical. That is the anti-join.
IS NULL is a separate operator on purpose: you cannot write o.id = NULL, because NULL is not a value you can be equal to.
Dry run of the same query, step by step.
| step | what runs | rows alive |
|---|---|---|
| FROM + LEFT JOIN | pair each customer with her orders, pad the unmatched | 7 |
| WHERE o.id IS NULL | keep only the padded rows | 2 |
| SELECT c.name | project one column | 2 |
| ORDER BY c.name | Bilal, then Deepak | 2 |
The join is the widest point of the query, and the filter does all the work, only because that one column makes the padded rows recognisable.
Check the output shape. This query returns one name column, ordered alphabetically:
name
Bilal
DeepakRemove debugging columns before submitting, and follow the ordering and formatting the question specifies.
What the wrong column looks like. Suppose a payment is pending, so order 105 has amount stored as NULL. A student tests the wrong column:
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount IS NULL -- wrong column
ORDER BY c.name;| wrong output | correct output |
|---|---|
| Bilal | Bilal |
| Chitra | Deepak |
| Deepak |
Chitra has an order, but its missing amount causes the filter to include her. A sample with all amounts filled will not expose this mistake. Test a column guaranteed to be non-null on a real match, usually the child table's primary key.
Which column is guaranteed non-null on every real order row, making it a reliable way to identify the padded rows?
Query Templates
SQL
-- 1. Self join: compare a row against its parent row in the same table.
SELECT e.name AS employee
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.name;
-- 2. LEFT self join: every employee, including the ones with no manager.
SELECT e.name AS EmployeeName,
COALESCE(m.name, 'No Manager') AS ManagerName,
CASE WHEN m.id IS NOT NULL AND e.salary > m.salary
THEN 'Yes' ELSE 'No' END AS EarnsMore
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;
-- 3. Anti-join, the safe form: rows of A with no partner in B.
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;
-- 4. Semi-join: rows of A that DO have a partner, no duplicates, no DISTINCT needed.
SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.name;Variations
Variation 1 - Anti-join three ways
The NOT EXISTS subquery checks whether a matching order exists for the current customer. This is the logical check; the database may optimise how it performs it.
-- (b) NOT EXISTS: correct, NULL safe
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.name;
-- (c) NOT IN: WRONG whenever the subquery column can be NULL
SELECT c.name FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders)
ORDER BY c.name;Form (a) is the LEFT JOIN ... IS NULL you already wrote. Run all three on the tables above:
| form | output |
|---|---|
| LEFT JOIN, o.id IS NULL | Bilal, Deepak |
| NOT EXISTS | Bilal, Deepak |
| NOT IN | (no rows at all) |
Two forms agree, one returns an empty file. The NOT IN version fails because the list contains NULL.
Why NOT IN collapses. The subquery produces (1, 1, 1, NULL, 3, 5). Ask SQL whether Bilal qualifies: is 2 NOT IN (1, 1, 1, NULL, 3, 5)?
SQL cannot say yes. That NULL is an unknown value, and for all SQL knows it is 2. So the answer is not true and not false, it is unknown. WHERE keeps only true rows, so every customer is thrown away.
Delete order 104 and the same query suddenly returns Bilal and Deepak. Test nullable keys explicitly; a sample without them can make the two forms look equivalent.
IMPNULL check: If you use NOT IN here, exclude NULLs in the subquery:
NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). NOT EXISTS expresses the missing-match question directly. With plain IN, known matches remain true even if the list also contains NULL.
Variation 2 - Semi-join with EXISTS
The mirror image: rows of A that do have a partner. EXISTS stops at the first matching order, so Asha comes out once even though she has three orders.
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.name;| EXISTS output (correct) | plain JOIN output |
|---|---|
| Asha | Asha |
| Chitra | Asha |
| Nithya | Asha |
| Chitra | |
| Nithya |
The plain JOIN returns five rows because Asha has three orders. EXISTS returns each qualifying customer row once. DISTINCT on names gives the expected result here, but could merge different customers with the same name.
Variation 3 - Pairs from one table with a.id < b.id
"List every pair of customers in the same city." Join the table to itself on city and control the pairing with the id.
SELECT a.name AS customer_1, b.name AS customer_2, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;| customer_1 | customer_2 | city |
|---|---|---|
| Asha | Deepak | Bengaluru |
Bengaluru has two customers, so exactly one pair comes out.
See what the wrong conditions produce:
| condition on the ON line | what you get |
|---|---|
a.id < b.id | Asha, Deepak. One row, correct |
a.id <> b.id | Asha, Deepak and Deepak, Asha. Same pair twice |
| nothing at all | 7 rows, including Asha paired with herself |
< picks one ordering of each pair and prints it once. <> removes only the self pairs, not the mirror image.
Variation 4 - Consecutive rows by id
When ids are gap free, "customers with three consecutive orders" is a triple self join on id + 1 and id + 2.
SELECT DISTINCT o1.customer_id AS customer_id
FROM orders o1
JOIN orders o2 ON o2.id = o1.id + 1 AND o2.customer_id = o1.customer_id
JOIN orders o3 ON o3.id = o1.id + 2 AND o3.customer_id = o1.customer_id;| customer_id |
|---|
| 1 |
Orders 101, 102 and 103 all belong to Asha, so only customer 1 qualifies.
DISTINCT is not optional. A run of five consecutive orders gives three starting positions for the same customer, so it prints three times. If the ids have gaps this approach is simply wrong, and you need LAG and LEAD, which come in Week 2.
Variation 5 - "Every customer here has ordered"
"Which cities have no inactive customer?" This is a double anti-join, with two absence checks: a city qualifies when there is no customer in it for whom there is no order.
SELECT DISTINCT c.city
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM customers c2
WHERE c2.city = c.city
AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c2.id))
ORDER BY c.city;| city |
|---|
| Chennai |
| Pune |
Bengaluru is out because of Deepak, Hyderabad because of Bilal. Chennai and Pune have one customer each, and both ordered.
The counting version says it differently. GROUP BY collapses rows into one row per group, and HAVING filters those groups the way WHERE filters rows.
SELECT c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.city
HAVING COUNT(DISTINCT CASE WHEN o.id IS NOT NULL THEN c.id END) = COUNT(DISTINCT c.id)
ORDER BY c.city;Same two cities, comparing "customers here who ordered" against "customers here". Choose the version you can explain clearly. NOT EXISTS follows the wording closely here.
Order 104 has customer_id set to NULL. What does SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) return?
Common Mistakes
- No aliases in a self join. Both sides expose the same column names. Give each role an alias and qualify the columns so the relationship is clear.
- NOT IN over nullable values. A NULL in the inner list prevents the condition from being true for any customer here. Use NOT EXISTS, or exclude NULLs deliberately when NOT IN fits the question.
- Testing a nullable data column.
WHERE o.amount IS NULLalso includes customers with an order whose amount is missing. Testo.id, which cannot be NULL on a real order. - Including equal salaries. “Strictly more” needs
>. Using>=adds Divya even though she earns the same as her manager. - Losing employees with no matching manager. An inner join removes both NULL manager ids and ids that point to no existing row. Use LEFT JOIN when these employees must remain.
- Returning each pair twice.
a.id <> b.idremoves self-pairs but keeps both directions.a.id < b.idreturns each unordered pair once. - Repeating customers in a long run. Five consecutive orders have three starting positions for a three-order run. Use DISTINCT when the output needs each customer only once.
- Assuming ids define sequence. Joining on id + 1 is suitable only when consecutive ids are what the question means. For events ordered by time, define the order explicitly and use the window-function approach covered later.
Interview Notes
When would you use a self join? When two rows of the same table play different roles, such as an employee and their manager. Explain the roles before writing the matching condition.
NOT IN or NOT EXISTS? For a missing relationship, NOT EXISTS is a clear default and handles nullable inner keys naturally. NOT IN can also be correct when NULLs are ruled out. Neither syntax is automatically faster; performance depends on the plan and available indexes.
Is LEFT JOIN ... IS NULL the same as NOT EXISTS? They express the same absence check when they use the same matching condition and the IS NULL test identifies only unmatched rows. A right-table primary key is a reliable column for that test.
EXISTS or IN? IN checks membership in the subquery result; EXISTS checks whether the subquery returns any row. Choose the form that matches the question. The engine may optimise either form, so do not assume IN always builds a full list or EXISTS is always faster.
How do you find duplicates? Group by the columns that define a duplicate and use HAVING COUNT(*) > 1. To show pairs of duplicate records, self join on those columns and use a.id < b.id to avoid mirror pairs.
How would you list employees with no reports?
Use an anti-join against the same table: FROM employees e LEFT JOIN employees r ON r.manager_id = e.id WHERE r.id IS NULL. The result here is Divya, Farhan and Sneha.
How do you find cities where every customer has ordered? Look for cities with no customer who has no order. Double NOT EXISTS expresses that directly. The count comparison works too, provided multiple orders do not inflate the customer count.
Quick Test
Check what you learned
orders.customer_id is nullable. Which query correctly lists the customers with no orders?