CodeToLive

Table Joins in SQL

Joins are used to combine rows from two or more tables based on related columns. They are fundamental for working with relational databases where data is normalized across multiple tables.

Types of Joins

Sample Tables for Examples


-- Employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    manager_id INT
);

-- Departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    location_id INT
);

-- Sample data
INSERT INTO departments VALUES 
(10, 'Accounting', 100),
(20, 'Marketing', 200),
(30, 'IT', 300),
(40, 'HR', 400);

INSERT INTO employees VALUES 
(101, 'John', 'Doe', 10, 103),
(102, 'Jane', 'Smith', 20, 103),
(103, 'Robert', 'Johnson', 10, NULL),
(104, 'Emily', 'Davis', 30, 103),
(105, 'Michael', 'Brown', NULL, 103);

INNER JOIN

Returns only rows where there's a match in both tables. 


-- Employees with their department names
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN

Returns all rows from the left table (employees) plus matches from the right table. 


-- All employees with department names (including those without departments)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

RIGHT JOIN

Returns all rows from the right table (departments) plus matches from the left table. 


-- All departments with employees (including departments without employees)
SELECT d.department_name, e.first_name, e.last_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

FULL OUTER JOIN

Returns all rows when there's a match in either table. 


-- All employees and all departments
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

Note: MySQL doesn't support FULL OUTER JOIN directly. You can simulate it with UNION of LEFT and RIGHT joins.

CROSS JOIN

Returns the Cartesian product of both tables (all possible combinations). 


-- All possible employee-department combinations
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;

SELF JOIN

Joins a table to itself, useful for hierarchical data. 


-- Employees with their managers
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Joining Multiple Tables


-- Employees with departments and locations
SELECT e.first_name, e.last_name, 
       d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

Using Table Aliases

Aliases make queries more readable, especially with joins. 


-- Using aliases for clarity
SELECT emp.first_name, emp.last_name, 
       dept.department_name
FROM employees emp
JOIN departments dept ON emp.department_id = dept.department_id;

NATURAL JOIN

Automatically joins tables on columns with the same name (use with caution). 


-- Natural join on department_id
SELECT first_name, last_name, department_name
FROM employees
NATURAL JOIN departments;

USING Clause

Alternative syntax when join columns have the same name. 


-- Join using department_id
SELECT first_name, last_name, department_name
FROM employees
JOIN departments USING (department_id);

Non-Equi Joins

Joins based on conditions other than equality. 


-- Employees and salary grades
SELECT e.first_name, e.last_name, e.salary, g.grade
FROM employees e
JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary;

Join Performance Considerations

Back to Tutorials