Top 26 DBMS Joins Interview Questions & Answers (2026)

Last Updated: June 2026 | Level: Beginner to Advanced | Format: Q&A with SQL Examples and Diagrams

SQL joins are tested in virtually every backend SDE and data engineering interview. Candidates report that join type identification from scenarios and writing complex multi-table queries appear at product companies (Flipkart, Zomato, Swiggy) and in DBMS written rounds at service companies. Based on public preparation resources and candidate-reported interview accounts, LEFT JOIN with NULL detection and self-join patterns are the most commonly tested join questions.

---

Table of Contents

1. Join Basics (Q1-Q8)
2. Outer Joins (Q9-Q14)
3. Advanced Joins (Q15-Q21)
4. Performance and Optimization (Q22-Q26)

---

Join Basics

Q1. What is a SQL JOIN? What are the main types? Easy

A JOIN combines rows from two or more tables based on a related column between them.

Main join types:

Join Type	Returns
INNER JOIN	Only rows with matching values in BOTH tables
LEFT (OUTER) JOIN	All rows from left table + matching rows from right (NULLs for no match)
RIGHT (OUTER) JOIN	All rows from right table + matching rows from left (NULLs for no match)
FULL OUTER JOIN	All rows from both tables (NULLs on either side for no match)
CROSS JOIN	Cartesian product of both tables (every combination)
SELF JOIN	A table joined with itself
NATURAL JOIN	Auto-joins on columns with the same name (rarely used in production)

Sample tables used throughout this guide:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name   VARCHAR(50),
    dept_id INT,
    salary INT,
    manager_id INT
);

CREATE TABLE departments (
    dept_id  INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location  VARCHAR(50)
);

---

Q2. Explain INNER JOIN with an example. Easy

INNER JOIN returns only the rows where the join condition is TRUE in both tables.

SELECT e.name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result concept (Venn diagram in words): Only the intersection of both tables. Employees with no matching department are excluded. Departments with no employees are excluded.

Sample data:

employees:                    departments:
emp_id | name  | dept_id      dept_id | dept_name
1      | Alice | 10           10      | Engineering
2      | Bob   | 20           20      | Marketing
3      | Carol | NULL (no dept)  30   | HR (no employees)

INNER JOIN result:
name  | dept_name
Alice | Engineering
Bob   | Marketing
-- Carol excluded (no dept_id match)
-- HR excluded (no employees)

---

Q3. What is the difference between JOIN and INNER JOIN? Easy

There is NO difference. JOIN and INNER JOIN are identical in standard SQL. INNER is the default join type.

-- These are equivalent:
SELECT * FROM A JOIN B ON A.id = B.id;
SELECT * FROM A INNER JOIN B ON A.id = B.id;

Most style guides recommend writing INNER JOIN explicitly for clarity when writing complex queries with multiple join types.

---

Q4. What is NATURAL JOIN? What are its risks? Easy

NATURAL JOIN automatically joins tables on ALL columns with the same name and compatible data types. No explicit ON or USING clause.

SELECT * FROM employees NATURAL JOIN departments;
-- Automatically joins on dept_id (same name in both tables)
-- Equivalent to: JOIN departments ON employees.dept_id = departments.dept_id

Risks:

1. Brittle: If a column with the same name is added to either table for a different purpose, the join condition silently changes.
2. Ambiguous: It is unclear which columns are being joined without inspecting both schemas.
3. Unexpected exclusion: Columns with same name but different purpose (e.g., a created_at column in both tables) cause an unintended join condition.

Best practice: Never use NATURAL JOIN in production code. Always use explicit JOIN ON or JOIN USING for clarity and safety.

---

Q5. What is CROSS JOIN? When is it useful? Easy

CROSS JOIN produces the Cartesian product of two tables: every row in the first table is combined with every row in the second table.

SELECT e.name, d.dept_name
FROM employees e CROSS JOIN departments d;
-- Result: 3 employees * 3 departments = 9 rows

Result:

name  | dept_name
Alice | Engineering
Alice | Marketing
Alice | HR
Bob   | Engineering
Bob   | Marketing
Bob   | HR
Carol | Engineering
Carol | Marketing
Carol | HR

When useful:

1. Generating test data combinations.
2. Calendar tables: CROSS JOIN dates with time slots to get all appointments.
3. Pairing every customer with every product for analysis.

Warning: CROSS JOIN on two large tables is very expensive. 1000 rows x 1000 rows = 1,000,000 rows. Always make sure CROSS JOIN is intentional.

---

