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.

EasyTechnical

76 practiced

Explain how COALESCE helps in join results when combining tables with missing matches. Give a concrete query example where COALESCE is used to replace NULL with a meaningful default (e.g., 'Unknown' region or 0 revenue) and describe how that affects aggregations in BI reports.

Sample Answer

COALESCE returns the first non-NULL value from its arguments — handy in joins where one side has missing matches. In BI reporting it ensures labels and numeric measures show meaningful defaults (e.g., 'Unknown' region, 0 revenue) so aggregates and visuals don’t break or mislead.

Example: left join customers to monthly_revenue; some customers have no revenue rows.

sql

SELECT
  COALESCE(c.region, 'Unknown')        AS region,
  COALESCE(m.revenue, 0)               AS revenue
FROM customers c
LEFT JOIN monthly_revenue m
  ON c.customer_id = m.customer_id
  AND m.month = '2025-10';

How it affects aggregations:- Summing revenue: SUM(COALESCE(revenue,0)) correctly counts customers with no revenue as 0 rather than excluding them, preventing undercount.- Grouping by region: COALESCE(c.region,'Unknown') ensures customers without a region appear in an "Unknown" bucket instead of becoming NULL group or being omitted in visualizations.Best practices: apply COALESCE consistently in SELECT and aggregate expressions, and document defaults so stakeholders interpret "Unknown" or 0 correctly.

MediumTechnical

53 practiced

Given an orders table and a shipments table where some orders have multiple shipments, write a SQL query that returns one row per order showing total shipped quantity and a boolean 'fully_shipped' if total_shipped >= order_quantity. Be careful with joins to avoid multiplying rows due to another table that lists order discounts per order.

Sample Answer

Approach: aggregate shipments to one row per order first (or use a correlated subquery) to avoid row-multiplication when joining other one-to-many tables (like discounts). Then join the aggregated shipments to orders and compute total_shipped and fully_shipped.

sql

