Join Operations and Multi Table Queries Questions

Comprehensive mastery of joining data across two or more tables in Structured Query Language. Candidates should understand and be able to use inner join, left join, right join, and full outer join semantics, including how each type affects row inclusion and null propagation. Be familiar with self joins, cross joins and anti join and semi join patterns for filtering. Know how to write correct multi table join conditions to avoid inadvertent Cartesian products, how to deduplicate and validate results by checking row counts and key uniqueness, and how to handle nulls and duplicate column names. Understand when to prefer joins versus subqueries or common table expressions for clarity or performance. Be able to read and interpret execution plans and explain how join order, join algorithms such as nested loop join, hash join, and merge join, and appropriate indexing affect performance. Recognize differences in join syntax and behavior across Structured Query Language dialects, including use of USING versus ON clauses and older comma separated join styles. Practice building queries that combine filtering, aggregation, grouping, and joins across three or more tables to express realistic business logic while keeping correctness and performance in mind.

EasyTechnical

53 practiced

Rewrite this legacy comma-style join into explicit JOIN syntax and explain why explicit joins are preferred for readability and correctness:

Original:SELECT u.name, o.order_idFROM users u, orders oWHERE u.id = o.user_id AND o.amount > 100;

Show the equivalent using explicit JOINs and mention any pitfalls when mixing old-style and explicit joins.

Sample Answer

Use an explicit INNER JOIN to make the relationship and intent clear:

sql

SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o
  ON u.id = o.user_id
WHERE o.amount > 100;

Why explicit JOINs are preferred- Readability: join conditions live in the ON clause next to the tables being joined, separating relational logic from filter predicates in WHERE. This makes queries easier to scan and maintain, especially with many tables.- Correctness: explicit JOINs make join type (INNER, LEFT, RIGHT, CROSS) unambiguous, reducing accidental cross joins or wrong results.- Maintainability at scale: easier to refactor, add outer joins, and reason about null-preserving behavior.

Pitfalls when mixing styles- Mixing comma-style joins with explicit JOINs can produce surprising precedence/associativity and make outer joins behave incorrectly or be hard to read.- Old-style joins put join conditions in WHERE; when converting, ensure those conditions become ON clauses if they define relationships — otherwise you may unintentionally turn an outer join into an inner join.- Some DBs/linters may warn; adopt explicit JOINs consistently across the codebase.

EasyTechnical

45 practiced

Describe the difference between JOIN ... USING(column) and JOIN ... ON table1.col = table2.col. Show an example where USING reduces duplicated columns in the result set and explain how column resolution differs when referring to that column later in SELECT or ORDER BY.

Sample Answer

Brief answer:USING(col) tells the DB to join on equality of that column and collapse the two input columns into a single output column named col. JOIN ... ON table1.col = table2.col keeps both input columns available in the result (table1.col and table2.col) unless you explicitly project one. With USING you must refer to the merged column by its unqualified name in SELECT/ORDER BY; qualifying it (table1.col) will usually fail or be ambiguous.

Example (Postgres/MySQL):

CREATE sample tables and data:

sql

CREATE TABLE users(id INT, name TEXT);
CREATE TABLE orders(id INT, user_id INT, total INT);
INSERT INTO users VALUES (1,'Alice'),(2,'Bob');
INSERT INTO orders VALUES (10,1,100),(11,2,50);

JOIN ... ON (keeps both columns if selected):

sql

SELECT users.id AS user_id, orders.user_id AS orders_user_id, name, total
FROM users
JOIN orders ON users.id = orders.user_id;

Result columns: user_id | orders_user_id | name | totalYou see both id columns if you select them.

JOIN ... USING (collapses duplicate column):

sql

SELECT id, name, total
FROM users
JOIN orders USING(id);

Result columns: id | name | totalOnly one id column appears (the common column is merged).

Column resolution differences:- After USING(id), refer to that column as id (unqualified). Qualifying as users.id or orders.id in the SELECT list typically is invalid because the merged column is not returned as a table-qualified field.- After ON, you can refer to users.id or orders.user_id (depending on schema); if you select both without aliases, you’ll get duplicated names and may need qualification or aliases.- In ORDER BY: WITH USING use ORDER BY id; WITH ON you can ORDER BY users.id or orders.user_id or by column position.

