JOIN Operations

JOINs are used to retrieve data from multiple tables in a single query. They combine rows from different tables based on a related column between them.

Common scenarios for using JOINs:

• Getting employee information along with their department names
• Retrieving order details with customer information
• Combining product data with category information

INNER JOIN selects records that have matching values in both tables. If there's no match, the records are not included in the result.

-- Get employees with their department names
SELECT e.name, e.salary, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department = d.name;

This query returns only employees who have a matching department in the departments table.

LEFT JOIN returns all records from the left table (first table), and the matched records from the right table (second table). If there's no match, the result contains NULL for columns from the right table.

-- Get all employees, including those without departments
SELECT e.name, e.salary, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department = d.name;

This query returns all employees, even if they don't have a matching department.

RIGHT JOIN returns all records from the right table (second table), and the matched records from the left table (first table). If there's no match, the result contains NULL for columns from the left table.

-- Get all departments, including those without employees
SELECT e.name, e.salary, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.name;

This query returns all departments, even if they don't have any employees.

FULL OUTER JOIN returns all records from both tables. If there's no match, the result contains NULL for columns from the table without a match.

-- Get all employees and departments, regardless of matches
SELECT e.name, e.salary, d.name AS department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department = d.name;

This query returns all employees and all departments, with NULLs where there are no matches.

A `CROSS JOIN` creates a combination of every row from the first table with every row from the second table. It's used to generate all possible pairings.

-- Generate all possible pairings of employees and departments
SELECT e.name as employee_name, d.name as department_name
FROM employees e
CROSS JOIN departments d;

A self JOIN is a regular join, but the table is joined with itself. This is useful for comparing rows within the same table.

-- Find employees who earn more than their manager
SELECT e1.name AS employee, e1.salary, 
       e2.name AS manager, e2.salary AS manager_salary
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id
WHERE e1.salary > e2.salary;