Question 1

In standard SQL, what does the expression NULL = NULL evaluate to?

Accepted Answer

NULL represents an unknown value, so comparing two unknowns with = does not produce TRUE or FALSE — it produces NULL (unknown), because SQL has no way to determine whether two unknown values are equal. This is why filtering for NULL values requires 'IS NULL' rather than '= NULL' in a WHERE clause: a condition like "WHERE column = NULL" never matches any row, even rows where column genuinely is NULL, because that comparison always evaluates to NULL rather than TRUE, and WHERE only keeps rows where the condition is TRUE.

Question 2

In a query that uses GROUP BY with an aggregate function, what is the difference between filtering with WHERE and filtering with HAVING?

Accepted Answer

WHERE is applied first, row by row, before rows are grouped, so it can't reference an aggregate like COUNT(*) because that aggregate doesn't exist yet at that stage. HAVING runs after GROUP BY has formed the groups and the aggregates have been computed, so it can filter on those aggregate results, for example "HAVING COUNT(*) > 1". Writing "WHERE COUNT(*) > 1" instead is a syntax error in most databases for exactly this reason — the aggregate simply isn't available yet when WHERE runs.

Question 3

Given tables customers and orders, consider this query:
SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
What happens for a customer who has placed no orders?

Accepted Answer

LEFT JOIN keeps every row from the left table (customers) regardless of whether a match exists on the right (orders); when no match exists, the columns coming from the right table are filled with NULL instead of the row being dropped. Excluding the customer entirely is what an INNER JOIN would do instead — that's the key difference between the two, and it's exactly why LEFT JOIN gets used when the goal is 'everyone, plus their orders if they happen to have any.'

Question 4

You add an index on a column that is frequently used in WHERE clauses of a large, read-heavy table. What is the main trade-off of doing this?

Accepted Answer

An index is an auxiliary structure (commonly a B-tree) that the database maintains alongside the table, so every row that's inserted, updated, or deleted also requires the index to be updated — that write overhead, plus the extra storage the index occupies, is the price paid for faster lookups. This is why indexing is a judgment call based on a column's read/write ratio and how selective it is, not something to apply blindly to every column in a table.

Question 5

In the context of database transactions, what is a 'dirty read'?

Accepted Answer

A dirty read happens when transaction A reads a row that transaction B has modified but not yet committed; if B then rolls back, A has read a value that never actually existed in the committed data. Isolation levels above 'read uncommitted' — 'read committed' and stricter — exist specifically to prevent this by only ever letting a transaction see data that has been committed. The other options each describe a real query issue, but neither is what 'dirty read' refers to in transaction isolation terminology.

Question 6

This query is run:
SELECT department, COUNT(*) FROM employees GROUP BY department;
Why does department need to appear in the GROUP BY clause?

Accepted Answer

GROUP BY collapses many rows into one row per group, and for a non-aggregated column like department the database needs a rule for what single value to display in that collapsed row — grouping by it guarantees every row folded into a group shares the same department value, so there's no ambiguity. Standard SQL requires this, and Postgres always enforces it; MySQL enforces it by default too, through its ONLY_FULL_GROUP_BY mode (the default since MySQL 5.7). Some databases historically allowed the non-grouped column anyway and silently picked an arbitrary row's value, which is a common source of confusing, non-deterministic results.

Question 7

Explain the difference between INNER JOIN and LEFT JOIN, with an example.

Accepted Answer

Both INNER JOIN and LEFT JOIN combine rows from two tables based on a matching condition, but they differ in what happens to rows that don't find a match. Consider an authors table and a books table, joined with:
SELECT authors.name, books.title
FROM authors
INNER JOIN books ON authors.id = books.author_id;
An INNER JOIN only returns rows where the join condition is satisfied on both sides — an author with no books in the books table simply doesn't appear in the result at all, because there's no matching books row to pair them with. Changing INNER JOIN to LEFT JOIN in that same query keeps every row from the left table (authors) no matter what: an author with no books still appears once, with books.title coming back as NULL, since there's no matching row on the right to fill that column with real data. In short, INNER JOIN answers 'which authors have at least one book, and what are they', while LEFT JOIN answers 'every author, and their books if they have any' — the choice depends on whether unmatched left-side rows should be dropped or kept with NULLs.

Question 8

The orders table has one row per order (columns: id, customer_name). The order_items table has one row per item within an order (columns: id, order_id, product), so a single order can have several order_items rows. Consider:
SELECT orders.id, orders.customer_name, order_items.product
FROM orders
JOIN order_items ON orders.id = order_items.order_id;
If a particular order has 3 items, what does this query return for that order, and why? What problem could this cause if you later tried to SUM(orders.total) over this joined result?

Accepted Answer