Why it matters for a Data Engineer:USING can make result sets cleaner and reduce accidental duplicate columns when joining many tables, but remember it changes how you reference that column in downstream queries and ETL code — use explicit projection and aliases when you need both source columns or want to preserve provenance.

MediumTechnical

52 practiced

When joining tables that share column names, how do you avoid ambiguous column references and remove duplicate column names in the result? Given customers(id, name) and customer_snapshots(id, name, snapshot_date), write a query that returns id and name preferring snapshot.name when present (ANSI SQL). Show two variants: one using USING and one using COALESCE with explicit qualifiers.

HardTechnical

55 practiced

A Spark job joining events (billions of rows) to a small dimension stalls because a single key is extremely frequent (skew). Explain the salting technique to mitigate skew, provide a PySpark example adding salt to the join key on the large side and duplicating the small dimension keys as needed, and discuss tradeoffs and how to remove the salt in final aggregations.

Sample Answer

Salting adds a random/hashed small integer suffix to the join key on the large (skewed) side and duplicates the small dimension keys for each possible salt value so the heavy key’s records are spread across many partitions. That avoids one task handling most records.

Approach:- Choose a salt range N (e.g., 0..n-1) where n splits skew (start small: 8-64).- On large side, add salt = randInt(0, N-1) and create salted_key = key || '_' || salt.- On small side, cross-join distinct keys with 0..N-1 to produce N copies per key with salted_key matching.- Join on salted_key, process, then remove salt by aggregating back on original key.

PySpark example:

python

from pyspark.sql import functions as F
from pyspark.sql.types import IntegerType
import random

N = 16  # salt range

# large_events: DataFrame with 'key', 'value'...
large = large_events.withColumn("salt", (F.rand()*N).cast(IntegerType())) \
                    .withColumn("salted_key", F.concat_ws("_", F.col("key"), F.col("salt")))

# small_dim: DataFrame with 'key', 'dim_attr'...
salt_values = spark.createDataFrame([(i,) for i in range(N)], ["salt"])
small_replicated = small_dim.crossJoin(salt_values) \
                           .withColumn("salted_key", F.concat_ws("_", F.col("key"), F.col("salt"))) \
                           .drop("salt")

joined = large.join(small_replicated, on="salted_key", how="left")
# ... do aggregations, e.g., count per original key:
result = joined.groupBy("key").agg(F.count("*").alias("cnt"))

Tradeoffs and considerations:- Extra work: small dimension is replicated N× (small cost if small_dim is tiny).- Choosing N: too small fails to resolve skew; too large inflates shuffle and join cost.- Deterministic salt (hash of row id mod N) can help reproducibility.- Memory/network: increases shuffle parallelism but also total data moved (small side multiplication).- Ensure salt applied only to skewed keys (optional): you can detect top-K keys and only salt those; others join normally via union of salted and unsalted paths.

Removing salt:- After join/processing, aggregate by the original key (drop salt) to collapse results (e.g., sums, counts). For row-level outputs you may need to remove salt column and deduplicate or recompute keys. Always reduce back to original key space before downstream consumption.

HardTechnical

41 practiced

A nightly ETL joins customers -> addresses -> orders and downstream counts doubled unexpectedly. Describe a systematic debugging approach: what SQL checks and diagnostics would you run to find where duplication is introduced, how to validate key uniqueness, and what changes you would make to fix and prevent the issue.

Sample Answer

Situation clarification: first confirm scope — which nightly job, which tables/joins, and the exact downstream metric that doubled. Reproduce on a copy of the pipeline with yesterday's and a known-good day's outputs.

Diagnostics — quick high-level checks- Compare row counts by table and pipeline stage between good and bad runs:

sql

SELECT 'customers' AS t, COUNT(*) FROM customers WHERE batch_date = '2025-12-05';
SELECT 'addresses' AS t, COUNT(*) FROM addresses WHERE batch_date = '2025-12-05';
SELECT 'orders' AS t, COUNT(*) FROM orders WHERE batch_date = '2025-12-05';

- Compare pipeline outputs (pre-join vs post-join):

sql

