Joins & Query Techniques

1. What are the different types of JOINs, and how do they differ?

JOINs combine rows from two or more tables using a common column. The main types are INNER, LEFT, RIGHT, FULL, and CROSS JOIN.

Understanding tables first: A table is like a spreadsheet. Rows are records, columns are fields.

We’ll use two small tables to see every join in action.

Employees table:

employee_id	name	department_id
1	Ali	10
2	Bob	20
3	Cam	(none)

Departments table:

department_id	department_name
10	Sales
20	IT
30	HR

1. INNER JOIN Returns only rows where a match exists in both tables. Rows that don’t match on either side are dropped.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Result:

name	department_name
Ali	Sales
Bob	IT

Cam is missing because he has no department, so there’s no match.

2. LEFT JOIN Returns every row from the left table, plus matching rows from the right. Unmatched right columns become NULL.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Result:

name	department_name
Ali	Sales
Bob	IT
Cam	(none)

Cam is kept because LEFT JOIN preserves all left rows.

3. RIGHT JOIN Same idea as LEFT, but keeps every row from the right table, plus matches from the left.

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

Result:

name	department_name
Ali	Sales
Bob	IT
(none)	HR

HR is kept even though no employee belongs to it.

4. FULL OUTER JOIN Returns all rows from both tables. Wherever a match is missing, the absent side is NULL.

5. CROSS JOIN Pairs every row of one table with every row of the other. No condition is needed.

Key differences table:

Join	Keeps unmatched left rows?	Keeps unmatched right rows?
INNER	No	No
LEFT	Yes	No
RIGHT	No	Yes
FULL	Yes	Yes

When to use which: Use INNER when you only want data that exists on both sides.

Use LEFT when you must keep every row from the left table, even those with no match.

2. What is the difference between the WHERE and HAVING clauses?

WHERE filters rows before grouping. HAVING filters groups after grouping. You can’t use aggregate functions like COUNT() or SUM() inside WHERE — those must go in HAVING.

Why the order matters: A query does its work in steps. First it reads the rows from the table, then it filters them, then it groups them, and finally it aggregates them.

WHERE acts at the row-filtering step. It decides which rows are fed into the grouping.

HAVING acts after grouping. It decides which groups appear in the result.

Because of this, WHERE can only use plain column values. It cannot use the result of COUNT() or AVG(), because those don’t exist yet at that step.

Example — finding departments with more than 10 employees hired recently:

SELECT department_id, COUNT(*)
FROM employees
WHERE hire_date >= '2025-01-01'
GROUP BY department_id
HAVING COUNT(*) > 10;

Step by step:

1. WHERE hire_date >= '2025-01-01' keeps only recently hired employees.
2. GROUP BY department_id groups them by department.
3. HAVING COUNT(*) > 10 keeps only departments with more than 10.

Key differences table:

	WHERE	HAVING
Runs before grouping	Yes	No
Runs after grouping	No	Yes
Can use aggregate functions	No	Yes
Works with GROUP BY	Sometimes	Always

Key takeaway: Filtering rows with WHERE before grouping is also more efficient, because the database aggregates fewer rows.

3. What is a Self-Join, and when would you use it?

A self-join joins a table with itself. It’s used for hierarchical data or comparing rows within the same table.

Why join a table to itself? Sometimes the relationship you need is inside one table.

The classic example is employees and their managers — both live in the same employees table.

Example — employees table:

employee_id	name	manager_id
1	Ali	(none)
2	Bob	1
3	Cam	1

To show each employee’s manager, join the table to itself:

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

Result:

employee	manager
Ali	(none)
Bob	Ali
Cam	Ali

We used the table twice: once as e (employees) and once as m (managers). That’s the self-join.

Key takeaway: Use a self-join whenever the comparison is between rows of the same table — hierarchies, rankings, or finding pairs. Always use aliases so the two copies have clear names.

4. What is a Cross-Join?

A cross join matches every row in the first table with every row in the second. The result is called a Cartesian product.

How it works: If table A has 3 rows and table B has 4 rows, the result has 3 × 4 = 12 rows.

Every possible pair appears.

Example:

Colors table:

color
Red
Green

Sizes table:

size
S
M

SELECT * FROM colors CROSS JOIN sizes;

Result:

color	size
Red	S
Red	M
Green	S
Green	M

2 × 2 = 4 rows.

When is it used? Sometimes on purpose, like generating all combinations for testing.

Most of the time it’s a mistake — a join without a condition that accidentally creates millions of rows.

Key takeaway: A cross join produces every combination of rows. The row count is the product of both tables, so be careful — it grows fast.

5. What are the Set Operators (INTERSECT, EXCEPT/MINUS)?

Set operators combine the results of two queries. The main ones are UNION, UNION ALL, INTERSECT, and EXCEPT (also called MINUS).

The sample data:

Set A: {Ali, Bob}

Set B: {Bob, Cam}

UNION — everything in either set, no duplicates: Result: {Ali, Bob, Cam}