Q6. What is EQUI JOIN vs NON-EQUI JOIN? Medium

Type	Join Condition	Example
Equi Join	Uses equality operator (=)	ON e.dept_id = d.dept_id
Non-Equi Join	Uses non-equality operators (<, >, <=, >=, BETWEEN, !=)	ON e.salary BETWEEN s.min_sal AND s.max_sal

Non-Equi JOIN example: Salary grade lookup

CREATE TABLE salary_grades (
    grade CHAR(1),
    min_sal INT,
    max_sal INT
);
-- A: 0-30000, B: 30001-60000, C: 60001+

SELECT e.name, e.salary, sg.grade
FROM employees e
JOIN salary_grades sg ON e.salary BETWEEN sg.min_sal AND sg.max_sal;

-- Result:
-- Alice, 45000, B
-- Bob, 75000, C
-- Carol, 25000, A

Non-equi joins require full nested-loop evaluation (no B+tree optimization via equality). They are generally slower and require index scan or hash cannot be used (no equality condition to hash on).

---

Q7. How does a JOIN work internally? Medium

The database uses one of three join algorithms (chosen by the query optimizer based on table sizes and available indexes):

1. Nested Loop Join:

For each row in table A:
    For each row in table B:
        If join condition satisfied: output combined row

Time: O(|A| * |B|)
Best for: small tables, or when inner table has an index on join column.

2. Hash Join:

Phase 1 (Build): Hash all rows of smaller table into a hash table on join key.
Phase 2 (Probe): For each row in larger table, look up hash table.

Time: O(|A| + |B|)
Space: O(min(|A|, |B|)) for hash table
Best for: large tables without usable index.

3. Sort-Merge Join:

Sort both tables by join key (if not already sorted).
Merge the sorted sequences.

Time: O(|A| log |A| + |B| log |B|)
Best for: tables already sorted by join key, or when result must be sorted.

---

Q8. How do you join more than two tables? Easy

Chain JOIN clauses. SQL processes them left to right (unless the optimizer reorders).

SELECT e.name, d.dept_name, l.city
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE l.country = 'India';

Four-table join with different join types:

SELECT
    e.name,
    d.dept_name,
    p.project_name,
    m.name AS manager_name
FROM employees e
INNER JOIN departments d  ON e.dept_id = d.dept_id
LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
LEFT JOIN projects p ON pa.project_id = p.project_id
LEFT JOIN employees m ON e.manager_id = m.emp_id;  -- self-join for manager

Join order matters for performance: The optimizer typically re-orders joins, but with hints or when statistics are poor, the written order can affect performance.

---

Outer Joins

Q9. Explain LEFT JOIN with an example. Easy

LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from the left table plus matching rows from the right table. Where no match exists, right table columns appear as NULL.

SELECT e.name, e.salary, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Result:

name  | salary | dept_name
Alice | 45000  | Engineering  (matched)
Bob   | 75000  | Marketing    (matched)
Carol | 25000  | NULL         (no dept_id -- kept from left, NULL from right)

Carol appears even though she has no department (NULL dept_id). The Engineering and Marketing departments without employees (HR) do NOT appear.

Common use case: find rows in left table with no match in right table (anti-join):

-- Find employees with no department assignment:
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;  -- NULL means no match in departments

---

Q10. Explain RIGHT JOIN with an example. Easy

RIGHT JOIN is the mirror of LEFT JOIN: returns ALL rows from the right table, plus matching rows from the left. Non-matching left rows appear as NULL.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Result:

name  | dept_name
Alice | Engineering  (matched)
Bob   | Marketing    (matched)
NULL  | HR           (no employees in HR -- right table kept, left is NULL)

Practical note: Most SQL developers prefer LEFT JOIN (rewrite RIGHT JOIN by swapping table order). A RIGHT JOIN B is equivalent to B LEFT JOIN A.

-- These are equivalent:
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
SELECT * FROM B LEFT JOIN A ON B.id = A.id;

---

Q11. Explain FULL OUTER JOIN. Medium

FULL OUTER JOIN returns ALL rows from BOTH tables. Non-matching rows from either side appear with NULLs for the other table's columns.

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

Result:

name  | dept_name
Alice | Engineering   (both matched)
Bob   | Marketing     (both matched)
Carol | NULL          (employee with no dept)
NULL  | HR            (dept with no employees)

MySQL does not support FULL OUTER JOIN directly. Emulate with UNION:

-- MySQL FULL OUTER JOIN emulation:
SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

---

Q12. What is an anti-join? How do you write one? Medium