SELECT stage, COUNT(*) FROM staging_table WHERE batch_date = '2025-12-05' GROUP BY stage;

Find where duplication appears (join cardinality checks)- For each join, compute counts before and after and group-by key multiplicity:

sql

-- multiplicity in addresses per customer
SELECT customer_id, COUNT(*) AS addr_cnt
FROM addresses WHERE batch_date = '2025-12-05'
GROUP BY customer_id HAVING COUNT(*) > 1 LIMIT 10;

-- multiplicity in orders per address
SELECT address_id, COUNT(*) AS ord_cnt
FROM orders WHERE batch_date = '2025-12-05'
GROUP BY address_id HAVING COUNT(*) > 1 LIMIT 10;

- Run join cardinality matrix to detect many-to-many surprises:

sql

SELECT COUNT(*) FROM customers c
JOIN addresses a ON c.customer_id = a.customer_id
WHERE c.batch_date = '2025-12-05';
-- compare to COUNT(distinct c.customer_id) and COUNT(distinct a.address_id)

Validate key uniqueness and identity- Check primary-key uniqueness and nulls:

sql

SELECT COUNT(*) AS total, COUNT(DISTINCT customer_id) AS distinct_ids FROM customers WHERE batch_date = '2025-12-05';
SELECT COUNT(*) - COUNT(DISTINCT <pk>) AS duplicate_rows FROM <table> WHERE batch_date = '2025-12-05';

- Use window functions to find duplicate rows (identical payloads):

sql

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, name, email ORDER BY ingest_ts DESC) rn
  FROM customers_staging WHERE batch_date = '2025-12-05'
) t WHERE rn > 1 LIMIT 50;

- Compute checksums to detect unintended re-ingest:

sql

SELECT customer_id, MD5(CONCAT_WS('|', col1,col2,...)) AS row_hash, COUNT(*) FROM customers_staging GROUP BY customer_id,row_hash HAVING COUNT(*) > 1;

Compare diffs between last-good and bad runs- Use EXCEPT/LEFT JOIN to find newly duplicated keys or new rows:

sql

SELECT customer_id FROM customers_bad EXCEPT SELECT customer_id FROM customers_good;

Root-cause hypotheses & tests- Was a JOIN key changed (e.g., address.customer_id nulls replaced)? Test for NULLs or whitespace in join keys.- Was upstream dedupe failing (staging dedupe removed/buggy)? Re-run dedupe logic on staging data to see if it removes duplicates.- Was incremental logic (CDC) applied twice? Check offsets/timestamps and ingestion markers.

Fixes (short-term remediation)- Fix the offending join by deduping input relation with deterministic rule before join:

sql

WITH dedup_addresses AS (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY address_id ORDER BY updated_ts DESC) rn FROM addresses WHERE batch_date = '2025-12-05'
  ) x WHERE rn = 1
)
-- use dedup_addresses in downstream joins

- If duplication came from many-to-many semantics, change join to aggregate or distinct keys prior to join (e.g., join on primary keys only, or aggregate orders counts before join).

Fixes (long-term & prevention)- Enforce uniqueness constraints where possible (DB primary keys, unique indexes) or add pipeline assertions that fail the job if duplicates appear (e.g., count != distinct_count).- Add idempotent ingestion patterns: use MERGE/upsert with natural keys and last-write-wins logic instead of blind inserts.- Add end-to-end tests: nightly checks that compare row counts, distinct counts, key null rates, and row-hash diffs against baseline.- Add monitoring/alerting: data quality metrics (duplicate rate, cardinality ratio) with thresholds and automated alerts.- Add lineage and lineage-aware sampling so you can trace a duplicated downstream row back to source row(s).

Communication & postmortem- Triage: communicate impact, rollback or reprocess if needed, and run reprocessing with patched dedupe logic.- Postmortem: document root cause, timeline, code diff that introduced bug, and the permanent controls (assertions, constraints, monitoring) deployed.

This approach systematically narrows the problem from pipeline stage to specific join or upstream ingestion, validates uniqueness with COUNT vs COUNT(DISTINCT) and window functions, and prescribes both immediate remediation and long-term controls to prevent recurrence.

Unlock Full Question Bank

Get access to hundreds of Join Operations and Multi Table Queries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.