SQL Subqueries, Correlated Subqueries, Self-Joins, and Cross-Joins: 20 Real Interview Questions

SQL subqueries fundamentals are critical tools for complex data retrieval. Subqueries (inner queries nested within outer queries) solve problems requiring multiple logical steps: filtering based on aggregates, comparing rows across the same table, and retrieving data from multiple sources.

Understanding SQL Subqueries Fundamentals

SQL subqueries fundamentals start with the basic concept: a query nested inside another query. The inner query executes first, returning results used by the outer query. Unlike CTEs which make code readable, subqueries can become difficult to parse when nested multiple levels deep.

Subqueries appear in three locations: SELECT clause (scalar subqueries returning one value), FROM clause (derived tables), and WHERE clause (filtering conditions). Each location has different rules and performance implications.

Scalar Subqueries vs IN/EXISTS Subqueries

SQL subqueries fundamentals teach the difference between scalar and set-returning subqueries. A scalar subquery returns exactly one row-one column value, used like a variable. An IN or EXISTS subquery returns multiple rows, used for filtering conditions.

IN(subquery) checks if a value appears in the subquery result set. EXISTS(subquery) checks whether the subquery returns any rows at all (ignoring actual values). EXISTS is often faster for large result sets because it stops after finding the first match.

Correlated Subqueries: Row-by-Row Processing

SQL subqueries fundamentals include correlated subqueries, which reference outer query columns. For each row in the outer query, the inner query executes with that rows values. This is powerful but risky—correlated subqueries can be extremely slow on large tables because the inner query runs once per outer row (1M outer rows = 1M inner query executions).

Self-Joins: Comparing Rows Within the Same Table

SQL self-joins solve hierarchy and comparison problems: finding employees and their managers (both in employees table), comparing consecutive dates, finding duplicates, or identifying related records. A self-join is simply joining a table to itself using aliases (e.g., e1 and e2).

Cross-Joins: Cartesian Products

SQL cross-joins produce all possible combinations of two tables. 100 stores × 365 days = 36,500 rows (all store-day combinations). Cross-joins are rare in SELECT but common in data generation—creating a complete calendar table or all possible combinations for reporting.

Subquery Performance Considerations

SQL subqueries fundamentals include understanding when to use subqueries vs joins vs CTEs. Subqueries in WHERE are often slower than JOINs because the optimizer cant always push filtering down. Correlated subqueries are slowest—avoid them on large tables. CTEs and JOINs are usually faster.

5 Complete Interview Questions with Solutions

Question 1: Scalar Subquery in SELECT

Return each customer and their total spending (from orders table). Use a scalar subquery in SELECT instead of GROUP BY.

SELECT c.customer_id, c.name,
       (SELECT SUM(amount) FROM orders o 
        WHERE o.customer_id = c.customer_id) AS total_spending
FROM customers c;

Explanation: Scalar subquery in SELECT calculates total_spending per customer. One correlated subquery per row—slower than JOIN + GROUP BY but preserves all customer rows even with zero orders. Use when you need customer detail AND spending in one row.

Amazon – Senior Analytics EngineerGoogle – Data AnalystMeta – BI Engineer

Question 2: IN Subquery for Filtering

Find customers who placed orders worth > $1000. Use IN(subquery) instead of JOIN + GROUP BY + HAVING.

SELECT customer_id, name FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  WHERE amount > 1000
);

Explanation: IN(subquery) filters outer query to customers appearing in subquery result. Slower than JOIN on large sets. IN returns NULL matches (NULL IN (1,2,3) = NULL, not TRUE). Use NOT IN cautiously with NULL-prone columns.

Microsoft – Analytics EngineerAirbnb – Senior Data AnalystFlipkart – Data Analyst

Question 3: EXISTS Subquery vs IN

Find customers with at least one order. Which is faster: EXISTS(subquery) or IN(subquery) on 100M orders?

SELECT customer_id FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Explanation: EXISTS stops after finding first match (efficient). IN collects all matches before filtering (less efficient on large sets). EXISTS is generally faster. SELECT 1 is conventional (any column works since EXISTS only checks presence).

Google – Senior Analytics EngineerLinkedIn – BI EngineerUber – Analytics Engineer

Question 4: Self-Join for Hierarchies

Find employees and their manager names (both in employees table). Use self-join with aliases e1 (employee) and m (manager).

SELECT e.employee_id, e.name AS employee_name,
       m.employee_id AS manager_id, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Explanation: Self-join treats same table as two separate sources. LEFT JOIN keeps employees without managers (CEO, orphaned records). INNER JOIN excludes them. Self-joins are fundamental for hierarchies and comparisons.

OYO – Senior Data AnalystStripe – Analytics Engineer

