- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- Get all combinations of employees and departments
SELECT e.emp_name, d.dept_name
FROM Employee e
CROSS JOIN Department d;
- 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;
- Find common records in two tables
SELECT id FROM TableA
INTERSECT
SELECT id FROM TableB;
- Find records in A not in B
SELECT id FROM TableA
EXCEPT
SELECT id FROM TableB;
- Find duplicate customer orders using join
SELECT customer_id, order_date, COUNT(*)
FROM Orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;
