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