Definition
- INNER JOIN: Returns only matching rows from both tables.
- LEFT JOIN: Returns all left rows + matching right rows. Right columns = NULL when no match.
- RIGHT JOIN: Mirror of LEFT JOIN. All right rows + matching left rows.
- FULL OUTER JOIN: All rows from both tables. NULLs for unmatched sides.
- CROSS JOIN: Cartesian product. Every combination of rows from both tables.
- SELF JOIN: Same table joined to itself using aliases (e.g., employee-manager).
- Anti-join: LEFT JOIN + WHERE right.id IS NULL finds rows with no match.
This section is a focused tutorial on JOINs. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.
Understanding the Concepts
## JOINs: INNER, LEFT, RIGHT, FULL, CROSS, SELF
JOIN combines rows from two or more tables. INNER JOIN returns only matching rows. LEFT JOIN returns all left rows plus matches (NULLs for non-matches). RIGHT JOIN is the reverse. FULL JOIN returns all rows from both. CROSS JOIN returns every combination. SELF JOIN joins a table to itself (useful for manager-employee relationships).
Sample Tables
employees and departments tables (shared across all queries in this section):
| id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Rajesh | 90000 | 101 | NULL |
| 2 | Priya | 75000 | 101 | 1 |
| 3 | Amit | 95000 | 102 | 1 |
| 4 | Sneha | 60000 | 102 | 3 |
| 5 | Vikram | 90000 | 103 | 1 |
| 6 | Meena | 75000 | NULL | 5 |
departments:
| department_id | department_name |
|---|---|
| 101 | Retail Banking |
| 102 | Corporate |
| 103 | Treasury |
| 104 | IT (no employees) |
projects:
| project_id | project_name | department_id | start_date |
|---|---|---|---|
| P01 | Core Banking | 101 | 2025-01-01 |
| P02 | Risk Engine | 102 | 2025-03-01 |
| P03 | Mobile App | 103 | 2025-06-01 |
---
Q1. Find employees who joined in the last 6 months
Problem: Find employees who joined in the last 6 months.
How to think before writing:
1. Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM employees
WHERE join_date >= CURRENT_DATE -
INTERVAL '6 months';
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q2. Get departments with no employees
Problem: Get departments with no employees.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id =
e.department_id
WHERE e.id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
departments. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q3. Find customers who have not made any purchase
Problem: Find customers who have not made any purchase.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
WHERE s.sale_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q4. Identify overlapping date ranges for bookings
Problem: Identify overlapping date ranges for bookings.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT b1.booking_id, b2.booking_id
FROM bookings b1
JOIN bookings b2 ON b1.booking_id <> b2.booking_id
WHERE b1.start_date <= b2.end_date
AND b1.end_date >= b2.start_date;
Step-by-step how the database executes this:
- FROM: Reads table
bookings. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q5. Find employees who have the same salary as their manager
Problem: Find employees who have the same salary as their manager.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS Employee, e.salary, m.name AS
Manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary = m.salary;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q6. Write a query to perform a self-join to find pairs of employees in the same department
Problem: Write a query to perform a self-join to find pairs of employees in the same department.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
SQL Solution:
SELECT e1.name AS Employee1, e2.name AS
Employee2, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id =
e2.department_id AND e1.id < e2.id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q7. Find employees with no salary changes in the last 2 years
Problem: Find employees with no salary changes in the last 2 years.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN salary_history sh ON e.id =
sh.employee_id AND sh.change_date >=
CURRENT_DATE - INTERVAL '2 years'
WHERE sh.employee_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q8. Write a query to create a running count of how many employees joined in each year
Problem: Write a query to create a running count of how many employees joined in each year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT join_year, COUNT(*) AS yearly_hires,
SUM(COUNT(*)) OVER (ORDER BY join_year)
AS running_total_hires
FROM (
SELECT EXTRACT(YEAR FROM hire_date) AS
join_year
FROM employees
) sub
GROUP BY join_year
ORDER BY join_year;
Step-by-step how the database executes this:
- FROM: Reads table
hire_date. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q9. Find employees who have never made a sale
Problem: Find employees who have never made a sale.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
LEFT JOIN sales s ON e.id = s.employee_id
WHERE s.sale_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q10. List all departments and their employee counts, including departments with zero employees
Problem: List all departments and their employee counts, including departments with zero employees.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT d.department_id, d.department_name,
COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id =
e.department_id
GROUP BY d.department_id, d.department_name;
Step-by-step how the database executes this:
- FROM: Reads table
departments. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q11. Find products that have never been sold
Problem: Find products that have never been sold.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.sale_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
products. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q12. Find employees with no corresponding entries in the salary_history table
Problem: Find employees with no corresponding entries in the salary_history table.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN salary_history sh ON e.id =
sh.employee_id
WHERE sh.employee_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q13. Write a query to find the first purchase date and last purchase date for each customer, including customers who never purchased anything
Problem: Write a query to find the first purchase date and last purchase date for each customer, including customers who never purchased anything.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT c.customer_id,
MIN(s.purchase_date) AS first_purchase,
MAX(s.purchase_date) AS last_purchase
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q14. Write a query to identify overlapping shifts for employees
Problem: Write a query to identify overlapping shifts for employees.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT s1.employee_id, s1.shift_id AS shift1,
s2.shift_id AS shift2
FROM shifts s1
JOIN shifts s2 ON s1.employee_id = s2.employee_id
AND s1.shift_id <> s2.shift_id
WHERE s1.start_time < s2.end_time AND s1.end_time
> s2.start_time;
Step-by-step how the database executes this:
- FROM: Reads table
shifts. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q15. Write a query to find the employee(s) who have never received a promotion
Problem: Write a query to find the employee(s) who have never received a promotion.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN promotions p ON e.id = p.employee_id
WHERE p.employee_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q16. Find products that have never been ordered
Problem: Find products that have never been ordered.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.order_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
products. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q17. Write a query to find employees who have the same hire date as their managers
Problem: Write a query to find employees who have the same hire date as their managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS employee_name, m.name AS
manager_name, e.hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date = m.hire_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q18. Identify employees who had overlapping project assignments
Problem: Identify employees who had overlapping project assignments.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT p1.employee_id, p1.project_id AS project1,
p2.project_id AS project2
FROM project_assignments p1
JOIN project_assignments p2 ON p1.employee_id =
p2.employee_id AND p1.project_id <> p2.project_id
WHERE p1.start_date < p2.end_date AND p1.end_date
> p2.start_date;
Step-by-step how the database executes this:
- FROM: Reads table
project_assignments. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q19. Find customers who haven’t ordered in the last 6 months
Problem: Find customers who haven’t ordered in the last 6 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition
SQL Solution:
SELECT customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id =
o.customer_id
GROUP BY c.customer_id
HAVING MAX(o.order_date) < CURRENT_DATE -
INTERVAL '6 months' OR MAX(o.order_date) IS
NULL;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q20. Find employees who have never taken any leave
Problem: Find employees who have never taken any leave.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN leaves l ON e.id = l.employee_id
WHERE l.leave_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q21. Find employees who joined in the same month and year
Problem: Find employees who joined in the same month and year.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
SQL Solution:
SELECT e1.id AS emp1_id, e2.id AS emp2_id,
e1.hire_date
FROM employees e1
JOIN employees e2 ON e1.id <> e2.id
AND EXTRACT(MONTH FROM e1.hire_date) =
EXTRACT(MONTH FROM e2.hire_date)
AND EXTRACT(YEAR FROM e1.hire_date) =
EXTRACT(YEAR FROM e2.hire_date);
🔶 Advanced-Level SQL Questions
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q22. Find employees who have not been assigned to any project
Problem: Find employees who have not been assigned to any project.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN project_assignments pa ON e.id =
pa.employee_id
WHERE pa.project_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q23. Find the total sales per customer including those
Problem: Find the total sales per customer including those.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
with zero sales.
SELECT c.customer_id, COALESCE(SUM(s.amount),
0) AS total_sales
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q24. Find customers with no orders in the last year
Problem: Find customers with no orders in the last year.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id =
o.customer_id AND o.order_date >=
CURRENT_DATE - INTERVAL '1 year'
WHERE o.order_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q25. Find employees who were hired before their managers
Problem: Find employees who were hired before their managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS employee, m.name AS manager,
e.hire_date, m.hire_date AS manager_hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date < m.hire_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q26. List all employees and their manager names
Problem: List all employees and their manager names.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
SQL Solution:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q27. Find employees with the same salary as their manager
Problem: Find employees with the same salary as their manager.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS employee, m.name AS manager,
e.salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary = m.salary;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q28. Get employees who joined after their department was created
Problem: Get employees who joined after their department was created.
How to think before writing:
1. Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
JOIN departments d ON e.department_id =
d.department_id
WHERE e.hire_date > d.creation_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q29. Find customers with no sales records
Problem: Find customers with no sales records.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT c.*
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
WHERE s.sale_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q30. Find employees who have never received a bonus
Problem: Find employees who have never received a bonus.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN bonuses b ON e.id = b.employee_id
WHERE b.bonus_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q31. Write a query to display employee names alongside their manager names, including those without managers
Problem: Write a query to display employee names alongside their manager names, including those without managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
SQL Solution:
SELECT e.name AS employee_name, m.name AS
manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q32. Find the employees who were hired on the same day as their managers
Problem: Find the employees who were hired on the same day as their managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS employee, m.name AS manager,
e.hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date = m.hire_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q33. Write a query to calculate the difference in salary between employees and their managers
Problem: Write a query to calculate the difference in salary between employees and their managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
SQL Solution:
SELECT e.name, m.name AS manager_name, e.salary,
m.salary AS manager_salary, e.salary - m.salary AS
salary_diff
FROM employees e
JOIN employees m ON e.manager_id = m.id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q34. List the departments with no employees
Problem: List the departments with no employees.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.department_id =
e.department_id
WHERE e.id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
departments. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q35. Find employees who joined before their department was created
Problem: Find employees who joined before their department was created.
How to think before writing:
1. Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
JOIN departments d ON e.department_id =
d.department_id
WHERE e.hire_date < d.creation_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q36. Find employees who do not have dependents
Problem: Find employees who do not have dependents.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN dependents d ON e.id = d.employee_id
WHERE d.dependent_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q37. Get the total sales amount for each product category including categories with zero sales
Problem: Get the total sales amount for each product category including categories with zero sales.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT c.category_id, COALESCE(SUM(s.amount),
0) AS total_sales
FROM categories c
LEFT JOIN products p ON c.category_id =
p.category_id
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY c.category_id;
Step-by-step how the database executes this:
- FROM: Reads table
categories. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q38. Find employees with overlapping project assignments
Problem: Find employees with overlapping project assignments.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT pa1.employee_id, pa1.project_id,
pa2.project_id AS overlapping_project
FROM project_assignments pa1
JOIN project_assignments pa2 ON pa1.employee_id =
pa2.employee_id AND pa1.project_id <>
pa2.project_id
WHERE pa1.start_date <= pa2.end_date AND
pa1.end_date >= pa2.start_date;
Step-by-step how the database executes this:
- FROM: Reads table
project_assignments. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q39. Find employees with no projects assigned in the last 6 months
Problem: Find employees with no projects assigned in the last 6 months.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN project_assignments pa ON e.id =
pa.employee_id AND pa.start_date >=
CURRENT_DATE - INTERVAL '6 months'
WHERE pa.project_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q40. Find employees who have worked on every project in their department
Problem: Find employees who have worked on every project in their department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
JOIN projects p ON e.department_id = p.department_id
LEFT JOIN project_assignments pa ON e.id =
pa.employee_id AND p.project_id = pa.project_id
GROUP BY e.id, e.name, p.department_id
HAVING COUNT(p.project_id) =
COUNT(pa.project_id);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q41. Find the total sales for each product including products with zero sales
Problem: Find the total sales for each product including products with zero sales.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT p.product_id, COALESCE(SUM(s.amount),
0) AS total_sales
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id;
Step-by-step how the database executes this:
- FROM: Reads table
products. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q42. Find employees who have not been assigned to any project in the last year
Problem: Find employees who have not been assigned to any project in the last year.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.*
FROM employees e
LEFT JOIN project_assignments pa ON e.id =
pa.employee_id AND pa.assignment_date >=
CURRENT_DATE - INTERVAL '1 year'
WHERE pa.project_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q43. Find employees hired after their managers
Problem: Find employees hired after their managers.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.name AS employee_name, m.name AS
manager_name, e.hire_date, m.hire_date AS
manager_hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date > m.hire_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q44. Find employees who have not submitted their timesheets in the last month
Problem: Find employees who have not submitted their timesheets in the last month.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
LEFT JOIN timesheets t ON e.id = t.employee_id AND
t.timesheet_date >= CURRENT_DATE - INTERVAL
'1 month'
WHERE t.timesheet_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q45. Find employees who have not taken any leave in the last 6 months
Problem: Find employees who have not taken any leave in the last 6 months.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
LEFT JOIN leaves l ON e.id = l.employee_id AND
l.leave_date >= CURRENT_DATE - INTERVAL '6
months'
WHERE l.leave_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q46. Find the employees who have never been late to work
Problem: Find the employees who have never been late to work.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
LEFT JOIN attendance a ON e.id = a.employee_id
AND a.arrival_time > a.scheduled_start_time
WHERE a.employee_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q47. Find products that have never been returned
Problem: Find products that have never been returned.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT p.product_id
FROM products p
LEFT JOIN returns r ON p.product_id = r.product_id
WHERE r.return_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
products. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q48. Find customers with orders but no shipments
Problem: Find customers with orders but no shipments.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Are there duplicates I need to remove? Use DISTINCT.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT o.customer_id
FROM orders o
LEFT JOIN shipments s ON o.order_id = s.order_id
WHERE s.shipment_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q49. Find employees who have never received a promotion
Problem: Find employees who have never received a promotion.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
LEFT JOIN promotions p ON e.id = p.employee_id
WHERE p.promotion_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q50. Find employees who report to a manager hired after them
Problem: Find employees who report to a manager hired after them.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name, m.id AS manager_id, m.name
AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.hire_date < m.hire_date;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q51. Find customers who made orders but never returned a product
Problem: Find customers who made orders but never returned a product.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Are there duplicates I need to remove? Use DISTINCT.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT o.customer_id
FROM orders o
LEFT JOIN returns r ON o.order_id = r.order_id
WHERE r.return_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q52. Find products that have been ordered but never reviewed
Problem: Find products that have been ordered but never reviewed.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT p.product_id
FROM products p
LEFT JOIN product_reviews pr ON p.product_id =
pr.product_id
JOIN sales s ON p.product_id = s.product_id
WHERE pr.review_id IS NULL
GROUP BY p.product_id;
Step-by-step how the database executes this:
- FROM: Reads table
products. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q53. Find employees whose hire date is the same weekday as their manager’s
Problem: Find employees whose hire date is the same weekday as their manager’s.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT e.id, e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE EXTRACT(DOW FROM e.hire_date) =
EXTRACT(DOW FROM m.hire_date);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q54. List departments with no open positions
Problem: List departments with no open positions.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT d.department_id
FROM departments d
LEFT JOIN job_openings j ON d.department_id =
j.department_id AND j.status = 'Open'
WHERE j.job_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
departments. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Key Takeaway
This section covered 55 interview questions on JOINs: INNER, LEFT, RIGHT, FULL, CROSS, SELF. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.
JOIN combines rows from two or more tables. INNER JOIN returns only matching rows. LEFT JOIN returns all left rows plus matches (NULLs for non-matches). RIGHT JOIN is the reverse. FULL JOIN returns all rows from both. CROSS JOIN returns every combination. SELF JOIN joins a table to itself (useful for manager-employee relationships).
Practice each query on the shared sample table. If you can solve all 55 without looking at the answer, you have mastered this concept.
---