An anti-join returns rows from one table that have NO matching row in the other table.

Method 1: LEFT JOIN + IS NULL (most common):

-- Departments with no employees:
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Method 2: NOT EXISTS (often more readable):

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

Method 3: NOT IN (careful with NULLs):

SELECT dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);
-- Must filter NULLs: NOT IN with NULL in subquery returns no rows (SQL NULL behavior)

Performance: NOT EXISTS and LEFT JOIN + IS NULL are generally equivalent in modern databases. NOT IN is slower for large subqueries and has NULL trap.

---

Q13. What is the difference between WHERE and HAVING with joins? Medium

Aspect	WHERE	HAVING
When applied	Before aggregation (filters individual rows)	After aggregation (filters groups)
Works with	Any column	Aggregate functions (COUNT, SUM, AVG, MAX, MIN)
GROUP BY	Not required	Used with GROUP BY (or alone in rare cases)

Example:

-- Count employees per department, only include depts with > 5 employees
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE d.location = 'Mumbai'         -- Filter: only Mumbai departments (row-level filter)
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 5;         -- Filter: only groups with > 5 employees

Execution order:

FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

WHERE filters happen BEFORE grouping (rows removed before aggregation). HAVING filters happen AFTER grouping (groups removed after aggregation).

---

Q14. What is a semi-join? Hard

A semi-join returns rows from one table for which a matching row EXISTS in the other table, without returning columns from the other table. Unlike a regular join, it does not duplicate rows even if multiple matches exist.

Written as EXISTS subquery:

-- Find all departments that have at least one employee:
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

Comparison with INNER JOIN (which duplicates rows):

-- INNER JOIN approach (may produce duplicates if multiple employees per dept):
SELECT DISTINCT d.dept_name
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id;
-- Need DISTINCT to remove duplicates.

-- EXISTS approach: cleaner, no duplicates by design.

Performance: EXISTS short-circuits as soon as one match is found. For large tables, EXISTS is often faster than JOIN + DISTINCT because it stops after finding the first match.

---

Advanced Joins

Q15. What is a SELF JOIN? Give a practical example. Medium

A self join joins a table with itself using two different aliases. Used when a table has a hierarchical or recursive relationship.

Classic example: Employee-Manager hierarchy

-- employees table: emp_id, name, manager_id (FK -> emp_id of manager)
SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- LEFT JOIN to include employees with no manager (like the CEO)

