SQL interviews at top multinational companies can go far beyond basic SELECT, WHERE, and JOIN syntax.
Candidates may be asked to reason about join behavior, data granularity, NULL values, aggregation, window functions, duplicate records, CTEs, query execution order, and query performance. The supplied research specifically highlights these areas as important patterns in advanced SQL interviews.
This list brings together 50 SQL interview questions across these high-value topics, with concise answers and practical query examples where useful.
Crack Data Analyst Interviews with Real Company Questions
Prepare for your next Data Analyst interview with 750+ curated interview questions covering SQL, Python, Excel, Power BI, Statistics, Machine Learning, and HR interviews—all organized in one structured guide.
1. INNER JOIN vs LEFT JOIN Interview Questions
1. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
An INNER JOIN returns only rows that have matching records in both tables.
A LEFT JOIN returns every row from the left table and the matching rows from the right table. If no match exists, the right-side columns contain NULL.
-- INNER JOIN
SELECT *
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
-- LEFT JOIN
SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
The key interview concept is row preservation:
- INNER JOIN → matching rows only
- LEFT JOIN → all left-side rows + matching right-side rows
2. When would you use a LEFT JOIN instead of an INNER JOIN?
Answer:
Use a LEFT JOIN when records from the left table must remain even if there is no corresponding record in the right table.
For example, to find all customers, including customers who have never placed an order:
SELECT
c.customer_id,
c.name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
3. Can a LEFT JOIN produce more rows than the left table?
Answer:
Yes.
If the right table contains multiple matching rows for one left-side record, the left-side row is repeated for every match.
For example, if one customer has three orders, joining customers to orders can produce three rows for that customer.
This is known as row multiplication or fan-out.
It is an important interview concept because aggregation performed after an unintended fan-out can produce incorrect results.
4. What happens if you put a LEFT JOIN condition in WHERE instead of ON?
Answer:
Consider:
SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.status = 'Completed';
The WHERE condition removes rows where o.status is NULL. As a result, unmatched customers disappear and the query behaves like an inner join for this condition.
Compare that with:
SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.status = 'Completed';
Here, all customers remain, while only completed orders are considered matches.
This ON versus WHERE distinction is a common interview trap.
5. Is LEFT JOIN commutative?
Answer:
No.
These two queries are not equivalent:
A LEFT JOIN B
and:
B LEFT JOIN A
A LEFT JOIN preserves rows from the table on the left side. Changing the table order changes which relation is preserved.
2. CTE vs Subquery Interview Questions
6. What is a CTE in SQL?
Answer:
A Common Table Expression, or CTE, is a named temporary result set defined using the WITH clause.
Example:
WITH CustomerSales AS (
SELECT
customer_id,
SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
)
SELECT *
FROM CustomerSales
WHERE total_sales > 10000;
CTEs can make complex queries easier to read and are also useful for recursive queries and multi-step transformations.
7. What is the difference between a CTE and a subquery?
Answer:
Both can represent an intermediate result, but they differ mainly in structure and readability.
A subquery is embedded directly inside another query:
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
A CTE defines the intermediate result before the main query:
WITH AvgSalary AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT *
FROM employees
WHERE salary > (SELECT avg_salary FROM AvgSalary);
CTEs are often easier to read when a query contains multiple logical steps.
8. Does using a CTE automatically make a query faster?
Answer:
No.
A CTE is primarily a query-structuring mechanism. Its performance depends on the database engine, query structure, references, and optimizer behavior.
The supplied research specifically notes that CTE optimization and materialization behavior can vary by database version and engine.
9. What is CTE materialization?
Answer:
Materialization means the database creates and stores the intermediate CTE result rather than simply incorporating the CTE logic into the surrounding query.
The research highlights PostgreSQL as an example: older PostgreSQL versions treated CTEs as optimization fences, while PostgreSQL 12+ can inline eligible CTEs by default in certain circumstances.
Therefore, candidates should avoid the blanket statement that "CTEs are always materialized."
10. When might you prefer a CTE over a subquery?
Answer:
A CTE can be useful when:
- The query contains multiple logical steps.
- The intermediate result needs a clear name.
- The same CTE is referenced multiple times.
- Recursive querying is required.
- Readability is important.
The choice should still be evaluated against the database engine and execution plan when performance matters.
Crack Data Analyst Interviews with Real Company Questions
Prepare for your next Data Analyst interview with 750+ curated interview questions covering SQL, Python, Excel, Power BI, Statistics, Machine Learning, and HR interviews—all organized in one structured guide.
3. GROUP BY and HAVING Interview Questions
11. What is the difference between WHERE and HAVING?
Answer:
WHERE filters individual rows before aggregation.
HAVING filters groups after aggregation.
Example:
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT(*) >= 5;
Here:
- WHERE removes employees earning 50000 or less.
- GROUP BY creates department-level groups.
- HAVING keeps departments containing at least five remaining employees.
12. Why can't you normally use an aggregate function directly in WHERE?
Answer:
Because WHERE operates before the grouping and aggregation phase.
For example, this is invalid:
WHERE COUNT(*) > 5
Instead:
HAVING COUNT(*) > 5
The logical processing order explains why HAVING can evaluate aggregate results while WHERE cannot.
13. What is the difference between GROUP BY and DISTINCT?
Answer:
DISTINCT is primarily used to remove duplicate rows from the selected result.
GROUP BY creates groups and is designed to work with aggregate functions.
Example:
SELECT DISTINCT department_id
FROM employees;
versus:
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
Both may appear to eliminate duplicates in certain situations, but they express different intentions.
14. Write a query to find departments having more than 10 employees.
Answer:
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
The aggregate condition belongs in HAVING.
15. Can HAVING be used without GROUP BY?
Answer:
Yes, depending on the SQL dialect.
Without an explicit GROUP BY, the query can treat the filtered dataset as a single group.
For example:
SELECT COUNT(*) AS employee_count
FROM employees
HAVING COUNT(*) > 100;
The exact syntax and behavior should be considered in the context of the SQL database being used.
4. SQL NULL Value Interview Questions
16. What is NULL in SQL?
Answer:
NULL represents an unknown, unavailable, or inapplicable value.
It does not mean:
- Zero
- Empty string
- False
SQL uses three-valued logic involving: TRUE, FALSE, UNKNOWN.
17. Why does NULL = NULL not return TRUE?
Answer:
Because NULL represents an unknown value.
SQL cannot establish that one unknown value is equal to another unknown value through a normal equality comparison.
Therefore:
NULL = NULL
evaluates to UNKNOWN, not TRUE.
To check for NULL, use:
WHERE column_name IS NULL
-- or
WHERE column_name IS NOT NULL
18. What is the difference between COUNT(*) and COUNT(column)?
Answer:
COUNT(*) counts rows.
COUNT(column) counts only rows where that particular column is not NULL.
For example:
COUNT(*)
counts every row, while:
COUNT(employee_id)
counts only rows where employee_id is non-NULL.
A useful expression for counting missing values is:
COUNT(*) - COUNT(column_name)
19. How does NULL affect AVG(), SUM(), MIN(), and MAX()?
Answer:
These aggregate functions generally ignore NULL values.
Suppose salary values are: 100, 200, NULL
Then:
AVG(salary)
calculates the average using the two non-NULL values: (100 + 200) / 2 = 150.
It does not treat NULL as zero.
20. How does GROUP BY handle NULL values?
Answer:
Multiple NULL values can belong to the same group.
For example:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Rows with NULL in department_id are grouped together.
This creates an important distinction:
- Normal comparison with NULL → UNKNOWN
- Grouping multiple NULL values → same grouping bucket
Crack Data Analyst Interviews with Real Company Questions
Prepare for your next Data Analyst interview with 750+ curated interview questions covering SQL, Python, Excel, Power BI, Statistics, Machine Learning, and HR interviews—all organized in one structured guide.
5. LEAD and LAG Interview Questions
21. What is the difference between LAG and LEAD?
Answer:
LAG() accesses a preceding row.
LEAD() accesses a following row.
Example:
SELECT
employee_id,
salary,
LAG(salary) OVER (ORDER BY employee_id) AS previous_salary,
LEAD(salary) OVER (ORDER BY employee_id) AS next_salary
FROM employees;
They are particularly useful for comparing sequential records.
22. How would you calculate the previous month's sales?
Answer:
First aggregate sales by month, then use LAG():
WITH MonthlySales AS (
SELECT
month,
SUM(sales) AS total_sales
FROM sales
GROUP BY month
)
SELECT
month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) AS previous_month_sales
FROM MonthlySales;
23. How would you calculate year-over-year growth using LAG?
Answer:
Use LAG() to retrieve the previous period's value.
Conceptually: current_value - previous_value can then be divided by the previous value to calculate percentage growth.
SELECT
year,
revenue,
LAG(revenue) OVER (ORDER BY year) AS previous_revenue
FROM yearly_sales;
The final calculation should also account for the possibility that the previous value is NULL or zero.
24. What do PARTITION BY and ORDER BY do inside LAG or LEAD?
Answer:
PARTITION BY separates the data into independent groups.
ORDER BY determines the sequence of rows within each group.
Example:
LAG(salary)
OVER (
PARTITION BY department_id
ORDER BY employee_id
)
This retrieves the previous employee's salary within each department rather than across the entire table.
25. What happens when LAG or LEAD reaches the beginning or end of a partition?
Answer:
If there is no preceding row for LAG() or no following row for LEAD(), the function normally returns NULL unless a default value is supplied.
Example:
LAG(salary, 1, 0)
OVER (ORDER BY employee_id)
Here, 0 is returned when there is no previous row.
6. SQL Running Total Interview Questions
26. How do you calculate a running total in SQL?
Answer:
A common solution uses a windowed SUM():
SELECT
order_date,
sales,
SUM(sales) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales;
The window function preserves the individual rows while adding cumulative context.
27. What is the difference between ROWS and RANGE in a running total?
Answer:
ROWS operates according to physical row positions.
RANGE operates according to logical values and can treat tied ordering values as peers.
This matters when multiple rows have the same value in the ORDER BY column.
For a strict row-by-row running total, explicitly specifying:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
can make the intended behavior clear.
28. Why can duplicate ORDER BY values cause unexpected running totals?
Answer:
Because window frames can treat tied values differently depending on the frame specification.
With a logical RANGE frame, rows sharing the same ordering value can be treated as peers and receive the same cumulative result.
If you require strict physical row-by-row behavior, use an explicit ROWS frame.
29. How would you calculate a running total separately for each customer?
Answer:
Use PARTITION BY:
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
The running total resets for every customer.
30. Why are window functions useful for running totals?
Answer:
A window function allows the query to calculate cumulative information while preserving the original row-level data.
A traditional GROUP BY would collapse rows into groups.
Window functions provide aggregated context without losing the underlying rows.
Crack Data Analyst Interviews with Real Company Questions
Prepare for your next Data Analyst interview with 750+ curated interview questions covering SQL, Python, Excel, Power BI, Statistics, Machine Learning, and HR interviews—all organized in one structured guide.
7. SQL Duplicate Records Interview Questions
31. How do you find duplicate records in SQL?
Answer:
Use GROUP BY with HAVING.
SELECT
email,
COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
This identifies values that occur more than once.
32. How do you delete duplicate records while keeping the latest record?
Answer:
A common deterministic approach uses a CTE and ROW_NUMBER():
WITH DuplicateRecords AS (
SELECT
customer_id,
email,
created_at,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC
) AS row_num
FROM customers
)
SELECT *
FROM DuplicateRecords
WHERE row_num > 1;
The record with row_num = 1 is retained, while later rows can be targeted for deletion.
The key is defining a deterministic ORDER BY criterion for which record should survive.
33. Why is ROW_NUMBER useful for duplicate removal?
Answer:
ROW_NUMBER() assigns a sequential number within each duplicate group.
For example:
email row_num
a@test.com 1
a@test.com 2
a@test.com 3
The query can then retain row 1 and identify rows 2 and 3 as duplicates.
This is more controlled than simply using DISTINCT when a specific version of the record must be retained.
34. Is DISTINCT enough to remove duplicate records from a table?
Answer:
Not necessarily.
DISTINCT can remove duplicate rows from a query result, but it does not tell the database which physical record should be retained when several records represent the same logical entity.
If you need to retain the newest record, oldest record, or another specific version, ROW_NUMBER() with an appropriate ordering rule is more suitable.
35. What is the difference between duplicate output rows and duplicate source records?
Answer:
A query can produce repeated-looking output even when neither table contains exact duplicate records.
For example, a one-to-many relationship can create multiple valid rows after a join.
Therefore, before removing duplicates, determine whether the repetition is:
- Actual duplicate data
- A valid one-to-many relationship
- Join fan-out
- An aggregation issue
This distinction is important in SQL interviews.
8. SQL Anti-Join Interview Questions
36. What is an anti-join?
Answer:
An anti-join returns rows from one table that do not have a matching row in another table.
For example: Find customers who have never placed an order.
A common pattern is:
SELECT c.*
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
37. What are common ways to implement an anti-join?
Answer:
Common approaches include:
- NOT EXISTS
- LEFT JOIN ... WHERE right_table.key IS NULL
- NOT IN
and, depending on the SQL dialect and requirement: EXCEPT.
The correct choice depends on NULL behavior, data characteristics, and the database engine.
38. Why can NOT IN be dangerous when NULL values exist?
Answer:
Suppose the subquery produces:
1
2
NULL
Then:
WHERE id NOT IN (1, 2, NULL)
can evaluate to UNKNOWN because of SQL's three-valued logic.
This can produce an unexpectedly empty result.
This is one reason NOT EXISTS is often preferred when nullable values are involved.
39. What is the difference between NOT EXISTS and NOT IN?
Answer:
NOT EXISTS checks whether a matching row exists.
NOT IN compares a value against a set of values.
The major interview issue is NULL handling. A NULL returned by a NOT IN subquery can change the result because comparisons involving NULL evaluate to UNKNOWN.
Example:
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
40. Which pattern can be used to find employees without departments?
Answer:
A LEFT JOIN anti-join is one option:
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
Another option is:
SELECT e.*
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.department_id = e.department_id
);
9. SQL Query Optimization Interview Questions
41. What is query optimization in SQL?
Answer:
Query optimization is the process of finding an efficient execution strategy for a SQL statement.
The database optimizer considers factors such as:
- Available indexes
- Data distribution
- Statistics
- Join methods
- Filtering
- Access paths
- Estimated costs
- Memory requirements
A logically correct query may still perform poorly if it requires excessive scanning, sorting, joining, or memory usage.
42. What is an execution plan?
Answer:
An execution plan describes how the database intends to execute a query.
It can show operations such as:
- Table or sequential scans
- Index scans
- Index seeks
- Nested loops
- Hash joins
- Merge joins
- Sorts
- Aggregations
Interviewers may ask candidates to identify expensive operations and explain how the query or database design could be improved.
43. What is the difference between an index seek and an index scan?
Answer:
An index seek navigates an index to locate the required portion of data.
An index scan reads a broader portion or the entirety of an index.
For highly selective queries, an index seek can be efficient. A scan can be appropriate when a large proportion of the table's rows is required.
Therefore, an index scan is not automatically a sign of a bad query.
44. What is a key lookup and how can it affect performance?
Answer:
A key lookup can occur when an index identifies the required rows but does not contain all columns requested by the query.
The database must then retrieve additional columns from the underlying table or clustered index.
Repeated lookups can create substantial random I/O.
A covering index, where appropriate, can include the required non-key columns and reduce the need for lookups.
45. What are the main physical join algorithms?
Answer:
Three important physical join strategies are:
- Nested Loops Join: Useful when the outer input is relatively small and the inner side can be accessed efficiently.
- Merge Join: Works particularly well when both inputs are already sorted on the join key.
- Hash Join: Useful for large, unsorted datasets. It builds a hash structure from one input and probes it using the other.
The optimizer selects among these strategies based on estimated costs and available data structures.
10. SQL Execution Order Interview Questions
46. What is the logical execution order of a SQL query?
Answer:
A commonly used logical sequence is:
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMIT / OFFSET or TOP
This differs from the way the query is written.
For example, SELECT appears at the beginning of the SQL statement but is logically evaluated after several other operations.
47. Why can't you usually use a SELECT alias in WHERE?
Answer:
Because WHERE is logically processed before SELECT.
Consider:
SELECT
salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 1000000;
The alias is created during the SELECT phase, after the WHERE phase.
A common solution is to repeat the expression or use a subquery/CTE.
48. Why can ORDER BY use a SELECT alias?
Answer:
Because ORDER BY occurs after the SELECT phase in the logical processing order.
For example:
SELECT
salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
The alias has already been created when the ordering operation is evaluated.
49. Where should you filter data when possible: WHERE or HAVING?
Answer:
If a condition applies to individual rows before aggregation, use WHERE.
For example:
WHERE order_status = 'Completed'
If the condition applies to an aggregate result, use HAVING.
For example:
HAVING SUM(amount) > 100000
Filtering rows earlier can reduce the amount of data that reaches expensive aggregation operations.
50. Why is understanding SQL execution order important in interviews?
Answer:
Execution order helps candidates explain why SQL behaves the way it does.
It helps answer questions such as:
- Why can't a WHERE clause use an aggregate?
- Why does HAVING filter grouped results?
- Why can't a SELECT alias normally be referenced in WHERE?
- Why can ORDER BY use a SELECT alias?
- Why can a LEFT JOIN effectively become an inner join after a WHERE condition?
- Where can filtering reduce the amount of data processed?
For advanced MNC interviews, understanding execution order demonstrates that the candidate understands the logic behind SQL rather than simply memorizing syntax.
Key SQL Interview Traps to Remember
Before an MNC SQL interview, pay particular attention to these areas:
1. JOINs can change the grain of your data: A one-to-many join can multiply rows.
2. LEFT JOIN does not mean "all rows from both tables": It preserves all rows from the left table.
3. WHERE and HAVING are not interchangeable: WHERE filters rows; HAVING filters groups.
4. NULL is not zero: NULL represents an unknown or unavailable value.
IS NULL
-- or:
IS NOT NULL
6. NOT IN can behave unexpectedly with NULL: Always consider NULL semantics when choosing between NOT IN and NOT EXISTS.
7. COUNT(*) and COUNT(column) are different: COUNT(*) counts rows. COUNT(column) ignores NULL values.
8. Window functions preserve row-level detail: Unlike GROUP BY, they can calculate analytical values without collapsing the original rows.
9. Running totals can depend on window frames: When ties matter, understand the difference between ROWS and RANGE.
10. A correct query is not necessarily an efficient query: For advanced interviews, be prepared to discuss execution plans, indexes, scans, seeks, and join algorithms.
How to Prepare for SQL Interviews at Top MNCs
Do not prepare these questions by memorizing definitions alone. A stronger approach is to prepare at three levels.
Be able to explain:
- What the SQL operation does
- When it should be used
- What result it produces
Practice scenarios involving:
- Customers and orders
- Employees and departments
- Sales and revenue
- Duplicate records
- Missing records
- Monthly comparisons
- Running totals
- Aggregations
For more advanced interviews, be prepared to explain:
- Join cardinality & Fan-out
- NULL behavior & logic
- WHERE vs HAVING & ON vs WHERE
- CTEs vs subqueries
- Window frames
- Anti-joins
- Execution order
- Index usage & Execution plans
- Physical join algorithms
The supplied research emphasizes this progression from basic syntax toward reasoning about relational behavior and physical execution.
Final Takeaway
The SQL questions used in demanding MNC interviews often test more than whether you can write a syntactically correct query.
The important skill is being able to explain why the query produces a particular result, how edge cases affect that result, and what happens when the data becomes large.
Focus particularly on these ten areas: INNER JOIN vs LEFT JOIN, CTE vs subquery, GROUP BY and HAVING, NULL values and three-valued logic, LEAD and LAG, Running totals and window frames, Duplicate records, Anti-joins, Query optimization, and SQL logical execution order.
If you can solve the questions above and explain the reasoning behind your solutions, you will be preparing for SQL interviews at a deeper level than simple syntax memorization.
Interview preparation note: These questions are curated from the supplied accounting interview research and its documented interview themes across Big 4, banking, corporate finance, and large-company roles. They should be treated as preparation questions based on those themes, not as a claim that every question was independently verified as an exact past question from a specific MNC.