I’m sorry, the document you provided appears incomplete, with tables, code, and subsequent sections (such as Anti joins) missing. To perform an accurate and complete rewrite, please provide the full article.
Code: The EXISTS subquery checks each customer to see if at least one order exceeding $100 exists. The SELECT 1 is standard practice — EXISTS simply needs to know whether any row is returned, not the actual data inside it.
SELECT
c.customer_id,
c.customer_name
FROM online_store_customers c
WHERE EXISTS (
SELECT 1
FROM online_store_orders o
WHERE o.customer_id = c.customer_id
AND o.amount > 100
);
If an INNER JOIN had been used instead, customer 1 would show up twice because two orders meet the condition. EXISTS ensures customer 1 appears only once.
// Output
| customer_id | customer_name |
|---|---|
| 1 | Alice Johnson |
| 2 | Bob Smith |
| 3 | Carol Williams |
| … | … |
| 9 | Ivy Taylor |
# Anti Joins
An anti join retrieves records from the first (left) table where no corresponding entry exists in the second (right) table. It’s the opposite of a semi join.
Two approaches in SQL:
LEFT JOIN ... WHERE right_table.col IS NULLWHERE NOT EXISTS (SELECT 1 FROM ...)
Both approaches yield identical results. In modern PostgreSQL, NOT EXISTS often generates a more efficient query plan and reads more intuitively. The LEFT JOIN combined with IS NULL is an older technique and remains handy when you need to reference columns from the right table for rows that don’t match.
// Example: Free Users Who Didn’t Make Any April Calls
The task: identify free users who had zero calls during April 2020.
Data: Sample data from rc_calls and rc_users:
| user_id | call_id | call_date |
|---|---|---|
| 1218 | 0 | 2020-04-19 01:06:00 |
| 1554 | 1 | 2020-03-01 16:51:00 |
| 1857 | 2 | 2020-03-29 07:06:00 |
| 1525 | 3 | 2020-03-07 02:01:00 |
| … | … | … |
| 1910 | 39 | 2020-03-11 08:33:00 |
| user_id | status | company_id |
|---|---|---|
| 1218 | free | 1 |
| 1554 | inactive | 1 |
| 1857 | free | 2 |
| … | … | … |
| 1884 | free | 1 |
Code: Notice the date filter is placed in the ON clause, not in WHERE. That’s the critical detail that makes this work as an anti join. If the date filter were moved to WHERE, it would discard the NULL-producing rows from the LEFT JOIN, effectively reverting the query to an INNER JOIN. By keeping the filter in the ON clause, free users without any qualifying April call still produce a row (with NULLs on the right side), and the IS NULL check then retains only those rows.
SELECT DISTINCT u.user_id
FROM rc_users u
LEFT JOIN rc_calls c
ON u.user_id = c.user_id
AND c.call_date BETWEEN '2020-04-01' AND '2020-04-30'
WHERE u.status="free"
AND c.user_id IS NULL;
// Output
# Conclusion
These three join types address scenarios where INNER JOIN and LEFT JOIN fall short or produce incorrect results:
- LATERAL lets you invoke set-returning functions for each individual row within the FROM clause.
EXISTS lets you find “rows that have a match” without the duplicate rows that INNER JOIN introduces.
NOT EXISTS or LEFT JOIN + IS NULL lets you find “rows with no match” cleanly and reliably.
Keep this simple guideline in mind. When INNER JOIN creates unwanted duplicate rows, switch to EXISTS. When you need rows with no corresponding match, reach for NOT EXISTS or LEFT JOIN + IS NULL. When a subquery in FROM must reference columns from the outer query, use LATERAL.
Apply these techniques to real SQL interview questions and the syntax will become second nature.
Nate Rosidi is a data scientist specializing in product strategy. He’s also an adjunct professor teaching analytics and the founder of StrataScratch, a platform designed to help data scientists prep for interviews featuring real questions from top companies. Nate covers the latest career market trends, offers interview tips, shares data science projects, and writes extensively about SQL.