-- Result:
-- employee_name | manager_name
-- Alice         | David (Alice's manager is David)
-- Bob           | Alice (Bob's manager is Alice)
-- David         | NULL  (David is CEO, no manager)

Other self join uses:

-- Find pairs of employees in the same department:
SELECT a.name, b.name, a.dept_id
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;
-- a.emp_id < b.emp_id prevents (Alice,Bob) and (Bob,Alice) from both appearing

---

Q16. How do you find duplicate rows using a self join? Medium

-- Find employees with the same name (potential duplicates):
SELECT a.emp_id, a.name, b.emp_id
FROM employees a
JOIN employees b ON a.name = b.name AND a.emp_id < b.emp_id;
-- a.emp_id < b.emp_id: only pairs where a comes first (avoid symmetric duplicates)

-- Find the duplicate entries themselves:
SELECT emp_id, name, COUNT(*) AS occurrences
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
-- More efficient for just finding names that appear more than once.

---

Q17. Write a query to find the second highest salary using joins. Medium

Method 1: Self join

SELECT MAX(e1.salary) AS second_highest
FROM employees e1
WHERE e1.salary < (SELECT MAX(salary) FROM employees);

-- Or with self join:
SELECT MAX(e1.salary) AS second_highest
FROM employees e1
WHERE e1.salary NOT IN (
    SELECT MAX(salary) FROM employees
);

Method 2: Window function (cleaner, modern SQL):

SELECT salary AS second_highest
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

Method 3: Nth highest (parameterized with LIMIT/OFFSET):

-- Nth highest salary:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET (N-1);
-- OFFSET N-1 skips N-1 highest values

---

Q18. What is an equijoin vs non-equijoin? Show a non-equijoin example. Medium

Covered in Q6. A practical non-equijoin example: finding overlapping date ranges.

-- Find booking conflicts (same resource, overlapping dates):
CREATE TABLE bookings (
    booking_id INT,
    resource_id INT,
    start_date DATE,
    end_date DATE
);

SELECT a.booking_id, b.booking_id, a.resource_id
FROM bookings a
JOIN bookings b ON
    a.resource_id = b.resource_id         -- Same resource
    AND a.booking_id < b.booking_id       -- Avoid self-pairs
    AND a.start_date <= b.end_date        -- Non-equi condition
    AND b.start_date <= a.end_date;       -- Non-equi condition (overlap check)

Date range overlap condition: two intervals [a.start, a.end] and [b.start, b.end] overlap if: a.start <= b.end AND b.start <= a.end

---

Q19. Explain LATERAL JOIN (or CROSS APPLY). Hard

A LATERAL JOIN (SQL standard, PostgreSQL) or CROSS APPLY (SQL Server) allows each row from the left table to be used in a subquery on the right side. The right side can reference columns from the left side.

Problem without LATERAL: Subqueries cannot reference outer table columns in the FROM clause.

-- LATERAL JOIN: for each employee, find their 3 most recent projects
SELECT e.name, p.project_name, p.start_date
FROM employees e
CROSS JOIN LATERAL (
    SELECT project_name, start_date
    FROM project_assignments pa
    WHERE pa.emp_id = e.emp_id          -- References e.emp_id from outer query
    ORDER BY start_date DESC
    LIMIT 3
) p;

Without LATERAL, there is no way to reference e.emp_id in the FROM subquery.

Use cases:

• Top-N per group (most recent N records per user).
• Expanding JSON arrays (PostgreSQL json_array_elements in LATERAL).
• Calling table-valued functions per row.

---

Q20. What is a JOIN using USING clause vs ON clause? Easy

Both specify join conditions, but with different syntax:

-- ON clause: explicit column comparison (columns may have different names)
SELECT * FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- USING clause: when join column has same name in both tables
SELECT * FROM employees
JOIN departments USING (dept_id);
-- dept_id appears only once in the result (not twice)

Key difference:

• ON: Both columns appear in result (e.dept_id AND d.dept_id, usually the same value).
• USING: The joined column appears only once in the result.

USING limitation: Only works when the column has the same name in both tables.

---

Q21. Write complex join queries for common interview scenarios. Hard

Scenario 1: Find employees who earn more than the average salary of their department.

SELECT e.name, e.salary, d.dept_name, dept_avg.avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary
ORDER BY d.dept_name, e.salary DESC;

Scenario 2: Find departments where all employees earn more than 50,000.

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.dept_id = d.dept_id AND e.salary <= 50000
);
-- Only departments where NO employee earns <= 50000 (i.e., all earn > 50000)

Scenario 3: Find employees who have never been assigned to any project.

SELECT e.name
FROM employees e
LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
WHERE pa.emp_id IS NULL;

Scenario 4: Find the department with the highest average salary.

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY avg_salary DESC
LIMIT 1;

---

Performance and Optimization

Q22. Why can LEFT JOIN be slower than INNER JOIN? Medium

LEFT JOIN must retain ALL rows from the left table, even those with no match. This means:

1. Cannot use hash join efficiently for outer rows: After the hash join, non-matching left rows must still be emitted with NULLs.
2. Cannot filter early: INNER JOIN can filter non-matching rows immediately. LEFT JOIN must carry all left rows through.
3. More result rows: LEFT JOIN produces at least as many rows as INNER JOIN (often more), which means more data to process downstream.

When LEFT JOIN is not slower:

• If the additional rows (nulls) are filtered by a WHERE clause that happens to eliminate them (essentially making it an INNER JOIN, which the optimizer may recognize).
• If the left table is small.

Optimizer equivalence: A LEFT JOIN B ON condition WHERE B.col IS NOT NULL is sometimes recognized by the optimizer as equivalent to an INNER JOIN and optimized accordingly.

---

Q23. What is a cartesian join and when is it a performance problem? Easy

A Cartesian join (same as CROSS JOIN) occurs when two tables are joined without a join condition, producing every combination.

Accidental Cartesian join (a common SQL mistake):

-- WRONG: Missing join condition
SELECT e.name, d.dept_name
FROM employees e, departments d;  -- Old SQL-89 comma-syntax with no WHERE
-- Result: 3 employees * 3 departments = 9 rows (every combination!)

-- Correct:
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;  -- Must specify join condition

Performance impact:

Table A: 10,000 rows
Table B: 10,000 rows
Accidental Cartesian: 100,000,000 rows

If both tables are 1GB each:
Cartesian result: ~100TB (impossible to process)

Prevention: Use modern explicit JOIN syntax (JOIN ... ON ...) instead of comma-separated FROM clause. Missing ON clause is a syntax error in modern JOIN syntax.

---

Q24. How do you optimize a query with multiple joins? Hard

1. Use indexes on join columns:

-- If this query is slow:
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

-- Ensure:
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- c.customer_id should already be PK (indexed).

2. Filter early with WHERE:

-- Push filters before the join (optimizer usually does this automatically):
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'Mumbai';  -- Filter reduces departments before join

3. Use EXPLAIN to check join order and algorithm:

EXPLAIN SELECT ...;
-- Look for nested loops on large tables (bad), hash joins (usually good).

*4. Avoid SELECT :

-- Bad: fetches all columns from all joined tables
SELECT * FROM a JOIN b ON ...;

-- Good: fetch only needed columns
SELECT a.name, b.status FROM a JOIN b ON ...;
-- Reduces data transferred and processed.

5. Consider covering indexes: If the query only uses columns in the index, the join can be satisfied without touching the actual table rows.

6. Check for implicit type conversions:

-- Bad: type mismatch causes index not used
-- orders.customer_id is INT, customers.id is VARCHAR
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- Fix: ensure matching types, or use CAST

---

Q25. What is the difference between JOIN and subquery? When to prefer each? Medium

Aspect	JOIN	Subquery
Readability	Clear for combining data from multiple tables	Clear for filtering based on another table's data
Performance	Often faster (optimizer handles both sides together)	May be slower (correlated subquery = once per row)
Duplicate handling	May produce duplicate rows (use DISTINCT)	EXISTS subquery avoids duplicates
Use case	When you need columns from multiple tables	When you only need existence check or aggregate comparison

Correlated subquery (slow):

-- For each employee, find if they have any projects (runs once per employee row!)
SELECT e.name
FROM employees e
WHERE (SELECT COUNT(*) FROM projects p WHERE p.emp_id = e.emp_id) > 0;
-- O(n * m) complexity

Equivalent JOIN (faster):

SELECT DISTINCT e.name
FROM employees e
JOIN projects p ON e.emp_id = p.emp_id;
-- O(n + m) with hash join

When subquery is better:

-- Find employees earning above average (scalar subquery is clear and efficient):
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
-- AVG subquery executed once, not per row.

---

Q26. How do you handle NULL values in join conditions? Medium

NULL does not equal NULL in SQL. A JOIN condition ON a.col = b.col will NEVER match rows where either side is NULL.

employees: emp_id=3, name=Carol, dept_id=NULL
departments: dept_id=NULL, dept_name='Unknown'  -- hypothetical

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Carol does NOT appear because NULL = NULL evaluates to UNKNOWN (not TRUE).

Handle NULLs in join:

-- Join including NULL matches:
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id OR (e.dept_id IS NULL AND d.dept_id IS NULL);
-- Or using COALESCE:
ON COALESCE(e.dept_id, -1) = COALESCE(d.dept_id, -1)
-- Map NULLs to -1 for matching (use a value that cannot be a real dept_id)

NULL in NOT IN:

-- DANGER: NOT IN with NULL in subquery returns EMPTY result
SELECT name FROM employees WHERE dept_id NOT IN (10, 20, NULL);
-- Returns NO ROWS. Because: NOT (dept_id IN (10, 20, NULL))
--   = NOT (dept_id=10 OR dept_id=20 OR dept_id=NULL)
--   = dept_id!=10 AND dept_id!=20 AND dept_id!=NULL
--   dept_id != NULL is always UNKNOWN. Final result: UNKNOWN = not returned.

-- Safe alternative: use NOT EXISTS or filter NULLs
SELECT name FROM employees WHERE dept_id NOT IN (
    SELECT dept_id FROM departments WHERE dept_id IS NOT NULL
);

---

FAQ

Q: What is the difference between UNION and JOIN? JOIN combines columns from two tables (horizontal combination). UNION combines rows from two queries (vertical combination). JOIN requires a condition; UNION requires matching column count and compatible types.

Q: Can you join a table more than once in the same query? Yes. Use different aliases each time. Common when the same lookup table (like employees for the manager) is needed multiple times in the same query.

Q: What is the difference between UNION and UNION ALL? UNION removes duplicates (adds sorting overhead). UNION ALL keeps all rows including duplicates (faster). Use UNION ALL when you know results do not overlap or when you want to see duplicates.

---

Related PapersAdda guides:

• DBMS Interview Questions 2026
• DBMS Normalization Questions 2026
• DBMS ACID Transactions Questions 2026
• DBMS Indexing Questions 2026
• DBMS vs RDBMS Questions 2026