Welcome To TechBrothersIT: PostgreSQL Interview Questions and Answers Part 1

PostgreSQL Interview Q&A · Part 1 of 10

In this post we are going to cover joins and NULL traps in PostgreSQL: INNER, LEFT and FULL OUTER JOIN, anti joins, the NOT IN trap, filters in ON versus WHERE, the one-to-many join that inflates totals, NULL comparisons, and LATERAL joins. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.

Joins are in almost every interview, and not because the syntax is hard. The interviewer wants to know if you understand what a join does to your rows: which rows it keeps, which it drops, and when it quietly multiplies them. We answer each question with the short answer first, then a picture of what happens to the rows, then the real query and its output.

Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.

VIDEO COMING SOON

Part 1 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.

On this page

Let's create some sample data
Question 1: HR wants every department with its employees, including the department that has nobody in it, and the employees who have no department yet. Which join gives you that list?
Question 2: Marketing wants to email the customers who have never placed an order. How do you find them in PostgreSQL?
Question 3: A teammate used NOT IN to find the departments that have no employees. The query returns zero rows, even though Finance is empty. What went wrong?
Question 4: A manager wants every customer, with their shipped orders if they have any. Your LEFT JOIN returns only two customers. Why?
Question 5: Finance says delivered orders are worth 4,175. Your report, after joining orders to payments, shows 4,825. Why did revenue go up?
Question 6: You filter customers whose region is not West. Ravi, whose region is unknown, is missing from the result. Why, and how do you include him?
Question 7: The support team wants each customer with their two most recent orders. Customers with no orders must still appear. How do you write it in PostgreSQL?
Key points to remember
The full SQL script

Let's create some sample data

We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.

Sample data - SQL script (PostgreSQL)

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(20),
  dept_id    INT,            -- NULL = not assigned yet
  manager_id INT,            -- NULL = top of the company
  salary     INT,
  hire_date  DATE
);
INSERT INTO employees VALUES
  (1, 'Aisha',  NULL, NULL, 150000, '2018-03-01'),
  (2, 'Omar',   20,   1,    120000, '2019-06-15'),
  (3, 'Lina',   20,   2,     90000, '2021-01-10'),
  (4, 'Ravi',   20,   2,     90000, '2022-04-05'),
  (5, 'Mei',    20,   2,     75000, '2024-09-01'),
  (6, 'Sara',   10,   1,     95000, '2020-02-20'),
  (7, 'Daniel', 10,   6,    105000, '2021-07-01'),
  (8, 'Fatima', 10,   6,     60000, '2023-11-12'),
  (9, 'John',   30,   1,     70000, '2019-09-30'),
  (10,'Zara',   NULL, 9,     55000, '2026-01-05');

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(20),
  city        VARCHAR(20),
  region      VARCHAR(10),    -- NULL = unknown
  email       VARCHAR(40)
);
INSERT INTO customers VALUES
  (1, 'Sara Khan',  'Toronto', 'East', 'sara@mail.com'),
  (2, 'Omar Ali',   'Dallas',  'West', 'omar@mail.com'),
  (3, 'Lina Park',  'Seattle', 'West', 'lina@mail.com'),
  (4, 'Mei Chen',   'Boston',  'East', 'mei@mail.com'),
  (5, 'Ravi Patel', 'Austin',  NULL,   'ravi@mail.com'),
  (6, 'Chen Wu',    'Denver',  'West', 'chen@mail.com'),
  (7, 'Sara K.',    'Toronto', 'East', 'sara@mail.com');

CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price NUMERIC(10,2));
INSERT INTO products VALUES
  (1, 'Laptop',   'Electronics', 1200.00),
  (2, 'Monitor',  'Electronics',  300.00),
  (3, 'Keyboard', 'Accessories',   50.00),
  (4, 'Mouse',    'Accessories',   25.00),
  (5, 'Desk',     'Furniture',    400.00),
  (6, 'Chair',    'Furniture',    250.00);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  status      VARCHAR(10),
  amount      NUMERIC(10,2)
);
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05', 'Delivered', 1250.00),
  (1002, 2, '2026-01-18', 'Delivered',  300.00),
  (1003, 4, '2026-02-02', 'Delivered',  650.00),
  (1004, 1, '2026-02-14', 'Cancelled',   50.00),
  (1005, 3, '2026-02-20', 'Delivered', 1200.00),
  (1006, 4, '2026-03-03', 'Shipped',    275.00),
  (1007, 5, '2026-03-15', 'Delivered',  400.00),
  (1008, 2, '2026-04-01', 'Delivered',   75.00),
  (1009, 4, '2026-04-22', 'Delivered',  250.00),
  (1010, 1, '2026-05-09', 'Shipped',    300.00),
  (1011, 3, '2026-05-30', 'Cancelled',  400.00),
  (1012, 7, '2026-06-11', 'Delivered',   50.00);

CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price NUMERIC(10,2));
INSERT INTO order_items VALUES
  (1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
  (1002, 2, 1, 300.00),
  (1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
  (1004, 3, 1, 50.00),
  (1005, 1, 1, 1200.00),
  (1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
  (1007, 5, 1, 400.00),
  (1008, 4, 3, 25.00),
  (1009, 6, 1, 250.00),
  (1010, 2, 1, 300.00),
  (1011, 5, 1, 400.00),
  (1012, 3, 1, 50.00);

CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount NUMERIC(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00),
  (3, 1003,  400.00), (4, 1003, 250.00),   -- order 1003 was paid in two parts
  (5, 1005, 1200.00), (6, 1007, 400.00),
  (7, 1008,   75.00), (8, 1009, 250.00), (9, 1012, 50.00);

Question 1: HR wants every department with its employees, including the department that has nobody in it, and the employees who have no department yet. Which join gives you that list?

Say this first

A FULL OUTER JOIN. INNER keeps only the matches, LEFT keeps every row from the left table, and FULL keeps the unmatched rows from both sides.

Let's watch the rows. Departments are on the left, and employees are on the right. An INNER JOIN keeps only the pairs that match, so Sales, Engineering and HR find their people.

departments

dept_id	dept_name
10	Sales
20	Engineering
30	HR
40	Finance

employees

name	dept_id
Sara	10
Omar	20
Lina	20
John	30
Aisha	NULL
Zara	NULL

INNER JOINonly the matching pairs

LEFT JOIN+ Finance, with NULL employee

FULL OUTER JOIN+ Aisha and Zara, with NULL department

Finance has no employees, so an inner join drops it. A LEFT JOIN keeps it, with NULLs on the employee side. Aisha and Zara have no department. Only a FULL OUTER JOIN keeps them as well.

Now the real query. The inner join returns 8 rows, the left join returns 9, and the full outer join returns 11.

SQL - join row counts

SELECT 'INNER' AS join_type, COUNT(*) AS row_count
FROM departments d
INNER JOIN employees e ON e.dept_id = d.dept_id
UNION ALL
SELECT 'LEFT', COUNT(*)
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
UNION ALL
SELECT 'FULL OUTER', COUNT(*)
FROM departments d
FULL OUTER JOIN employees e ON e.dept_id = d.dept_id;

Output (PostgreSQL 16)3 rows

join_type	row_count
INNER	8
LEFT	9
FULL OUTER	11

Here is the full outer join itself. Finance shows up with a NULL employee, and Aisha and Zara show up with a NULL department. That is exactly the list HR asked for.

SQL - full outer join

SELECT d.dept_name, e.name AS employee
FROM departments d
FULL OUTER JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.name;

Output (PostgreSQL 16)11 rows

dept_name	employee
Engineering	Lina
Engineering	Mei
Engineering	Omar
Engineering	Ravi
Finance	NULL
HR	John
Sales	Daniel
Sales	Fatima
Sales	Sara
NULL	Aisha
NULL	Zara

One tip for the interview. A RIGHT JOIN is just a LEFT JOIN with the tables swapped, so most teams always write LEFT JOIN and keep the main table first.

Interview tip

RIGHT JOIN = LEFT JOIN with the tables swapped. Write LEFT, main table first.

Question 2: Marketing wants to email the customers who have never placed an order. How do you find them in PostgreSQL?

Say this first

Use an anti join: keep the customers for whom no order exists. Write it with NOT EXISTS, or with a LEFT JOIN that keeps only the rows where the order side IS NULL.

Picture it like this. For every customer, Postgres looks for a matching order. Customers 1 to 5 and customer 7 each find one, so they are dropped. Chen Wu finds nothing, and that is the row an anti join keeps.

customers

customer_id	name
1	Sara Khan
2	Omar Ali
3	Lina Park
4	Mei Chen
5	Ravi Patel
6	Chen Wu
7	Sara K.

orders

order_id	customer_id
1001	1
1002	2
1005	3
1003	4
1007	5
1012	7

Match foundthe customer has ordered: drop the row

No matchnever ordered: keep the row

Here it is with NOT EXISTS. For each customer, the subquery checks whether any order exists. One row comes back: customer 6, Chen Wu.

SQL - not exists

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

Output (PostgreSQL 16)1 row

customer_id	name
6	Chen Wu

The second way is a LEFT JOIN, keeping only the rows where the order side is NULL. Same answer. And look at the plan: Postgres turns it into an anti join, the same plan it uses for NOT EXISTS.

SQL - left join is null

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

SQL - plan ne

EXPLAIN (COSTS OFF)
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

Query plan (PostgreSQL 16)

Hash Right Anti Join
  Hash Cond: (o.customer_id = c.customer_id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on customers c

In a real project, I prefer NOT EXISTS. The intent is clear, it never duplicates customers, and it is safe with NULLs. NOT IN is not safe, and that is the next question.

Interview tip

Prefer NOT EXISTS: clear intent, no duplicates, safe with NULLs.

Question 3: A teammate used NOT IN to find the departments that have no employees. The query returns zero rows, even though Finance is empty. What went wrong?

Say this first

The subquery returned a NULL. Comparing anything with NULL gives unknown, never true, so with NOT IN no row can pass. Use NOT EXISTS instead.

Let's follow Finance, department 40. NOT IN is really a chain of not-equal checks joined by AND. 40 is not 10: true. Not 20: true. Not 30: true. But is 40 not equal to NULL? Postgres says unknown. True AND unknown is unknown, and WHERE keeps only true, so Finance is dropped.

40 NOT IN (10, 20, 30, NULL)

is the same as

40 <> 10true

AND

40 <> 20true

AND

40 <> 30true

AND

40 <> NULLunknown

true AND true AND true AND unknown = unknown → the row is dropped

Here is the teammate's query. The subquery reads department IDs from employees, and Aisha and Zara have NULL there. The result: zero rows.

SQL - not in trap

SELECT d.dept_id, d.dept_name
FROM departments d
WHERE d.dept_id NOT IN (SELECT e.dept_id
                        FROM employees e);

Output (PostgreSQL 16)0 rows

dept_id	dept_name

Rewrite it with NOT EXISTS. It only asks whether a matching row exists, so NULLs cannot break it. Now Finance comes back.

SQL - not exists fix

SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e
                  WHERE e.dept_id = d.dept_id);

Output (PostgreSQL 16)1 row

dept_id	dept_name
40	Finance

If you must keep NOT IN, filter the NULLs out inside the subquery. But NOT EXISTS is the habit that never bites you.

Interview tip

Keeping NOT IN? Add WHERE e.dept_id IS NOT NULL inside the subquery.

Question 4: A manager wants every customer, with their shipped orders if they have any. Your LEFT JOIN returns only two customers. Why?

Say this first

The filter on the orders table is in WHERE. WHERE runs after the join and removes the NULL rows, so the LEFT JOIN quietly becomes an INNER JOIN. Move the filter into ON.

Here is what the LEFT JOIN produces first. Seven customers. Two have a shipped order, and the other five get NULL. Now WHERE status equals Shipped runs on these rows. NULL equals Shipped is not true, so all five NULL rows are thrown away. Only two customers are left.

after the LEFT JOIN

c.name	o.status
Sara Khan	Shipped
Omar Ali	NULL
Lina Park	NULL
Mei Chen	Shipped
Ravi Patel	NULL
Chen Wu	NULL
Sara K.	NULL

WHERE o.status = 'Shipped'runs after the join, on every row

NULL = 'Shipped' → unknown → row removed

2 customers left. The LEFT JOIN now behaves like an INNER JOIN.

This is the query with the filter in WHERE. Only Sara Khan and Mei Chen come back.

SQL - filter in where

SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Shipped'
ORDER BY c.customer_id;

Output (PostgreSQL 16)2 rows

name	order_id	status
Sara Khan	1010	Shipped
Mei Chen	1006	Shipped

Move the condition into the ON clause. Now it only decides which orders match, and it does not remove customers. All seven customers come back, and the ones with no shipped order show NULL.

SQL - filter in on

SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.status = 'Shipped'
ORDER BY c.customer_id;

Output (PostgreSQL 16)7 rows

name	order_id	status
Sara Khan	1010	Shipped
Omar Ali	NULL	NULL
Lina Park	NULL	NULL
Mei Chen	1006	Shipped
Ravi Patel	NULL	NULL
Chen Wu	NULL	NULL
Sara K.	NULL	NULL

The rule: in a LEFT JOIN, conditions on the right table go in ON. Conditions on the left table go in WHERE.

Interview tip

LEFT JOIN: right-table filters in ON, left-table filters in WHERE.

Question 5: Finance says delivered orders are worth 4,175. Your report, after joining orders to payments, shows 4,825. Why did revenue go up?

Say this first

The join is one to many. An order with two payments appears twice, so its amount is summed twice. Aggregate payments to one row per order first, then join.

Look at order 1003. Its amount is 650, but the customer paid in two parts, so it matches two payment rows. After the join, the order appears twice, and 650 is counted twice. That extra 650 is the difference Finance found.

orders

order_id	amount
1002	300.00
1003	650.00
1005	1200.00

payments

payment_id	order_id	paid
2	1002	300.00
3	1003	400.00
4	1003	250.00
5	1005	1200.00

after the join

order_id	amount	paid
1002	300.00	300.00
1003	650.00	400.00
1003	650.00	250.00
1005	1200.00	1200.00

Here is the report query. It joins every order to its payments and sums the order amount. 4,825. Wrong.

SQL - wrong total

SELECT SUM(o.amount) AS revenue
FROM orders o
JOIN payments p ON p.order_id = o.order_id;

Output (PostgreSQL 16)1 row

revenue
4825.00

The fix: first collapse payments to one row per order in a CTE, then join. The grain is one row per order again, and the total is 4,175.

SQL - aggregate first

WITH paid AS (
  SELECT order_id, SUM(paid_amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT SUM(o.amount) AS revenue, SUM(paid.paid) AS paid
FROM orders o
JOIN paid ON paid.order_id = o.order_id;

Output (PostgreSQL 16)1 row

revenue	paid
4175.00	4175.00

Before any join, ask two questions. What is one row in each table? And is the join key unique on the right side? If it is not, aggregate first, or use EXISTS.

Interview tip

Before a join: what is the grain of each table, and is the key unique on the right side?

Question 6: You filter customers whose region is not West. Ravi, whose region is unknown, is missing from the result. Why, and how do you include him?

Say this first

NULL <> 'West' is not true, it is unknown, and WHERE keeps only true. In Postgres use IS DISTINCT FROM, which treats NULL like a normal value.

Three customers, three answers. East is not West: true, the row is kept. West is not West: false, dropped. NULL is not West: unknown, and unknown is dropped too. With IS DISTINCT FROM, the same NULL gives true, so Ravi stays.

how the filter sees each row

name	region	region <> 'West'	region IS DISTINCT FROM 'West'
Sara Khan	East	true	true
Omar Ali	West	false	false
Ravi Patel	NULL	unknown	true

WHERE keeps only true.false and unknown are both removed

IS DISTINCT FROMNULL-safe not-equal: never returns unknown

With the normal not-equal operator, we get three customers, and Ravi is missing.

SQL - not equal

SELECT customer_id, name, region
FROM customers
WHERE region <> 'West'
ORDER BY customer_id;

Output (PostgreSQL 16)3 rows

customer_id	name	region
1	Sara Khan	East
4	Mei Chen	East
7	Sara K.	East

Change it to IS DISTINCT FROM. Four customers, and Ravi is back with his NULL region.

SQL - is distinct from

SELECT customer_id, name, region
FROM customers
WHERE region IS DISTINCT FROM 'West'
ORDER BY customer_id;

Output (PostgreSQL 16)4 rows

customer_id	name	region
1	Sara Khan	East
4	Mei Chen	East
5	Ravi Patel	NULL
7	Sara K.	East

The same rule explains why equals NULL never matches anything. Always write IS NULL or IS NOT NULL.

Interview tip

= NULL never matches. Write IS NULL / IS NOT NULL.

Question 7: The support team wants each customer with their two most recent orders. Customers with no orders must still appear. How do you write it in PostgreSQL?

Say this first

Use a LEFT JOIN LATERAL. A lateral subquery runs once per customer and can see that customer's row, so it can use ORDER BY and LIMIT 2 for each one.

Think of it as a loop. Postgres takes Sara Khan, runs the subquery for her, and gets her two newest orders. Then Mei Chen: her two newest. Then Chen Wu: the subquery returns nothing, and because it is a LEFT join, he is kept with NULLs.

customers

customer_id	name
1	Sara Khan
4	Mei Chen
6	Chen Wu

for each row

LATERAL ( SELECT order_id, order_date FROM orders o WHERE o.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 2 )

result

name	order_id
Sara Khan	1010
Sara Khan	1004
Mei Chen	1009
Mei Chen	1006
Chen Wu	NULL

Here is the real query. The lateral subquery filters on the outer customer, sorts by date, and takes two rows. ON true means: keep whatever the subquery returned.

SQL - top2 lateral

SELECT c.name, o.order_id, o.order_date, o.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, order_date, amount
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 2
) o ON true
ORDER BY c.customer_id, o.order_date DESC;

Output (PostgreSQL 16)11 rows

name	order_id	order_date	amount
Sara Khan	1010	2026-05-09	300.00
Sara Khan	1004	2026-02-14	50.00
Omar Ali	1008	2026-04-01	75.00
Omar Ali	1002	2026-01-18	300.00
Lina Park	1011	2026-05-30	400.00
Lina Park	1005	2026-02-20	1200.00
Mei Chen	1009	2026-04-22	250.00
Mei Chen	1006	2026-03-03	275.00
Ravi Patel	1007	2026-03-15	400.00
Chen Wu	NULL	NULL	NULL
Sara K.	1012	2026-06-11	50.00

Every customer has at most two rows, and Chen Wu is still there with NULLs. With an index on customer ID and order date, each lateral lookup is very fast.

Interview tip

Add an index on orders (customer_id, order_date DESC) so each lookup is an index scan.

Key points to remember

INNER keeps matches, LEFT keeps every left row, FULL keeps both sides
Anti join: use NOT EXISTS. NOT IN breaks when the subquery returns a NULL
LEFT JOIN: filters on the right table go in ON, not WHERE
A one-to-many join multiplies rows: aggregate first, then join
IS DISTINCT FROM compares NULLs safely, and LATERAL gives top N per row

The full SQL script

Here is everything in one script: the sample data and every query from this post, in order.

Show the full SQL script (sample data and every query)

Full script - copy and run top to bottom

-- ========================================================================
-- PostgreSQL Interview Q&A #1 - Joins and NULL traps
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================

-- Sample data
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(20),
  dept_id    INT,            -- NULL = not assigned yet
  manager_id INT,            -- NULL = top of the company
  salary     INT,
  hire_date  DATE
);
INSERT INTO employees VALUES
  (1, 'Aisha',  NULL, NULL, 150000, '2018-03-01'),
  (2, 'Omar',   20,   1,    120000, '2019-06-15'),
  (3, 'Lina',   20,   2,     90000, '2021-01-10'),
  (4, 'Ravi',   20,   2,     90000, '2022-04-05'),
  (5, 'Mei',    20,   2,     75000, '2024-09-01'),
  (6, 'Sara',   10,   1,     95000, '2020-02-20'),
  (7, 'Daniel', 10,   6,    105000, '2021-07-01'),
  (8, 'Fatima', 10,   6,     60000, '2023-11-12'),
  (9, 'John',   30,   1,     70000, '2019-09-30'),
  (10,'Zara',   NULL, 9,     55000, '2026-01-05');

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(20),
  city        VARCHAR(20),
  region      VARCHAR(10),    -- NULL = unknown
  email       VARCHAR(40)
);
INSERT INTO customers VALUES
  (1, 'Sara Khan',  'Toronto', 'East', 'sara@mail.com'),
  (2, 'Omar Ali',   'Dallas',  'West', 'omar@mail.com'),
  (3, 'Lina Park',  'Seattle', 'West', 'lina@mail.com'),
  (4, 'Mei Chen',   'Boston',  'East', 'mei@mail.com'),
  (5, 'Ravi Patel', 'Austin',  NULL,   'ravi@mail.com'),
  (6, 'Chen Wu',    'Denver',  'West', 'chen@mail.com'),
  (7, 'Sara K.',    'Toronto', 'East', 'sara@mail.com');

CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price NUMERIC(10,2));
INSERT INTO products VALUES
  (1, 'Laptop',   'Electronics', 1200.00),
  (2, 'Monitor',  'Electronics',  300.00),
  (3, 'Keyboard', 'Accessories',   50.00),
  (4, 'Mouse',    'Accessories',   25.00),
  (5, 'Desk',     'Furniture',    400.00),
  (6, 'Chair',    'Furniture',    250.00);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  status      VARCHAR(10),
  amount      NUMERIC(10,2)
);
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05', 'Delivered', 1250.00),
  (1002, 2, '2026-01-18', 'Delivered',  300.00),
  (1003, 4, '2026-02-02', 'Delivered',  650.00),
  (1004, 1, '2026-02-14', 'Cancelled',   50.00),
  (1005, 3, '2026-02-20', 'Delivered', 1200.00),
  (1006, 4, '2026-03-03', 'Shipped',    275.00),
  (1007, 5, '2026-03-15', 'Delivered',  400.00),
  (1008, 2, '2026-04-01', 'Delivered',   75.00),
  (1009, 4, '2026-04-22', 'Delivered',  250.00),
  (1010, 1, '2026-05-09', 'Shipped',    300.00),
  (1011, 3, '2026-05-30', 'Cancelled',  400.00),
  (1012, 7, '2026-06-11', 'Delivered',   50.00);

CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price NUMERIC(10,2));
INSERT INTO order_items VALUES
  (1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
  (1002, 2, 1, 300.00),
  (1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
  (1004, 3, 1, 50.00),
  (1005, 1, 1, 1200.00),
  (1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
  (1007, 5, 1, 400.00),
  (1008, 4, 3, 25.00),
  (1009, 6, 1, 250.00),
  (1010, 2, 1, 300.00),
  (1011, 5, 1, 400.00),
  (1012, 3, 1, 50.00);

CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount NUMERIC(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00),
  (3, 1003,  400.00), (4, 1003, 250.00),   -- order 1003 was paid in two parts
  (5, 1005, 1200.00), (6, 1007, 400.00),
  (7, 1008,   75.00), (8, 1009, 250.00), (9, 1012, 50.00);

-- Step 1 (question 1): join row counts
SELECT 'INNER' AS join_type, COUNT(*) AS row_count
FROM departments d
INNER JOIN employees e ON e.dept_id = d.dept_id
UNION ALL
SELECT 'LEFT', COUNT(*)
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
UNION ALL
SELECT 'FULL OUTER', COUNT(*)
FROM departments d
FULL OUTER JOIN employees e ON e.dept_id = d.dept_id;

-- Step 2 (question 1): full outer join
SELECT d.dept_name, e.name AS employee
FROM departments d
FULL OUTER JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.name;

-- Step 3 (question 2): not exists
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

-- Step 4 (question 2): left join is null
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

-- Step 5 (question 2): plan ne
EXPLAIN (COSTS OFF)
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

-- Step 6 (question 3): not in trap
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE d.dept_id NOT IN (SELECT e.dept_id
                        FROM employees e);

-- Step 7 (question 3): not exists fix
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e
                  WHERE e.dept_id = d.dept_id);

-- Step 8 (question 4): filter in where
SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Shipped'
ORDER BY c.customer_id;

-- Step 9 (question 4): filter in on
SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.status = 'Shipped'
ORDER BY c.customer_id;

-- Step 10 (question 5): wrong total
SELECT SUM(o.amount) AS revenue
FROM orders o
JOIN payments p ON p.order_id = o.order_id;

-- Step 11 (question 5): aggregate first
WITH paid AS (
  SELECT order_id, SUM(paid_amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT SUM(o.amount) AS revenue, SUM(paid.paid) AS paid
FROM orders o
JOIN paid ON paid.order_id = o.order_id;

-- Step 12 (question 6): not equal
SELECT customer_id, name, region
FROM customers
WHERE region <> 'West'
ORDER BY customer_id;

-- Step 13 (question 6): is distinct from
SELECT customer_id, name, region
FROM customers
WHERE region IS DISTINCT FROM 'West'
ORDER BY customer_id;

-- Step 14 (question 7): top2 lateral
SELECT c.name, o.order_id, o.order_date, o.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, order_date, amount
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 2
) o ON true
ORDER BY c.customer_id, o.order_date DESC;

Next in this series

Part 2: GROUP BY, HAVING and FILTER

The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.

Welcome To TechBrothersIT

Label

PostgreSQL Interview Questions and Answers Part 1 - Joins and NULL traps

Let's create some sample data

Question 1: HR wants every department with its employees, including the department that has nobody in it, and the employees who have no department yet. Which join gives you that list?

Question 2: Marketing wants to email the customers who have never placed an order. How do you find them in PostgreSQL?

Question 3: A teammate used NOT IN to find the departments that have no employees. The query returns zero rows, even though Finance is empty. What went wrong?

Question 4: A manager wants every customer, with their shipped orders if they have any. Your LEFT JOIN returns only two customers. Why?

Question 5: Finance says delivered orders are worth 4,175. Your report, after joining orders to payments, shows 4,825. Why did revenue go up?

Question 6: You filter customers whose region is not West. Ravi, whose region is unknown, is missing from the result. Why, and how do you include him?

Question 7: The support team wants each customer with their two most recent orders. Customers with no orders must still appear. How do you write it in PostgreSQL?

Key points to remember

The full SQL script

No comments:

Post a Comment