SQL Joins Interview Questions Explained
Join questions in interviews are rarely about syntax. They test whether you can predict how many rows come back. These nine questions, each answered with a query on three million real New York taxi trips, cover anti-joins, ON versus WHERE, NOT IN with NULLs, fan-out, self-joins, calendars and full outer joins.
GloryTecks Team
Training & Placement Faculty
Last updated: 8 October 2026
Most join questions in an interview have one hidden question underneath: how many rows will this return? Writing JOIN ... ON is easy. Predicting that a filter turns an outer join into an inner one, that a NULL makes NOT IN return nothing, or that a duplicate key doubles a total is what the interviewer is checking. These SQL joins interview questions are answered with real queries on New York taxi data so you can see each trap happen.
The data is the New York City TLC yellow taxi trips for January 2024, about three million rows, plus the 265-row taxi zone lookup table, both from the TLC's trip record page (NYC TLC: trip record data). Each trip has a pickup and drop-off zone ID; the lookup maps IDs to zone and borough names. The queries run in DuckDB, which reads the files directly.
import duckdb
db = duckdb.connect()
db.execute("CREATE TABLE zones AS SELECT * FROM 'taxi_zone_lookup.csv'") # 265 NYC taxi zones
db.execute("""CREATE TABLE trips AS SELECT * FROM 'yellow_tripdata_2024-01.parquet'
WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01'""")
def ask(title, sql):
print(f"-- {title}")
for row in db.execute(sql).fetchall():
print(" ", *row)
ask("Q1 zones with no pickups in January 2024 (LEFT JOIN ... IS NULL)", """
SELECT z.LocationID, z.Zone FROM zones z
LEFT JOIN trips t ON t.PULocationID = z.LocationID
WHERE t.PULocationID IS NULL ORDER BY z.LocationID""")
ask("Q2 every zone with its count of trips over 50 miles: condition in ON vs in WHERE", """
SELECT (SELECT COUNT(*) FROM (SELECT z.LocationID, COUNT(t.PULocationID) FROM zones z
LEFT JOIN trips t ON t.PULocationID = z.LocationID AND t.trip_distance > 50
GROUP BY z.LocationID)) AS zones_with_filter_in_on,
(SELECT COUNT(*) FROM (SELECT z.LocationID, COUNT(t.PULocationID) FROM zones z
LEFT JOIN trips t ON t.PULocationID = z.LocationID
WHERE t.trip_distance > 50 GROUP BY z.LocationID)) AS zones_with_filter_in_where""")
ask("Q3 passenger counts from 0 to 12 that never occur: NOT IN vs NOT EXISTS", """
SELECT 'NOT IN' AS method, COUNT(*) AS values_found, LIST(n) AS values
FROM range(0, 13) r(n) WHERE n NOT IN (SELECT passenger_count FROM trips)
UNION ALL
SELECT 'NOT EXISTS', COUNT(*), LIST(n ORDER BY n)
FROM range(0, 13) r(n) WHERE NOT EXISTS (SELECT 1 FROM trips WHERE passenger_count = n)""")
ask(" why: passenger_count has NULLs", "SELECT COUNT(*) - COUNT(passenger_count) AS null_passenger_counts FROM trips")
ask("Q4 a join on a key that is not unique: Corona pickups joined to zones by name", """
SELECT (SELECT COUNT(*) FROM trips t JOIN zones z ON z.LocationID = t.PULocationID WHERE z.Zone = 'Corona') AS by_id,
(SELECT COUNT(*) FROM trips t JOIN zones z ON z.LocationID = t.PULocationID
JOIN zones z2 ON z2.Zone = z.Zone WHERE z.Zone = 'Corona') AS after_join_by_name""")
ask("Q5 self-join: zone names shared by different IDs", """
SELECT a.Zone, a.LocationID, b.LocationID FROM zones a
JOIN zones b ON a.Zone = b.Zone AND a.LocationID < b.LocationID ORDER BY a.LocationID, b.LocationID""")
ask("Q6 the same table joined twice: busiest routes between different zones", """
SELECT p.Zone AS pickup, d.Zone AS dropoff, COUNT(*) AS trips FROM trips t
JOIN zones p ON p.LocationID = t.PULocationID
JOIN zones d ON d.LocationID = t.DOLocationID
WHERE t.PULocationID <> t.DOLocationID GROUP BY ALL ORDER BY trips DESC LIMIT 3""")
ask("Q7 CROSS JOIN a calendar to count the days with zero pickups", """
WITH calendar AS (SELECT DATE '2024-01-01' + CAST(i AS INTEGER) AS day FROM range(0, 31) r(i)),
parks AS (SELECT LocationID, Zone FROM zones WHERE LocationID IN (8, 120, 240)),
daily AS (SELECT PULocationID, CAST(tpep_pickup_datetime AS DATE) AS day, COUNT(*) AS n FROM trips GROUP BY ALL)
SELECT p.Zone, COUNT(*) AS days, COUNT(*) FILTER (WHERE d.n IS NULL) AS days_without_pickups
FROM calendar c CROSS JOIN parks p
LEFT JOIN daily d ON d.PULocationID = p.LocationID AND d.day = c.day
GROUP BY p.Zone ORDER BY p.Zone""")
ask("Q8 zones with at least one trip over 50 miles: JOIN vs EXISTS", """
SELECT (SELECT COUNT(*) FROM zones z JOIN trips t ON t.PULocationID = z.LocationID AND t.trip_distance > 50) AS join_rows,
(SELECT COUNT(*) FROM zones z WHERE EXISTS
(SELECT 1 FROM trips t WHERE t.PULocationID = z.LocationID AND t.trip_distance > 50)) AS exists_rows""")
ask("Q9 FULL OUTER JOIN: pickup zones seen in only one of January 2024 and January 2025", """
WITH a AS (SELECT DISTINCT PULocationID AS id FROM trips),
b AS (SELECT DISTINCT PULocationID AS id FROM 'yellow_tripdata_2025-01.parquet'
WHERE tpep_pickup_datetime >= '2025-01-01' AND tpep_pickup_datetime < '2025-02-01')
SELECT COALESCE(a.id, b.id) AS zone_id, a.id IS NOT NULL AS in_2024, b.id IS NOT NULL AS in_2025
FROM a FULL OUTER JOIN b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL ORDER BY zone_id""")-- Q1 zones with no pickups in January 2024 (LEFT JOIN ... IS NULL)
5 Arden Heights
99 Freshkills Park
103 Governor's Island/Ellis Island/Liberty Island
104 Governor's Island/Ellis Island/Liberty Island
110 Great Kills Park
-- Q2 every zone with its count of trips over 50 miles: condition in ON vs in WHERE
265 63
-- Q3 passenger counts from 0 to 12 that never occur: NOT IN vs NOT EXISTS
NOT IN 0 None
NOT EXISTS 3 [10, 11, 12]
-- why: passenger_count has NULLs
140162
-- Q4 a join on a key that is not unique: Corona pickups joined to zones by name
280 560
-- Q5 self-join: zone names shared by different IDs
Corona 56 57
Governor's Island/Ellis Island/Liberty Island 103 104
Governor's Island/Ellis Island/Liberty Island 103 105
Governor's Island/Ellis Island/Liberty Island 104 105
-- Q6 the same table joined twice: busiest routes between different zones
Upper East Side South Upper East Side North 21883
Upper East Side North Upper East Side South 19402
Midtown Center Upper East Side South 10275
-- Q7 CROSS JOIN a calendar to count the days with zero pickups
Astoria Park 31 21
Highbridge Park 31 18
Van Cortlandt Park 31 17
-- Q8 zones with at least one trip over 50 miles: JOIN vs EXISTS
412 63
-- Q9 FULL OUTER JOIN: pickup zones seen in only one of January 2024 and January 2025
5 False True
99 False True
105 True FalseQ1. How do you find rows in one table with no match in another?
Use an anti-join: LEFT JOIN and keep the rows where the right side's key IS NULL, or write NOT EXISTS. Five zones had no pickups at all in January 2024, among them Arden Heights, Freshkills Park and Great Kills Park. Test the right table's join key for NULL, not any column: a nullable column could be NULL in a matched row too.
Q2. In a LEFT JOIN, what is the difference between a condition in ON and one in WHERE?
The ON condition decides which right-hand rows match, before the join keeps the unmatched left rows. The WHERE condition filters the joined result afterwards (PostgreSQL: table expressions). Asking for every zone with its number of trips over 50 miles, the condition in ON kept all 265 zones, most with a count of zero. Moved to WHERE, it removed every zone with no such trip and left 63: the LEFT JOIN had quietly become an inner join.
The trap to name: any WHERE test on a right-hand column, other than IS NULL, cancels a LEFT JOIN.
Q3. Why can NOT IN return no rows when you expect some?
Because of NULL. x NOT IN (list) is true only if x differs from every value. If the list contains a NULL, the comparison with that NULL is unknown, and the whole NOT IN becomes NULL rather than true (PostgreSQL: subquery expressions). Checking which passenger counts from 0 to 12 never occur, NOT IN found none, because 140,162 trips have no passenger count. NOT EXISTS correctly returned 10, 11 and 12. Anti-joins behave like NOT EXISTS and ignore NULLs on the right (DuckDB: joins).
What to say: prefer NOT EXISTS, or filter NULLs out of the subquery.
Q4. Why did my total go up after adding a join?
The join key was not unique on one side, so each row matched more than once: a fan-out. The zone lookup has two IDs, 56 and 57, both named Corona. Joining Corona's 280 pickups back to the lookup by name, as a report keyed on zone names would, matched each trip twice and produced 560. Any SUM over that result is doubled too.
How to check in an interview: before joining, ask whether the key is unique on the side you expect to be one-to-one, and compare row counts before and after the join.
Q5. When would you join a table to itself?
To compare rows of the same table: an employee with their manager, an event with the previous event, or duplicates. Joining the zone table to itself on name, with a.LocationID < b.LocationID so each pair appears once and no zone pairs with itself, showed the four ID pairs behind duplicated names: Corona, and the three Governor's Island / Ellis Island / Liberty Island IDs. That is also the explanation for Q4.
Q6. How do you join the same lookup table twice?
Give it two aliases, one per role. Trips have a pickup zone and a drop-off zone, so the zone table is joined once as p and once as d. The busiest route between two different zones was Upper East Side South to Upper East Side North, with 21,883 trips, and the reverse direction came second.
Q7. What is a CROSS JOIN useful for?
Producing every combination, most often a calendar against a list of entities, so that missing combinations show up as zeros instead of disappearing. Crossing 31 January days with three park zones and left-joining the daily counts showed Astoria Park had no pickups on 21 of those days. A plain GROUP BY on trips would have listed only the days that had pickups. Be ready to say the row count first: 31 days times 3 zones gives 93 rows before the left join.
Q8. How do you check "has at least one" without duplicating rows?
Use EXISTS, a semi-join, not an inner join. An inner join returns one row per matching trip: 412 rows for zones with a trip over 50 miles. EXISTS returns each zone once, 63 rows, and can stop at the first match. Adding DISTINCT to the join also removes the duplicates, but only after the database has built them.
Q9. When would you use a FULL OUTER JOIN?
When both sides may have rows the other lacks, such as comparing two snapshots. Comparing the pickup zones seen in January 2024 and January 2025, zones 5 and 99 appeared only in 2025, and zone 105 only in 2024. A LEFT or RIGHT join would have shown only one direction. COALESCE picks the key from whichever side has it.
How to answer join questions well
- State the expected row count before running anything, and explain why.
- Ask about key uniqueness on both sides.
- Ask how NULLs should be treated, especially in anti-joins.
- Keep filters for the right-hand table in ON when you need a LEFT JOIN to stay a LEFT JOIN.
For broader practice, see SQL interview questions: top 50 with answers. Questions that build on joins continue in window function interview questions and scenario-based SQL interview questions, and the cost of joins is covered in SQL query optimisation interview questions.
What are the most common SQL join interview questions?
Why does a LEFT JOIN behave like an INNER JOIN?
Is NOT EXISTS better than NOT IN?
How do I find duplicates caused by a join?
Joins are taught in depth in the SQL Server course.
Sources
- Table Expressions (PostgreSQL documentation) — PostgreSQL Global Development Group
- Subquery Expressions (PostgreSQL documentation) — PostgreSQL Global Development Group
- FROM and JOIN Clauses (DuckDB documentation) — DuckDB
- TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
GloryTecks Team
Training & Placement Faculty
The GloryTecks faculty team mentors learners across Data Science, AI and Data Engineering, with a focus on real-time projects and placement readiness in Hyderabad.
Want to master Interview Questions?
Join GloryTecks’ expert-led SQL Server training in Hyderabad with real-time projects and 100% placement support.
Related articles
SQL Interview Questions: Top 50 with Answers
Fifty SQL interview questions with answers checked on PostgreSQL, DuckDB and SQLite against one small practice schema: evaluation order, NULL logic, joins and fan-out, window functions, dates, and performance, including where the three engines disagree.
Python Interview Questions for Data Roles
The plain-Python coding tasks data-analyst and data-engineer interviews use, without pandas: counting, deduplicating, grouping, streaming large files, flattening JSON, joining, and the bugs interviewers plant. Each has a worked solution and the follow-up questions to expect.
Data Science Interview Questions and Answers 2026
A data science interview is several interviews in one: statistics, machine learning, coding, product sense and a take-home. This guide maps each round with model answers, then works a churn take-home on real telecom data, including the checks that separate a strong submission from a high score.