Structured Query Language Join Operations Questions

Comprehensive coverage of Structured Query Language join types and multi table query patterns used to combine relational data and answer business questions. Topics include inner join, left join, right join, full outer join, cross join, self join, and anti join patterns implemented with NOT EXISTS and NOT IN. Candidates should understand equi joins versus non equi joins, joining on expressions and composite keys, and how join choice affects row counts and null semantics. Practical skills include translating business requirements into correct join logic, chaining joins across two or more tables, constructing multi table aggregations, handling one to many relationships and duplicate rows, deduplication strategies, and managing orphan records and referential integrity issues. Additional areas covered are join conditions versus WHERE clause filtering, aliasing for readability, using functions such as coalesce to manage null values, avoiding unintended Cartesian products, and basic performance considerations including join order, appropriate indexing, and interpreting query execution plans to diagnose slow joins. Interviewers may probe result correctness, edge cases such as null and composite key behavior, and the candidate ability to validate outputs against expected business logic.

MediumTechnical

71 practiced

Explain how join order can affect performance in relational databases and query planners. Describe differences between heuristic and cost-based planners and when manually reordering joins or using optimizer hints might be necessary due to incorrect cardinality estimates.

MediumTechnical

72 practiced

Describe how SQL handles equality comparisons involving NULL in join predicates, for example when joining A.key = B.key and either side can be NULL. Provide approaches to treat NULLs as equal when matching keys across tables in Postgres or BigQuery.

Sample Answer

Short answer: equality A.key = B.key returns UNKNOWN (so no match) if either side is NULL. To treat NULLs as equal you can use NULL-safe comparisons. Common approaches in Postgres and BigQuery:

1) IS NOT DISTINCT FROM (preferred when available)- Semantics: treats NULL = NULL as true, otherwise behaves like =.- Postgres:

sql

SELECT *
FROM A
JOIN B ON A.key IS NOT DISTINCT FROM B.key;

- BigQuery (Standard SQL): also supports IS NOT DISTINCT FROM.

2) Explicit NULL checks (portable)

sql

SELECT *
FROM A
JOIN B
  ON (A.key = B.key)
  OR (A.key IS NULL AND B.key IS NULL);

3) COALESCE to a sentinel (fast but risky if sentinel collides)

sql

SELECT *
FROM A
JOIN B
  ON COALESCE(A.key, '__NULL__') = COALESCE(B.key, '__NULL__');

Use a value guaranteed not to exist (or concatenated type-safe sentinel).

Notes and trade-offs:- IS NOT DISTINCT FROM is clear and less error-prone. It’s usually preferable where supported.- The OR / COALESCE patterns are portable; COALESCE may be simpler but watch sentinel collisions and type casting.- Index use: equality with indexed columns is best; some DB engines may not use indexes for complex expressions (COALESCE or OR). Benchmark for large joins and consider pre-normalizing keys (e.g., replacing NULLs with a sentinel in a derived column) for performance.

EasyTechnical

78 practiced

Write two SQL queries that find customers who have never placed an order: one using LEFT JOIN ... WHERE orders.customer_id IS NULL and one using NOT EXISTS. Explain why NOT IN can be unsafe when orders.customer_id contains NULLs, and which pattern you generally prefer and why.

Sample Answer

LEFT JOIN anti-join:

sql

-- Customers who never placed an order using LEFT JOIN
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

NOT EXISTS version:

sql

-- Customers who never placed an order using NOT EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Why NOT IN can be unsafe:- If you write WHERE c.customer_id NOT IN (SELECT customer_id FROM orders) and the orders.customer_id subquery returns any NULL, the whole predicate evaluates to UNKNOWN for non-matching values, so rows you expect to return may be excluded. Example: orders contains (NULL, 2); then NOT IN yields no rows except those equal to NULL-handling exceptions.- To use NOT IN safely you must ensure the subquery cannot contain NULLs (e.g., WHERE customer_id IS NOT NULL inside the subquery).

Which I prefer and why:- I generally prefer NOT EXISTS (or LEFT JOIN ... IS NULL) over NOT IN. NOT EXISTS is clear, handles NULLs correctly, and often expresses the intent (anti-join) directly. Performance depends on the optimizer and indexes; for large datasets, both LEFT JOIN/IS NULL and NOT EXISTS produce similar plans in modern engines, but NOT EXISTS avoids subtle null-related bugs and is explicit about row-level checks—valuable for reliable data engineering pipelines.

MediumTechnical

69 practiced

Given two customer datasets system_a(id,email,name) and system_b(id,email,name), write a SQL query using FULL OUTER JOIN to find rows that exist in only one system and rows that exist in both. Show resulting columns that indicate source membership and explain how NULLs indicate absence.

Sample Answer