WITH shipments_agg AS (
  SELECT
    order_id,
    SUM(shipped_qty) AS total_shipped
  FROM shipments
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.order_quantity,
  COALESCE(s.total_shipped, 0) AS total_shipped,
  CASE WHEN COALESCE(s.total_shipped, 0) >= o.order_quantity THEN TRUE ELSE FALSE END AS fully_shipped
FROM orders o
LEFT JOIN shipments_agg s
  ON s.order_id = o.order_id;

Key points:- Aggregate shipments first to avoid multiplying rows when joining additional one-to-many tables (e.g., discounts).- Use LEFT JOIN so orders without shipments show total_shipped = 0.- COALESCE handles NULLs from the left join.Alternative: use a correlated subquery for total_shipped:

sql

SELECT o.order_id, o.order_quantity,
  COALESCE((SELECT SUM(shipped_qty) FROM shipments sh WHERE sh.order_id = o.order_id),0) AS total_shipped,
  (COALESCE((SELECT SUM(shipped_qty) FROM shipments sh WHERE sh.order_id = o.order_id),0) >= o.order_quantity) AS fully_shipped
FROM orders o;

EasyTechnical

72 practiced

A BI dashboard shows daily active users by acquisition channel. The user acquisition table contains channel_id but some rows have NULL channel_id. Describe two SQL approaches to attribute those users: map NULL to 'Unknown' using COALESCE, or join to a channel mapping table allowing NULL match. Provide example queries and discuss downstream implications for trend analysis.

Sample Answer

Approach 1 — map NULL to 'Unknown' with COALESCE- Simple, deterministic: replace NULL channel_id (or name) at select time so every row is attributed.

Example:

sql

SELECT
  activity_date,
  COALESCE(c.channel_name, 'Unknown') AS channel,
  COUNT(DISTINCT user_id) AS dau
FROM user_acquisition ua
LEFT JOIN channels c ON ua.channel_id = c.channel_id
GROUP BY 1, 2
ORDER BY 1, 2;

Notes: If channel_id is NULL or has no mapping, COALESCE ensures it appears in the dashboard under "Unknown".

Approach 2 — allow NULL match by joining on channel_id (keep NULL as NULL) and then label after aggregation- Useful when you want to distinguish explicit NULLs vs unmapped ids or preserve NULL for downstream logic.

Example:

sql

SELECT
  activity_date,
  CASE
    WHEN COUNT(c.channel_id) = 0 THEN 'Unknown'  -- no mapping found for any row in this group
    ELSE c.channel_name
  END AS channel,
  COUNT(DISTINCT user_id) AS dau
FROM user_acquisition ua
LEFT JOIN channels c ON ua.channel_id = c.channel_id
GROUP BY activity_date, c.channel_name;

Or keep NULL channel column and let BI tool display as "(null)".

Downstream implications for trend analysis- COALESCE centralizes all NULLs making trend lines stable and easy to filter, but masks whether NULL means missing data vs intentionally anonymous acquisition; this can hide data-quality issues.- Preserving NULLs helps root-cause (e.g., missing instrumentation) and lets you separate "Unknown" into subcases (unmapped id vs absent id) later, but can complicate visualizations (multiple null groups, unexpected blanks) and filtering.Recommendation: Use COALESCE for high-level dashboards for clarity, and maintain a separate QA/report that breaks out NULL reasons (missing id vs unmapped) so analysts can monitor data quality over time.

EasyTechnical

58 practiced

You have products(product_id INT, category TEXT) and months(month_date DATE). A report needs every month × category combination, even if sales are zero. Write an ANSI SQL query that builds all combinations and left joins sales to show sum(sales_amount) or 0 if missing. Tables: sales(product_id, sale_date, amount). Explain how you avoid an unintended Cartesian product and why CROSS JOIN is appropriate here.

Sample Answer

Approach: build the set of all month × category combinations (the desired reporting skeleton) using CROSS JOIN between months and the distinct categories, then LEFT JOIN aggregated sales (grouped by month and category). Use COALESCE to show 0 where no sales exist. CROSS JOIN is appropriate because we intentionally want every combination; we avoid an unintended Cartesian product by reducing one side to distinct categories (not full products) and by aggregating sales first.

sql

WITH categories AS (
  SELECT DISTINCT category
  FROM products
),
sales_by_month_cat AS (
  SELECT
    DATE_TRUNC('month', sale_date)::date AS month_date,
    p.category,
    SUM(s.amount) AS total_amount
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  GROUP BY DATE_TRUNC('month', sale_date)::date, p.category
)
SELECT
  m.month_date,
  c.category,
  COALESCE(sbm.total_amount, 0) AS sales_amount
FROM months m
CROSS JOIN categories c
LEFT JOIN sales_by_month_cat sbm
  ON sbm.month_date = m.month_date
  AND sbm.category = c.category
ORDER BY m.month_date, c.category;

Why this avoids an unintended Cartesian product:- We only cross-join months with distinct categories (small dimensional sets), not with the full products table; that produces exactly M×C rows intentionally.- Sales are pre-aggregated, so the LEFT JOIN attaches amounts without multiplying rows.- If you cross-joined products to months directly and then joined sales, you'd multiply product rows per month and inflate counts. Use distinct dimensions and aggregation to prevent that.

Performance notes: ensure indexes on sales.sale_date, sales.product_id and products.product_id for the join and on month_date if months is large.

MediumTechnical

54 practiced

A dashboard needs to show customers who churned: customers with a last_order_date older than 180 days and no subscription active. Write a robust SQL query that joins customers, orders, and subscriptions, ensuring customers with NULL subscription records are treated as unsubscribed, and discuss edge cases such as cancelled but still active subscriptions.

Sample Answer

Approach: aggregate last order per customer, determine whether any subscription is currently active (handle multiple subs and NULLs), then select customers whose last_order_date < CURRENT_DATE - 180 and who have no active subscription.

sql

WITH last_orders AS (
  SELECT
    c.id AS customer_id,
    c.email,
    MAX(o.order_date) AS last_order_date
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.email
),
active_subs AS (
  -- for each customer, flag if ANY subscription is considered active
  SELECT
    s.customer_id,
    MAX(
      CASE
        WHEN s.status IN ('active','trial') 
             AND (s.end_date IS NULL OR s.end_date >= CURRENT_DATE) THEN 1
        WHEN s.status = 'cancelled' AND (s.end_date IS NULL OR s.end_date >= CURRENT_DATE) THEN 1
        ELSE 0
      END
    ) AS has_active_sub
  FROM subscriptions s
  GROUP BY s.customer_id
)
SELECT
  l.customer_id,
  l.email,
  l.last_order_date
FROM last_orders l
LEFT JOIN active_subs a ON a.customer_id = l.customer_id
WHERE
  -- treat NULL last_order_date as churned only if business defines customers with no orders as churned;
  l.last_order_date IS NOT NULL
  AND l.last_order_date < CURRENT_DATE - INTERVAL '180 days'
  -- NULL in active_subs -> no subscriptions -> treated as unsubscribed
  AND COALESCE(a.has_active_sub, 0) = 0;

Key points and edge cases:- NULL subscription rows are treated as unsubscribed via COALESCE.- Handle multiple subscriptions by aggregating to a single flag (any active = active).- "Cancelled but still active": many systems keep a cancelled subscription active until end_date — query treats cancelled with future/null end_date as still active.- Customers with no orders: I excluded NULL last_order_date; if business wants to consider never-ordered customers as churned, remove that filter.- Timezone and date semantics: ensure order_date/end_date use same timezone and DATE vs TIMESTAMP handling.- Performance: index on orders(customer_id, order_date) and subscriptions(customer_id, end_date, status) helps. Alternative: use EXISTS subquery for active subscription check.

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.