Joins in SQL

In relational databases, data is usually stored across multiple tables to avoid duplication and improve organization. Joins are used to combine rows from two or more tables based on a related column between them. Without joins, it would be impossible to generate meaningful reports that require data from different tables, such as employee details along with department locations or project assignments.

---

Sample Tables Used for Join Examples

Before understanding joins, it is important to understand the structure of the tables involved.

Departments Table

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

This table stores department-related information such as department name and location.

---

Projects Table

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    employee_id INT,
    budget DECIMAL(10,2)
);

This table stores project details and links projects to employees using employee_id.

---

INNER JOIN

The INNER JOIN returns only those rows where a matching value exists in both tables.

SELECT 
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM employees e
INNER JOIN departments d 
    ON e.department = d.department_name;

Explanation

• SQL compares the department column in employees with department_name in departments.
• Only employees whose department exists in the departments table are returned.
• Employees without a valid department mapping are excluded.

Use Case

• Employee lists with valid department details
• Data integrity checks

---

LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all rows from the left table and only matching rows from the right table.

SELECT 
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM employees e
LEFT JOIN departments d 
    ON e.department = d.department_name;

Explanation

• Every employee appears in the result.
• If a department match exists, department details are shown.
• If no match exists, department columns contain NULL.

Use Case

• Finding employees without assigned departments
• Complete employee reports

---

RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT 
    d.department_name,
    d.location,
    e.first_name,
    e.last_name
FROM employees e
RIGHT JOIN departments d 
    ON e.department = d.department_name;

Explanation

• All departments are shown, even if they have no employees.
• Employee columns show NULL if no match exists.

Use Case

• Finding departments without employees
• Organizational structure analysis

---

FULL OUTER JOIN

The FULL OUTER JOIN returns all rows from both tables, with NULL values where no match exists.

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    d.location
FROM employees e
FULL OUTER JOIN departments d 
    ON e.department = d.department_name;

Explanation

• Combines LEFT JOIN and RIGHT JOIN results
• Shows unmatched rows from both tables

Important Note

• Not supported in MySQL
• Alternative in MySQL:

SELECT ...
FROM employees e
LEFT JOIN departments d ON ...
UNION
SELECT ...
FROM employees e
RIGHT JOIN departments d ON ...

---

CROSS JOIN

The CROSS JOIN creates a Cartesian product, meaning every row from the first table is combined with every row from the second table.

SELECT 
    e.first_name,
    e.last_name,
    p.project_name
FROM employees e
CROSS JOIN projects p;

Explanation

• No ON condition is required
• If employees has 10 rows and projects has 5 rows → result = 50 rows

Use Case

• Generating combinations
• Test data generation
• Matrix-style reports

---

SELF JOIN

A SELF JOIN is a join where a table is joined with itself.

SELECT 
    e1.first_name AS employee1,
    e2.first_name AS employee2,
    e1.department
FROM employees e1
INNER JOIN employees e2 
    ON e1.department = e2.department
    AND e1.employee_id != e2.employee_id;

Explanation

• Same table is referenced using different aliases
• Used to find relationships within the same table
• Excludes self-matching rows

Use Case

• Employees in same department
• Manager–employee relationships
• Comparing rows within a table

---

Multiple Joins (Joining More Than Two Tables)

SQL allows chaining multiple joins in a single query.

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    d.location,
    p.project_name,
    p.budget
FROM employees e
INNER JOIN departments d 
    ON e.department = d.department_name
LEFT JOIN projects p 
    ON e.employee_id = p.employee_id;

Explanation

• First joins employees with departments
• Then joins project data using employee_id
• LEFT JOIN ensures employees without projects are still included

---

Join Execution Order (Conceptual)

1. FROM clause
2. JOIN conditions
3. WHERE clause
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT

---

INNER vs OUTER JOIN Summary

Join Type	What It Returns
INNER JOIN	Matching rows in both tables
LEFT JOIN	All left + matching right
RIGHT JOIN	All right + matching left
FULL JOIN	All rows from both tables
CROSS JOIN	Every possible combination
SELF JOIN	Table joined with itself

---

Common Beginner Mistakes

• Forgetting ON condition
• Using wrong join type
• Confusing LEFT and RIGHT joins
• Creating unintended Cartesian products
• Joining on incorrect columns