Question 5: Cross-Join for Data Generation

Create all store-date combinations (100 stores × 365 days = 36,500 rows) for sales reporting, even stores with zero sales.

SELECT s.store_id, d.date
FROM stores s
CROSS JOIN calendar_dates d
WHERE d.date BETWEEN 2024-01-01 AND 2024-12-31
LEFT JOIN sales ON s.store_id = sales.store_id 
  AND d.date = sales.date;

Explanation: CROSS JOIN creates all combinations without ON clause. LEFT JOIN adds actual sales, keeping rows with zero sales (NULL). Essential for complete reporting—shows which stores had zero sales on specific dates.

Amazon – Senior BI EngineerGoogle – Analytics Engineer

15 Practice Questions (Test Yourself)

Question 6: When you use a scalar subquery in the SELECT clause and it returns NULL for some customers because they have no matching orders, how does this result differ from using a LEFT JOIN with GROUP BY?

Google – Data AnalystMicrosoft – Senior Analytics Engineer

Question 7: If you have 10 million customers and 50 million orders, a scalar subquery approach takes 45 seconds while a JOIN with GROUP BY takes only 2 seconds, why is there such a significant 22-fold performance difference between these two approaches?

Meta – BI EngineerFlipkart – Analytics Engineer

Question 8: When an IN subquery result set contains NULL values along with actual customer IDs like (1, 2, NULL), what happens when you execute a WHERE clause with IN that includes NULL in its result set?

Amazon – Senior Data AnalystAirbnb – Analytics EngineerLinkedIn – Data Analyst

Question 9: When comparing the performance of EXISTS versus IN on a table with 100 million rows, how does EXISTS manage to be more efficient by using SELECT 1 compared to selecting actual column values?

Google – Senior BI EngineerOYO – Senior Analytics Engineer

Question 10: In a self-join where you want to find employees with the same salary level, why is it important to include a condition like e1.id < e2.id in your join clause?

Stripe – Senior Analytics EngineerUber – Data AnalystMicrosoft – BI Engineer

Question 11: When you need to find consecutive dates or sequence patterns in your data, would you prefer using a self-join approach or would a window function with ROW_NUMBER be more efficient for achieving this?

Amazon – Analytics EngineerGoogle – Senior Analytics Engineer

Question 12: If you create a CROSS JOIN between stores (50 rows) and products (1000 rows) and dates (365 rows) resulting in 18.25 million rows, what strategy would you use to manage memory usage effectively?

Meta – Senior Data AnalystFlipkart – BI EngineerLinkedIn – Analytics Engineer

Question 13: When you use a correlated subquery in the SELECT clause that references the outer query for every row in a 10 million row dataset, what would be the expected performance impact on overall query execution time?

Google – Data AnalystAmazon – Senior BI Engineer

Question 14: A subquery in the FROM clause (derived table) can improve readability compared to nested subqueries in the WHERE clause. What are the advantages and disadvantages of using derived tables versus using CTEs?

Airbnb – Senior Analytics EngineerOYO – Data AnalystStripe – BI Engineer

Question 15: When writing a self-join with multiple conditions such as e1.manager_id = e2.id AND e1.department = e2.department, how would you interpret the results and what business logic does this condition satisfy?

Microsoft – Analytics EngineerGoogle – Senior BI Engineer

Question 16: When you need to find records that do not exist in another table, would you use NOT EXISTS or NOT IN, and why is NOT EXISTS often the safer choice when your subquery might contain NULL values?

Amazon – Analytics EngineerMeta – Senior Data AnalystLinkedIn – BI Engineer

Question 17: If you need to create all possible combinations between a calendar table (365 rows) and departments (100 rows) for reporting purposes, would a simple CROSS JOIN be sufficient or would you need to add additional filtering afterward?

Google – Senior Analytics EngineerFlipkart – Analytics Engineer

Question 18: When you use a subquery result like (SELECT MAX(salary) FROM employees) in a WHERE clause to find employees earning the maximum salary, would this be a scalar or set-returning subquery?

OYO – Senior Data AnalystAirbnb – BI EngineerMicrosoft – Senior Analytics Engineer

Question 19: When using a self-join to find duplicate records based on email addresses, why would you want to include the condition e1.id < e2.id to ensure you dont return the same pair of duplicates twice?

Meta – Analytics EngineerAmazon – Senior BI Engineer

Question 20: If you have scalar subqueries in multiple SELECT columns each performing different aggregate functions like SUM, COUNT, and MAX, would it be more efficient to use multiple correlated subqueries or rewrite this using a single JOIN operation?

Google – Data AnalystLinkedIn – Senior Analytics EngineerStripe – BI Engineer

Ready to Master SQL Subqueries & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.