SQL Interview Cheat Sheet - SQL JOINS CHEAT SHEET

Sample Tables

Employee

EmpID	Name	DeptID
1	Anita	10
2	Ravi	20
3	John	NULL

Department

DeptID	DeptName
10	HR
20	IT
30	Finance

---

INNER JOIN

Returns only matching records.

SELECT e.Name,
       d.DeptName
FROM Employee e
INNER JOIN Department d
ON e.DeptID = d.DeptID;

Result:

Name	DeptName
Anita	HR
Ravi	IT

---

LEFT JOIN

Returns all rows from left table.

SELECT e.Name,
       d.DeptName
FROM Employee e
LEFT JOIN Department d
ON e.DeptID = d.DeptID;

Result:

Name	DeptName
Anita	HR
Ravi	IT
John	NULL

Interview Question:
Find employees without departments.

SELECT e.*
FROM Employee e
LEFT JOIN Department d
ON e.DeptID=d.DeptID
WHERE d.DeptID IS NULL;

---

RIGHT JOIN

Returns all rows from right table.

SELECT e.Name,
       d.DeptName
FROM Employee e
RIGHT JOIN Department d
ON e.DeptID=d.DeptID;

---

FULL OUTER JOIN

Returns matching and non-matching rows.

SELECT *
FROM Employee e
FULL OUTER JOIN Department d
ON e.DeptID=d.DeptID;

---

SELF JOIN

Employee and Manager in same table.

EmpID	Name	ManagerID
1	Anita	NULL
2	Ravi	1
3	John	1

SELECT e.Name Employee,
       m.Name Manager
FROM Employee e
LEFT JOIN Employee m
ON e.ManagerID=m.EmpID;

---

CROSS JOIN

Every row joins every row.

SELECT *
FROM Employee
CROSS JOIN Department;

Rows Returned:

Employee Rows × Department Rows

3 × 3 = 9 rows

Top SQL Queries for Practice (With Short Answers) - Join Queries (31–45)

 

31. Get employee with department name

    SELECT e.emp_name, d.dept_name
    FROM Employee e
    JOIN Department d ON e.dept_id = d.dept_id;

32. Get all employees even without department

    SELECT e.emp_name, d.dept_name
    FROM Employee e
    LEFT JOIN Department d ON e.dept_id = d.dept_id;

33. Get all departments even without employees

    SELECT e.emp_name, d.dept_name
    FROM Employee e
    RIGHT JOIN Department d ON e.dept_id = d.dept_id;

34. Get matching + non-matching rows from both tables

    SELECT e.emp_name, d.dept_name
    FROM Employee e
    FULL OUTER JOIN Department d ON e.dept_id = d.dept_id;

35. Get employee and manager name

    SELECT e.emp_name AS Employee, m.emp_name AS Manager
    FROM Employee e
    LEFT JOIN Employee m ON e.manager_id = m.emp_id;

36. Find employees without department

    SELECT e.*
    FROM Employee e
    LEFT JOIN Department d ON e.dept_id = d.dept_id
    WHERE d.dept_id IS NULL;

37. Find departments without employees

    SELECT d.*
    FROM Department d
    LEFT JOIN Employee e ON e.dept_id = d.dept_id
    WHERE e.emp_id IS NULL;

38. Find customers with no orders

    SELECT c.*
    FROM Customers c
    LEFT JOIN Orders o ON c.customer_id = o.customer_id
    WHERE o.order_id IS NULL;

39. Find orders with customer names

    SELECT o.order_id, c.customer_name
    FROM Orders o
    JOIN Customers c ON o.customer_id = c.customer_id;

40. Find products never sold

    SELECT p.*
    FROM Products p
    LEFT JOIN Order_Details od ON p.product_id = od.product_id
    WHERE od.product_id IS NULL;

41. Get all combinations of employees and departments

    SELECT e.emp_name, d.dept_name
    FROM Employee e
    CROSS JOIN Department d;

42. Find employees earning more than manager

    SELECT e.emp_name
    FROM Employee e
    JOIN Employee m ON e.manager_id = m.emp_id
    WHERE e.salary > m.salary;

43. Find common records in two tables

    SELECT id FROM TableA
    INTERSECT
    SELECT id FROM TableB;

44. Find records in A not in B

    SELECT id FROM TableA
    EXCEPT
    SELECT id FROM TableB;

45. Find duplicate customer orders using join

    SELECT customer_id, order_date, COUNT(*)
    FROM Orders
    GROUP BY customer_id, order_date
    HAVING COUNT(*) > 1;