50 SQL Query Interview Questions for Java Developers (with Answers)
50 SQL interview queries with answers for Java developers: Nth highest salary, duplicates, joins, GROUP BY and HAVING, window functions, recursive CTEs and indexes. One sample schema, every query run on PostgreSQL 16 with its real result, plus a downloadable SQL file.
SQL interview questions for Java developers keep coming back to the same dozen ideas: finding the Nth highest value, removing duplicates, choosing the right join, grouping with GROUP BY and HAVING, ranking with window functions, and knowing when an index helps. The classic opener is “find the second highest salary”. Here it is, run against the sample schema used for all 50 queries in this post:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
second_highest
----------------
200000
(1 row)
Every one of the 50 queries below has the same shape: the question, the SQL, a plain explanation of why it works (or where it breaks), and the real result copied from a PostgreSQL 16 run — not a hand-typed guess. The whole thing is one SQL file you can download and run.
If the interviewer asks…
Pattern
Queries
Second / Nth highest salary
Subquery, DENSE_RANK, LIMIT/OFFSET
6–11
Find or delete duplicate rows
GROUP BY … HAVING COUNT(*) > 1, ROW_NUMBER
12–16
Which join, and why are rows missing?
INNER / LEFT / FULL, anti-join, NOT EXISTS
17–26
Totals, averages, filtering groups
GROUP BY, HAVING, ROLLUP
27–33
Above-average earners, per-department logic
Scalar and correlated subqueries
34–37
Rank, running total, previous row
Window functions
38–44
Org chart, set operations, slow query
Recursive CTE, UNION, EXPLAIN, indexes
45–50
Versions used here. PostgreSQL 16.15. Most queries are standard SQL and also run on MySQL 8 and Oracle with small changes; where PostgreSQL differs (FULL OUTER JOIN, STRING_AGG, ROLLUP syntax, DELETE … USING) the explanation says so. I ran everything on PostgreSQL only — the MySQL notes come from its documented syntax, not from a run. Checked 22 October 2026.
The sample schema
The sample schema behind all 50 queries.
One schema serves every query, so you only learn it once. The quirks listed under the diagram are intentional: they are what makes the “why is my result wrong?” questions real. Employees and departments are small enough to read in full:
Download the whole thing — schema, data and all 50 queries in one file — as sql-interview-lab.sql, create a database and run psql -d lab -f sql-interview-lab.sql. (The file runs top to bottom without errors on PostgreSQL 16.15; queries 15 and 16 change data inside a transaction that they roll back.)
Basics: filtering, NULLs, DISTINCT and paging (queries 1–5)
Start here: these are warm-up questions, but each hides a trap (NULL comparisons and unstable ordering).
1. List employees in Pune earning more than 100,000, highest paid first
SELECT name, salary, city
FROM employees
WHERE city = 'Pune' AND salary > 100000
ORDER BY salary DESC, name;
WHERE filters rows before anything else happens; ORDER BY runs last, so it can sort on any selected column. Add a tiebreaker column (here name) whenever two rows can share the sort key, otherwise the order of ties is unspecified.
name | salary | city
-------------+--------+------
Rohan Mehta | 180000 | Pune
Rohan Mehta | 130000 | Pune
Vikram Shah | 120000 | Pune
Meera Joshi | 110000 | Pune
(4 rows)
2. Combine LIKE, IN and BETWEEN
SELECT name, city, hire_date
FROM employees
WHERE name LIKE 'R%'
OR city IN ('Delhi', 'Kolkata')
OR hire_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY id;
BETWEEN is inclusive on both ends, which matters for dates: '2024-12-31' is included, but a timestamp later that day would not be. LIKE 'R%' can use a B-tree index only when the pattern has no leading wildcard (and, on PostgreSQL, when the index was built with the right operator class).
3. Handle NULLs: employees with no manager, and a safe default
SELECT id, name, manager_id, COALESCE(manager_id, 0) AS manager_or_zero
FROM employees
WHERE manager_id IS NULL
ORDER BY id;
manager_id = NULL is never true — any comparison with NULL yields UNKNOWN, and WHERE keeps only TRUE. Use IS NULL. COALESCE returns its first non-NULL argument and is portable; IFNULL and NVL are vendor-specific.
SELECT COUNT(*) AS total_rows,
COUNT(manager_id) AS rows_with_manager,
COUNT(DISTINCT city) AS distinct_cities
FROM employees;
COUNT(*) counts rows; COUNT(col) counts non-NULL values of that column; COUNT(DISTINCT col) counts unique non-NULL values. The 15 versus 10 here is the NULL-skipping rule in action.
SELECT id, name
FROM employees
ORDER BY id
LIMIT 5 OFFSET 5;
Page 2 of size 5. Without ORDER BY the pages are not stable. For deep pages OFFSET still reads and discards every skipped row; keyset pagination (WHERE id > :last_id ORDER BY id LIMIT 5) stays fast. SQL Server uses OFFSET ... FETCH, Oracle 12c+ supports FETCH FIRST.
The most-asked SQL question. Know at least three ways and say which one you would use in production (usually DENSE_RANK).
6. Second highest salary with a subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
The classic opener. It works because the inner query finds the top salary and the outer query takes the maximum of everything below it. It returns one row with NULL (not zero rows) when there is no second value.
second_highest
----------------
200000
(1 row)
7. Second highest with LIMIT/OFFSET over DISTINCT salaries
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
The DISTINCT is the whole point: without it, 180000 appears twice and the “second highest” would come back as 180000 pointing at the same tie. Unlike the previous query this returns zero rows, not NULL, when the value does not exist.
salary
--------
200000
(1 row)
8. Nth highest with DENSE_RANK (N = 3)
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 3
GROUP BY salary;
The general solution: change 3 to any N. DENSE_RANK gives ties the same rank and does not skip numbers, so rank 3 is the third distinct salary. RANK would skip (1,2,2,4) and ROW_NUMBER would split ties arbitrarily; both give wrong answers here. The GROUP BY collapses the tied rows into one result.
salary
--------
180000
(1 row)
9. Nth highest with a correlated subquery (no window functions)
SELECT DISTINCT e1.salary
FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary);
For a salary S, count how many distinct salaries are higher; if exactly N-1 are higher then S is the Nth highest. Here N = 3, so the count is 2. It runs the inner query once per outer row, so it is O(n²) — fine for the interview whiteboard (and for MySQL 5.7, which has no window functions), not for a million rows.
salary
--------
180000
(1 row)
10. Highest salary per department, with the employee name
SELECT d.name AS department, e.name AS employee, e.salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY d.name, e.name;
Engineering shows one row because 250000 is unique, but if two employees tied for the department maximum both would appear — usually what you want. Beware the tempting SELECT dept_id, name, MAX(salary) ... GROUP BY dept_id: PostgreSQL rejects it, and older MySQL returned an arbitrary name.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
A variant that comes up as “top N salaries”. If the interviewer wants the people instead of the values, join this result back to employees or use DENSE_RANK() <= 3.
Finding duplicates is easy; deleting them safely is the real test, so always wrap the delete in a transaction first.
12. Find duplicate emails
SELECT email, COUNT(*) AS occurrences
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
Group by the column you suspect, then keep groups with more than one row. HAVING is required because the condition is on an aggregate. Isha Kapoor was loaded with [email protected], which is Asha Rao’s address.
13. Show the full duplicate rows (names and ids), not just the key
SELECT e.id, e.name, e.email
FROM employees e
JOIN (SELECT email FROM employees GROUP BY email HAVING COUNT(*) > 1) d
ON d.email = e.email
ORDER BY e.email, e.id;
The aggregate query loses the ids, so join its result back to the base table. A window function does the same without the join: COUNT(*) OVER (PARTITION BY email) > 1 inside a subquery.
14. Find people duplicated by name (a different key gives a different answer)
SELECT name, COUNT(*) AS occurrences, MIN(id) AS keep_id, MAX(id) AS extra_id
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
Two Rohan Mehta rows exist with different emails. Whether that is a duplicate depends on the business key, which is the first question to ask in the interview: “what makes two rows the same?”
15. Delete duplicates but keep the lowest id (run inside a transaction here)
BEGIN;
DELETE FROM employees e
USING employees keep
WHERE e.name = keep.name
AND e.id > keep.id;
SELECT id, name FROM employees WHERE name = 'Rohan Mehta' ORDER BY id;
ROLLBACK;
PostgreSQL’s DELETE ... USING self-join removes every row that has a lower-id twin. The ROLLBACK keeps the demo data intact. The portable version is DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY name); on MySQL you cannot delete from a table you select from in a subquery without wrapping it in a derived table. Employee 15 was Rohan Mehta’s twin, so it goes.
id | name
----+-------------
2 | Rohan Mehta
(1 row)
16. Delete duplicates with ROW_NUMBER (works when there is no unique id to compare)
BEGIN;
DELETE FROM employees
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
FROM employees
) t
WHERE rn > 1
);
SELECT COUNT(*) AS remaining FROM employees;
ROLLBACK;
Number the rows inside each duplicate group and delete everything numbered 2 or higher. The ORDER BY inside OVER decides which row survives. Always run the inner SELECT alone first and look at what it would remove.
Most wrong results in production come from joins that silently drop or multiply rows. Count rows before and after.
17. INNER JOIN: employees with their department name
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;
Only matching rows survive. Tarun Bose has dept_id = NULL and Legal has no employees, so neither appears — 14 rows, not 15.
18. LEFT JOIN: keep every employee, even without a department
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE e.id IN (13, 14)
ORDER BY e.id;
LEFT JOIN keeps all rows from the left table and fills the right side with NULL when nothing matches. The trap: putting a filter on the right table in WHERE (e.g. WHERE d.name = 'Sales') silently turns it back into an inner join — put such conditions in ON.
name | department
-------------+------------
Isha Kapoor | Finance
Tarun Bose |
(2 rows)
19. Departments that have no employees (anti-join with LEFT JOIN … IS NULL)
SELECT d.id, d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
Left-join and then keep only the rows where the right side did not match. This is the most-asked join question after plain INNER versus LEFT.
id | name
----+-------
5 | Legal
(1 row)
20. FULL OUTER JOIN: unmatched rows from both sides
SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.dept_id
WHERE e.id IS NULL OR d.id IS NULL;
Returns Tarun (no department) and Legal (no employee) in one result. MySQL has no FULL OUTER JOIN; emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.
22. CROSS JOIN: every employee city paired with every department (row explosion)
SELECT COUNT(*) AS pairs
FROM (SELECT DISTINCT city FROM employees) c
CROSS JOIN departments;
4 cities × 5 departments = 20 rows. A cross join has no ON clause; it is also what you get by accident when you forget the join condition. Legitimate uses are calendars, test-data generation and combinatorial lists.
pairs
-------
20
(1 row)
23. Employees with no project assignment (NOT EXISTS)
SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM project_assignments pa WHERE pa.emp_id = e.id)
ORDER BY e.id;
NOT EXISTS is the safe anti-join. It asks “is there no matching row” and is immune to NULLs, unlike NOT IN (next question).
24. The NOT IN trap: one NULL makes the whole result empty
SELECT COUNT(*) AS with_not_in
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
SELECT COUNT(*) AS with_not_exists
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id = e.id);
manager_id contains NULLs, so id NOT IN (1, 5, ..., NULL) evaluates to UNKNOWN for every row and nothing is returned. NOT EXISTS correctly returns the employees who manage nobody. Prefer NOT EXISTS or add WHERE manager_id IS NOT NULL to the subquery.
SELECT e.name AS employee, p.name AS project, pa.hours
FROM project_assignments pa
JOIN employees e ON e.id = pa.emp_id
JOIN projects p ON p.id = pa.project_id
ORDER BY p.name, e.name;
A many-to-many link table needs two joins. Join order does not change the result for inner joins; the optimiser picks its own order.
26. Customers and their order totals, including customers with no orders
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'
GROUP BY c.name
ORDER BY total_spent DESC;
The status = 'PAID' condition lives in ON so that Dabur (no orders) still appears with zero. Counting o.id rather than * gives 0 instead of 1 for that row.
Aggregation: WHERE filters rows, HAVING filters groups. Everything in SELECT must be grouped or aggregated.
27. Headcount and average salary per department
SELECT d.name AS department, COUNT(*) AS headcount, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY headcount DESC, d.name;
Every non-aggregated column in SELECT must appear in GROUP BY (MySQL relaxes this unless ONLY_FULL_GROUP_BY is on, which is the default since 5.7).
28. HAVING: departments with an average salary above 120,000
SELECT d.name AS department, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
HAVING AVG(e.salary) > 120000;
HAVING filters groups after aggregation.
department | avg_salary
-------------+------------
Engineering | 172000
(1 row)
29. WHERE versus HAVING in one query
SELECT city, COUNT(*) AS employees, ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01' -- filters rows BEFORE grouping
GROUP BY city
HAVING COUNT(*) >= 2 -- filters groups AFTER grouping
ORDER BY city;
Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. A condition that does not involve an aggregate belongs in WHERE, where it can use an index and shrink the work.
SELECT d.name AS department, e.city, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name, e.city
ORDER BY d.name, e.city;
Each distinct (department, city) pair becomes one group.
SELECT COALESCE(d.name, 'ALL DEPARTMENTS') AS department, SUM(e.salary) AS payroll
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY ROLLUP (d.name)
ORDER BY d.name NULLS LAST;
ROLLUP adds a super-aggregate row where the grouping column is NULL, which COALESCE relabels. MySQL spells it GROUP BY d.name WITH ROLLUP.
SELECT d.name AS department,
COUNT(*) FILTER (WHERE e.city = 'Mumbai') AS mumbai,
SUM(CASE WHEN e.city = 'Pune' THEN 1 ELSE 0 END) AS pune,
SUM(CASE WHEN e.city = 'Delhi' THEN 1 ELSE 0 END) AS delhi
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY d.name;
SUM(CASE ...) works on every database; FILTER (WHERE ...) is the standard-SQL spelling that PostgreSQL supports and MySQL does not. This is how you turn rows into columns without a vendor-specific PIVOT.
SELECT d.name AS department, STRING_AGG(e.name, ', ' ORDER BY e.name) AS people
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY d.name;
PostgreSQL’s STRING_AGG is GROUP_CONCAT in MySQL and LISTAGG in Oracle.
Scalar, correlated and EXISTS subqueries. A correlated subquery runs once per outer row, so know when to rewrite it as a join or window.
34. Employees earning more than the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, name;
A scalar subquery runs once and returns one value. The company-wide average works out to about 126,333, so only the six highest-paid rows survive.
35. Employees earning more than their own department’s average (correlated)
SELECT e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY d.name, e.salary DESC;
The inner query refers to e.dept_id from the outer query, so it is evaluated per outer row (databases often rewrite it as a join). The equivalent window form AVG(salary) OVER (PARTITION BY dept_id) scans once.
SELECT d.name, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY AVG(e.salary) DESC
LIMIT 1;
Group, sort, take one. If ties must all be returned use RANK() ... = 1 instead of LIMIT 1.
name | avg_salary
-------------+------------
Engineering | 172000
(1 row)
37. Managers: employees who have at least one direct report (EXISTS)
SELECT m.id, m.name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = m.id)
ORDER BY m.id;
EXISTS stops at the first match, so it does not matter what the inner SELECT returns (SELECT 1 is convention). It is a semi-join.
Rank, running totals and previous/next row without collapsing rows. Window functions run after WHERE and GROUP BY, so you cannot filter on them directly — wrap them in a subquery or CTE.
38. ROW_NUMBER over the whole company
SELECT id, name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS rn
FROM employees
ORDER BY rn
LIMIT 5;
ROW_NUMBER always gives distinct numbers 1..n. The id tiebreaker makes the numbering deterministic when salaries tie.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC, name
LIMIT 6;
After the two 180000 rows RANK jumps to 4 while DENSE_RANK continues with 3. That one difference answers most window-function interview questions.
SELECT hire_date, name, salary,
SUM(salary) OVER (ORDER BY hire_date, id) AS running_payroll
FROM employees
ORDER BY hire_date, id
LIMIT 6;
With ORDER BY inside OVER, the default frame is “from the first row up to the current row”, which makes SUM cumulative. Add the id tiebreaker, because with the default RANGE frame tied sort keys would be summed together.
41. LAG and LEAD: compare each order with the previous one
SELECT id, order_date, amount,
LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders
ORDER BY order_date;
LAG(col, n, default) looks n rows back inside the window; the first row gets NULL. LEAD looks forward. Month-over-month growth is the usual real-world form.
SELECT department, name, salary
FROM (
SELECT d.name AS department, e.name, e.salary,
DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk
FROM employees e
JOIN departments d ON d.id = e.dept_id
) t
WHERE rnk <= 2
ORDER BY department, salary DESC, name;
PARTITION BY restarts the ranking per department. You cannot filter on a window function in the same query’s WHERE — wrap it in a subquery or CTE. Engineering and Sales each return three rows because two people tie for second place.
SELECT d.name AS department, e.name, e.salary,
ROUND(100.0 * e.salary / SUM(e.salary) OVER (PARTITION BY d.id), 1) AS pct_of_dept
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE d.name = 'Sales'
ORDER BY e.salary DESC, e.name;
A window aggregate keeps the individual rows and attaches the group total to each, which GROUP BY cannot do.
Hierarchies, set operations and indexes (queries 45–50)
Recursive CTEs for trees, UNION versus UNION ALL, and reading an EXPLAIN plan well enough to explain why a query is slow.
45. Recursive CTE: the full reporting chain under Asha Rao
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT depth, name FROM org ORDER BY depth, name;
A recursive CTE has an anchor query (the root) and a recursive query that joins back to the CTE until it returns no rows. Use UNION ALL (not UNION) unless you need cycle protection; for cyclic data add a depth limit or a visited-path column.
SELECT city FROM employees WHERE dept_id = 1
UNION
SELECT city FROM employees WHERE dept_id = 2
ORDER BY city;
SELECT COUNT(*) AS rows_union_all FROM (
SELECT city FROM employees WHERE dept_id = 1
UNION ALL
SELECT city FROM employees WHERE dept_id = 2
) t;
UNION removes duplicates, which costs a sort or hash; UNION ALL does not. If you know the sets cannot overlap, always write UNION ALL.
city
--------
Delhi
Mumbai
Pune
(3 rows)
rows_union_all
----------------
9
(1 row)
47. EXCEPT and INTERSECT
SELECT city FROM employees WHERE dept_id = 1
EXCEPT
SELECT city FROM employees WHERE dept_id = 2;
SELECT city FROM employees WHERE dept_id = 1
INTERSECT
SELECT city FROM employees WHERE dept_id = 2
ORDER BY 1;
Cities where Engineering has staff but Sales does not, then cities with both. EXCEPT is MINUS in Oracle; MySQL gained EXCEPT/INTERSECT in 8.0.31.
city
------
Pune
(1 row)
city
--------
Mumbai
(1 row)
48. EXPLAIN: sequential scan versus index scan on 200,000 rows
EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id = 42;
CREATE INDEX idx_big_orders_customer ON big_orders (customer_id);
ANALYZE big_orders;
EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id = 42;
Before the index the planner has no option but to read every row. After it, the planner switches to a bitmap or index scan. EXPLAIN (COSTS OFF) hides the cost numbers so the plan shape is readable; use EXPLAIN (ANALYZE, BUFFERS) for real timings. An index is not always used — for a predicate matching most rows the sequential scan wins.
QUERY PLAN
------------------------------
Seq Scan on big_orders
Filter: (customer_id = 42)
(2 rows)
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: (customer_id = 42)
-> Bitmap Index Scan on idx_big_orders_customer
Index Cond: (customer_id = 42)
(4 rows)
49. A function on the indexed column defeats the index
CREATE INDEX idx_big_orders_date ON big_orders (order_date);
ANALYZE big_orders;
EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE EXTRACT(MONTH FROM order_date) = 3;
EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';
The first query wraps the column in EXTRACT, so the B-tree on order_date cannot be searched. Rewrite the predicate as a range on the raw column (second query) or create an expression index. The same applies to LOWER(email) = ..., col + 1 = ... and implicit casts.
QUERY PLAN
-----------------------------------------------------------------
Gather
Workers Planned: 1
-> Parallel Seq Scan on big_orders
Filter: (EXTRACT(month FROM order_date) = '3'::numeric)
(4 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: ((order_date >= '2025-03-01'::date) AND (order_date < '2025-04-01'::date))
-> Bitmap Index Scan on idx_big_orders_date
Index Cond: ((order_date >= '2025-03-01'::date) AND (order_date < '2025-04-01'::date))
(4 rows)
50. Composite index and the leftmost-prefix rule
CREATE INDEX idx_big_orders_cust_date ON big_orders (customer_id, order_date);
ANALYZE big_orders;
EXPLAIN (COSTS OFF)
SELECT id FROM big_orders WHERE customer_id = 42 AND order_date >= '2025-06-01';
EXPLAIN (COSTS OFF)
SELECT id FROM big_orders WHERE order_date = '2025-06-01' AND amount > 9990;
A composite index on (customer_id, order_date) serves queries that filter on customer_id, or on both. A query filtering only on the second column cannot use it as a search key — the planner falls back to the single-column order_date index created earlier. Put the equality column first and the range column last.
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: ((customer_id = 42) AND (order_date >= '2025-06-01'::date))
-> Bitmap Index Scan on idx_big_orders_cust_date
Index Cond: ((customer_id = 42) AND (order_date >= '2025-06-01'::date))
(4 rows)
QUERY PLAN
-------------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: (order_date = '2025-06-01'::date)
Filter: (amount > 9990)
-> Bitmap Index Scan on idx_big_orders_date
Index Cond: (order_date = '2025-06-01'::date)
(5 rows)
Java developers often find SQL easier once they map it onto the Streams API they already know. The mapping is not exact (SQL is set-based and the optimizer chooses the plan; a stream runs in the order you write it), but it is a useful memory aid.
SQL
Java Streams
Note
WHERE
filter
Rows before grouping
SELECT col
map
Projection
ORDER BY
sorted(Comparator)
Add a tiebreaker in both
LIMIT n
limit(n)
With OFFSET: skip(n).limit(m)
DISTINCT
distinct()
Uses equals/hashCode in Java
GROUP BY + aggregate
Collectors.groupingBy + downstream collector
Counting: counting()
HAVING
filter on the grouped map entries
Filters groups, not rows
JOIN
no direct equivalent (build a lookup Map)
Let the database join
The practical rule for a Java developer: do filtering, grouping and sorting in the database, and only stream what comes back. Pulling a whole table into a List to filter it in Java is the most common way to make a fast query slow. For the streams side of this, see the Top 50 Java Stream API coding questions and the complete Streams guide.
Common interview questions
Short follow-ups that interviewers use after the queries above (each links to the query that answers it):
“What is the difference between WHERE and HAVING?” — query 29.
“RANK, DENSE_RANK and ROW_NUMBER — what is the difference?” — queries 38 and 39.
“UNION versus UNION ALL?” — query 46.
“Why did NOT IN return nothing?” — query 24.
“How do you delete duplicates and keep one?” — queries 15 and 16.
“The query is slow — what do you do?” — queries 48 to 50 (EXPLAIN, then the index).
“INNER JOIN vs LEFT JOIN, with a real example?” — queries 17 and 18.
Comparing with = NULL. Use IS NULL; any comparison with NULL is unknown, not false (queries 3 and 24).
Using NOT IN on a column that can be NULL. One NULL empties the result; use NOT EXISTS (query 24).
Filtering the right table of a LEFT JOIN in WHERE. It turns the outer join into an inner join; put the condition in ON.
Selecting a non-grouped column. Everything in SELECT must be in GROUP BY or aggregated.
Pagination without a stable ORDER BY. Rows repeat or vanish across pages (query 5).
Wrapping an indexed column in a function.WHERE EXTRACT(MONTH FROM d) = 3 cannot use the index; use a range (query 49).
Running a duplicate delete without a transaction. Look at the rows first, delete inside BEGIN … ROLLBACK, then commit.
Assuming DISTINCT fixes a bad join. It hides row multiplication instead of explaining it.
Should you memorise 50 queries? No — memorise the patterns. If you can explain why queries 6, 12, 17, 24, 29, 39 and 49 behave as they do, you can derive most of the rest. Interviewers care more that you notice NULLs, ties and duplicate rows than that you recall syntax.
No Comments yet!