SQL_joins.sql

-- Create sample tables
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

-- Insert sample data
INSERT INTO employees VALUES
(1, 'Alice', 1, 50000, '2020-01-15'),
(2, 'Bob', 2, 45000, '2019-03-20'),
(3, 'Charlie', 1, 60000, '2018-06-10'),
(4, 'David', 3, 48000, '2021-01-01'),
(5, 'Eve', NULL, 55000, '2019-11-15');

INSERT INTO departments VALUES
(1, 'Engineering', 'NYC'),
(2, 'Sales', 'LA'),
(3, 'HR', 'NYC'),
(4, 'Finance', 'Chicago');

-- 1. INNER JOIN - Returns only matching records from both tables
SELECT 
    e.name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

/* Output:
Alice       50000   Engineering   NYC
Bob         45000   Sales         LA
Charlie     60000   Engineering   NYC
David       48000   HR            NYC
*/

-- 2. LEFT JOIN - Returns all records from left table, matching from right
SELECT 
    e.name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

/* Output:
Alice       50000   Engineering   NYC
Bob         45000   Sales         LA
Charlie     60000   Engineering   NYC
David       48000   HR            NYC
Eve         55000   NULL          NULL
*/

-- 3. RIGHT JOIN - Returns all records from right table, matching from left
SELECT 
    e.name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

/* Output:
Alice       50000   Engineering   NYC
Bob         45000   Sales         LA
Charlie     60000   Engineering   NYC
David       48000   HR            NYC
NULL        NULL    Finance       Chicago
*/

-- 4. FULL OUTER JOIN - Returns all records from both tables
SELECT 
    e.name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

/* Output:
Alice       50000   Engineering   NYC
Bob         45000   Sales         LA
Charlie     60000   Engineering   NYC
David       48000   HR            NYC
Eve         55000   NULL          NULL
NULL        NULL    Finance       Chicago
*/

-- 5. CROSS JOIN - Cartesian product (all combinations)
SELECT 
    e.name,
    d.department_name
FROM employees e
CROSS JOIN departments d
LIMIT 10;

/* Output: Each employee paired with each department (5 × 4 = 20 rows)
Alice   Engineering
Alice   Sales
Alice   HR
...
*/

-- 6. SELF JOIN - Join table with itself
CREATE TABLE employee_manager AS
SELECT 
    e1.name as employee_name,
    e2.name as manager_name,
    e1.salary as employee_salary,
    e2.salary as manager_salary
FROM employees e1
LEFT JOIN employees e2 ON e1.employee_id = e2.employee_id + 1;

-- 7. Multiple JOINs
SELECT 
    e.name,
    e.salary,
    d.department_name,
    d.location,
    COUNT(*) as team_size
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, d.department_id;

-- 8. JOIN with WHERE clause (filter after join)
SELECT 
    e.name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;

/* Output:
Charlie     Engineering   60000
Eve         NULL          55000
Alice       Engineering   50000
*/