INTERSECT — only what’s in both sets:

SELECT name FROM employees
INTERSECT
SELECT name FROM managers;

Result: {Bob}

EXCEPT (MINUS) — in the first set but not the second:

SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

Result: {Ali}

Example of all three together:

Operator	A = {Ali, Bob}, B = {Bob, Cam}	Result
UNION	{Ali, Bob, Cam}	Both, no dupes
INTERSECT	{Bob}	Only common
EXCEPT	{Ali}	Only in A

Key takeaway: Use INTERSECT for common rows and EXCEPT for rows that exist in only one query. Both require the queries to have matching column counts and types.

6. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows. UNION ALL keeps every row, including duplicates.

Example:

Employees_2024: {Ali, Bob}

Employees_2025: {Bob, Cam}

UNION:

SELECT name FROM employees_2024
UNION
SELECT name FROM employees_2025;

Result: {Ali, Bob, Cam} — Bob appears once.

UNION ALL:

SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

Result: {Ali, Bob, Bob, Cam} — Bob appears twice.

Key differences table:

	UNION	UNION ALL
Removes duplicates	Yes	No
Extra sort step	Yes	No
Speed	Slower	Faster

Key takeaway: Use UNION ALL when duplicates are fine — it’s faster. Use UNION only when you need distinct rows.

7. What is a ‘Cartesian Product’ in SQL?

A Cartesian product is the result of a join without a condition — every row of one table combined with every row of another.

How it happens: When a JOIN (or comma-separated tables) has no ON condition, every possible pair is produced.

Example:

Employees: {Ali, Bob}

Departments: {Sales, IT, HR}

SELECT * FROM employees, departments;

Result: 2 × 3 = 6 rows — every employee paired with every department.

Why it’s dangerous: The result grows as a product.

1,000 × 1,000 = 1,000,000 rows.

Usually a mistake that floods the output.

Key takeaway: A Cartesian product combines every row with every row. Without a join condition, the row count multiplies fast.

8. What is a ‘Database Schema’?

A database schema is the blueprint of the database — its tables, columns, relationships, and constraints.

The idea: It’s the design document that defines how data is organized.

It doesn’t hold the data itself; it describes the structure.

What a schema defines:

• Tables
• Columns and their types
• Primary and foreign keys
• Constraints and indexes

Example of a tiny schema:

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT REFERENCES departments(department_id)
);

Key takeaway: A schema is the structure of the database: what tables exist, what’s in them, and how they connect.

9. What is the difference between a Clustered Index and a Table?

A table is the actual data. A clustered index is how that data is physically ordered.

The table: Holds the real rows and columns.

Think of it as the content.

The clustered index: Determines the physical order the rows are stored in on disk.

Think of it as the arrangement.

The analogy: A dictionary’s content is the table.

Its alphabetical arrangement is the clustered index.

If you change the arrangement, the content stays the same — just stored differently.

Key point: The clustered index is built on the table. A table can have only one physical order, so only one clustered index.

Key takeaway: The table stores data; the clustered index orders that data. They’re separate concepts tied to the same physical storage.

10. What is a Self-Join and when is it used?

A self-join joins a table with itself. It’s used for hierarchical data or comparing rows within the same table.

The idea: Sometimes the relationship lives inside one table.

The classic case: employees and their managers are both in the employees table.

Example:

employee_id	name	manager_id
1	Ali	(none)
2	Bob	1
3	Cam	1

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

Result:

employee	manager
Ali	(none)
Bob	Ali
Cam	Ali

The table appears twice — once as e, once as m.

Key takeaway: Use a self-join when rows in one table relate to other rows in the same table. Always use aliases so the two copies are clear.

11. What is the difference between EXISTS, IN, and JOIN?

Three ways to answer “are these rows related?” — they differ in semantics and performance:

Semantics:

• IN — checks a list of values: WHERE id IN (1,2,3) or WHERE id IN (SELECT id FROM orders). Classic gotcha: if the subquery returns NULL, NOT IN returns no rows at all (NULL comparisons are unknown, not true).
• EXISTS — a boolean test: “does at least one row satisfy this correlated subquery?” WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id). Returns the outer row if the subquery has any match — ignores NULLs safely.
• JOIN — actually combines the two tables, so you get columns from both. If you only want rows from one table (existence check), JOIN can duplicate rows when there are multiple matches — you’d need DISTINCT.

Performance:

• EXISTS is typically fastest — it stops at the first match and is optimized like a semi-join.
• IN with a subquery is often rewritten by the optimizer to a JOIN/semi-join, but historically had issues with large lists and NULL.
• JOIN materializes the combined rows, so it’s heavier when you only need existence.

-- EXISTS: are there orders? (no dupes, NULL-safe)
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- IN: same idea but breaks on NULLs in the subquery
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);

The interview one-liner: use EXISTS for existence checks (stop-early, NULL-safe), IN for small value lists, and JOIN when you actually need columns from both tables. EXISTS and IN both answer “does it exist?”; JOIN answers “give me the combined data.”