Approach: Use a FULL OUTER JOIN on the natural key (email is common unique identifier here) to produce every row from both systems. Create membership flags and a canonical id/name using COALESCE. NULLs in the joined columns show absence from one side.

sql

-- Full reconciliation showing membership flags and canonical fields
SELECT
  COALESCE(a.email, b.email)                        AS email,
  COALESCE(a.id, b.id)                              AS unified_id,
  a.id                                              AS id_a,
  b.id                                              AS id_b,
  a.name                                            AS name_a,
  b.name                                            AS name_b,
  CASE WHEN a.email IS NOT NULL THEN 1 ELSE 0 END   AS in_system_a,
  CASE WHEN b.email IS NOT NULL THEN 1 ELSE 0 END   AS in_system_b
FROM system_a a
FULL OUTER JOIN system_b b
  ON a.email = b.email;

To get rows only in one system:

sql

-- Exists only in A or only in B
SELECT *
FROM (
  -- use the previous query as subquery or CTE named reconciled
) reconciled
WHERE (in_system_a = 1 AND in_system_b = 0)
   OR (in_system_a = 0 AND in_system_b = 1);

To get rows present in both:

sql

WHERE in_system_a = 1 AND in_system_b = 1;

Explanation:- FULL OUTER JOIN returns all rows from both tables; when a row has no match, columns from the other table are NULL.- in_system_a/in_system_b flags are derived from whether the email (or id) is NULL on that side.- COALESCE(a.id, b.id) picks a canonical id when only one side exists; you can also prioritize one system (e.g., COALESCE(a.id, b.id) or COALESCE(b.id, a.id)).- Compare name_a vs name_b to detect data drift; NULL in name_b means record absent in system_b.

HardTechnical

72 practiced

You wrote a daily revenue report joining stores -> orders -> order_items -> discounts but totals are inflated due to join multiplicity. Given typical schemas, describe how to identify which join causes duplication and rewrite the query to produce correct aggregation, explaining the reasoning step by step.

Sample Answer

Situation: You're joining stores -> orders -> order_items -> discounts and daily revenue totals are too large because some joins multiply rows (one-to-many relationships).

Approach summary:1. Confirm which join causes duplication by checking row counts and cardinality per key.2. Fix by aggregating at the correct grain before joining (pre-aggregate) or by joining to deduplicated aggregates.

Steps and example SQL:

1) Diagnose multiplicity- Count rows at each join-step and distinct keys:

sql

-- base counts
SELECT COUNT(*) AS stores,
       (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM order_items) AS items,
       (SELECT COUNT(*) FROM discounts) AS discounts;
-- counts after join
SELECT COUNT(*) AS joined_total,
       COUNT(DISTINCT orders.order_id) AS distinct_orders
FROM stores
JOIN orders ON stores.store_id = orders.store_id
JOIN order_items ON orders.order_id = order_items.order_id
JOIN discounts ON order_items.item_id = discounts.item_id; -- or discounts.order_id?

If COUNT(*) >> COUNT(DISTINCT orders.order_id), duplication occurs (likely from order_items or discounts).

2) Correct pattern: aggregate at the lowest one-to-many grain before joining up.

Preferred fix: compute revenue per order (or per order_item) first, then join stores and discounts appropriately.

Example: if discounts are per order (one row per order) but you mistakenly joined discounts to order_items (one-to-many), do:

sql

WITH order_revenue AS (
  SELECT oi.order_id,
         SUM(oi.price * oi.quantity) AS items_total
  FROM order_items oi
  GROUP BY oi.order_id
),
order_discounts AS (
  SELECT d.order_id,
         SUM(d.amount) AS discount_total
  FROM discounts d
  GROUP BY d.order_id
)
SELECT s.store_id,
       o.order_date::date AS day,
       SUM(orv.items_total - COALESCE(od.discount_total,0)) AS revenue
FROM orders o
JOIN order_revenue orv ON o.order_id = orv.order_id
LEFT JOIN order_discounts od ON o.order_id = od.order_id
JOIN stores s ON o.store_id = s.store_id
GROUP BY s.store_id, day;

Explanation and reasoning:- Multiplicity comes from joining tables with one-to-many relationships without collapsing the many-side first.- Pre-aggregating ensures each order contributes once; joining raw order_items and discounts multiplies discounts across items.- Use LEFT JOIN for optional discounts; COALESCE prevents nulls.- If discounts apply per item, aggregate at order_item level instead.

Edge cases:- Multiple discount rows per order -> must SUM before subtracting.- Overlapping discounts (per-item and per-order) -> carefully model application order.- Null/negative values -> validate business rules.

Alternative: use DISTINCT ON or window functions to select canonical discount row, but aggregation is clearer and scales better for big data pipelines.

Unlock Full Question Bank

Get access to hundreds of Structured Query Language Join Operations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.