Back to SQL Practice: 331 Real Interview Queries
Q3Question 3 of 9

JOINs: INNER, LEFT, RIGHT, FULL, CROSS, SELF

Answer: INNER JOIN returns matching rows only, LEFT JOIN keeps all left rows with NULLs for non-matches, SELF JOIN joins a table to itself, and LEFT JOIN + IS NULL finds unmatched rows.

Key Concepts
  • INNER JOIN returns only rows that have matching values in both tables. If no match, the row is excluded entirely.
  • LEFT JOIN returns all rows from the left table. If no match in the right table, right columns are filled with NULL.
  • RIGHT JOIN is the mirror of LEFT JOIN. Returns all right rows with NULLs for unmatched left columns. Most developers use LEFT JOIN exclusively.
  • FULL OUTER JOIN returns all rows from both tables. Unmatched rows on either side get NULLs for the missing table's columns.
  • CROSS JOIN returns the Cartesian product: every row from table A paired with every row from table B. Row count = A rows * B rows.
  • SELF JOIN joins a table to itself using aliases. Common for manager-employee hierarchies: FROM employees e JOIN employees m ON e.manager_id = m.id.
  • LEFT JOIN + WHERE right.id IS NULL finds rows in the left table that have NO match in the right table (anti-join pattern).

Definition

INNER JOIN returns matching rows only, LEFT JOIN keeps all left rows with NULLs for non-matches, SELF JOIN joins a table to itself, and LEFT JOIN + IS NULL finds unmatched rows.
  • 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):

idnamesalarydepartment_idmanager_id
1Rajesh90000101NULL
2Priya750001011
3Amit950001021
4Sneha600001023
5Vikram900001031
6Meena75000NULL5

departments:

department_iddepartment_name
101Retail Banking
102Corporate
103Treasury
104IT (no employees)

projects:

project_idproject_namedepartment_idstart_date
P01Core Banking1012025-01-01
P02Risk Engine1022025-03-01
P03Mobile App1032025-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):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE join_date >= CURRENT_DATE - 
INTERVAL '6 months';

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table departments.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table bookings.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table hire_date.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table departments.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table products.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table shifts.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table products.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table project_assignments.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table customers.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table departments.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table categories.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table project_assignments.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table products.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table products.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Are there duplicates I need to remove? Use DISTINCT.
  4. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table orders.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Are there duplicates I need to remove? Use DISTINCT.
  4. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table orders.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  5. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
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:

SQL
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:

  1. FROM: Reads table products.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  3. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table departments.
  2. LEFT JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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.

---

Share
Use ← → arrow keys to navigate