A join produces one output row for every matching pair of rows between the two tables, not one row per order. Since that order has 3 matching rows in order_items (one per item), the joined result contains 3 rows for it — each one repeating the same orders.id and orders.customer_name, paired with a different order_items.product each time. This 'fan-out' is expected behavior for a one-to-many join, but it's a common source of bugs: if you then tried to SUM(orders.total) over this joined result to get a total across orders, the order's total would effectively be added in 3 times (once per row it appears in) instead of once, silently inflating the sum by a factor equal to however many items that order has. To avoid that, either aggregate order_items first (for example, count or sum items per order_id in a subquery) before joining it to orders, or aggregate orders.total separately from the item-level join, or use SELECT DISTINCT / a correlated aggregate that doesn't multiply the order-level columns by the number of matching item rows.

Question 9

Explain the difference between a clustered index and a non-clustered index, and why a table can typically have only one clustered index but many non-clustered indexes.

Accepted Answer

A clustered index determines the physical order in which a table's rows are stored on disk — the table's data pages themselves are organized as the index, so the leaf level of the index is the actual row data. Because a table's rows can only be physically arranged in one order at a time, a table can have at most one clustered index; in many databases, if you declare a primary key, that key becomes the clustered index by default. A non-clustered index is a separate structure: it stores the indexed column's values in sorted order along with a pointer (or the clustered key) back to where the full row actually lives. A table can have many non-clustered indexes, since each one is just an independent lookup structure sitting alongside the table rather than dictating their physical layout. The practical consequence is that a lookup satisfied entirely by a clustered index retrieves the row directly, since the index leaf is the data, while a lookup that goes through a non-clustered index and needs columns beyond what the index stores has to take an extra step — often called a bookmark or key lookup — to fetch the rest of the row using the pointer. This concept is most explicit in engines like SQL Server and MySQL's InnoDB, which cluster tables by primary key; Postgres, by contrast, doesn't maintain a table in continuously clustered order automatically (its CLUSTER command reorders data once but doesn't keep it clustered as new rows are written), so the term is used more loosely there.

Question 10

Explain the difference between UNION and UNION ALL, and when you'd choose one over the other.

Accepted Answer

Both UNION and UNION ALL combine the result rows of two (or more) SELECT queries into a single result set, and both require the queries to have the same number of columns with compatible types. The difference is what happens to duplicate rows: UNION performs an implicit duplicate-elimination pass over the combined results, so if the same row appears in both queries (or repeats within one of them), it only shows up once in the final output. UNION ALL simply concatenates the result sets without checking for duplicates at all, so a row that appears in both queries shows up twice. For example:
SELECT city FROM current_customers
UNION
SELECT city FROM past_customers;
would list a city that appears in both tables only once, while replacing UNION with UNION ALL would list it twice. In practice, UNION ALL is generally preferred when duplicates are impossible by construction (for example, the two queries pull from mutually exclusive date ranges) or simply don't matter for the use case, because skipping the deduplication step avoids a sort or hash pass and is noticeably cheaper on large result sets. UNION is worth the extra cost specifically when a deduplicated combined list is the actual requirement.

Question 11

How would you write a query to find duplicate rows in a table — for example, rows in a users table where the same email address appears more than once? Write the query and explain your approach.

Accepted Answer

The core idea is to group rows by whatever column (or columns) define a 'duplicate' and then keep only the groups with more than one member:
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Grouping by email collapses every row into one group per distinct email address, COUNT(*) counts how many original rows fell into each group, and HAVING keeps only the groups where that count is greater than one — in other words, email addresses that show up more than once. This query is cheap and easy to reason about, but it returns the duplicate email values and their counts, not the full duplicate rows themselves. To get the full rows, you'd join this result back to the base table on email, or use a window function instead: something like ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) computed in a subquery, then filtering the outer query for rows where that row number is greater than 1. The window-function version is often preferable in practice because it naturally identifies which specific rows are 'the extras' (for example, everything after the first by id), which matters if the next step is deciding which duplicate row to keep and which to delete.

Question 12

Explain what a correlated subquery is, how it differs from a regular (non-correlated) subquery, and give an example.

Accepted Answer

A non-correlated subquery is self-contained: it can be evaluated once, entirely on its own, independent of whatever row the outer query happens to be looking at. For example:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Here the inner query computes one company-wide average salary, and that single value is used to filter every row of the outer query. A correlated subquery, by contrast, references a column from the outer query inside the subquery itself, so conceptually it has to be re-evaluated for each row the outer query considers. For example:
SELECT e1.name FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary) FROM employees e2
  WHERE e2.department = e1.department
);
Here the inner query's WHERE clause depends on e1.department, which comes from the outer row currently being checked, so it computes a different department-specific average salary for each outer row — the whole query finds employees earning more than their own department's average, rather than the company-wide average. Because a correlated subquery is conceptually tied to the current outer row, it's often slower than a non-correlated one on a naive execution plan, though real database engines frequently rewrite correlated subqueries into an equivalent join or apply other optimizations rather than literally re-running the subquery per row. In practice, many correlated subqueries can also be rewritten explicitly as a join or a window function, which is often clearer and can be faster depending on the engine.

SQL Interview Questions