Joining Tables (Relationships)

In a well-designed relational database, data is normalized, meaning it is spread across multiple tables to avoid redundancy. Joins are SQL commands that reconstruct this related data by temporarily combining columns from two or more tables based on their shared key columns.

1. The Need for Joins

Consider two tables:

1. Employees: Contains employee_id (Primary Key), first_name, and salary.
2. Departments: Contains department_id (Primary Key) and department_name.

To know the name of the department an employee belongs to, the Employees table would typically have a Foreign Key column called department_id. A join is necessary to link the department_id in the Employees table to the department_id in the Departments table.

2. The INNER JOIN

The INNER JOIN is the most common type of join. It returns only the rows that have matching values in both tables, based on the specified join condition. Rows from either table that do not have a match in the other table are excluded.

Syntax:

SELECT column_list
FROM TableA
INNER JOIN TableB
  ON TableA.matching_column = TableB.matching_column;

Tip: Always use Table.column notation when the column name exists in both tables to avoid ambiguity.

Execution Example: Listing Employees with Department Names

Let’s join the Employees table (aliased as E) and a hypothetical Departments table (aliased as D) on their common department_id key.

-- Retrieve employee name and the corresponding department name
SELECT
    E.first_name,
    E.last_name,
    D.department_name 
FROM Employees AS E                            -- Table E
INNER JOIN Departments AS D                    -- Join it with Table D
  ON E.department_id = D.department_id;        -- Where the Foreign Key matches the Primary Key

The result is a combined virtual table showing the names alongside their department names.

3. Joining Multiple Tables

You can chain multiple INNER JOIN clauses to combine data from three or more tables.

Execution Example: Employees, Departments, and Projects

Assuming we also have a Projects table and a ProjectAssignments table that links employees to projects:

SELECT
    E.first_name,
    D.department_name,
    P.project_title
FROM Employees AS E
-- 1. Link Employees to Departments
INNER JOIN Departments AS D ON E.department_id = D.department_id
-- 2. Link Employees to the Assignment Table
INNER JOIN ProjectAssignments AS A ON E.employee_id = A.employee_id
-- 3. Link the Assignment Table to the Project Table
INNER JOIN Projects AS P ON A.project_id = P.project_id
WHERE P.project_title = 'Q3 Marketing Campaign';

This single query efficiently pulls related data from four tables based on shared key columns.

4. Self-Join

A Self-Join is a regular join where a table is joined to itself. This is useful when data in one row relates to data in another row within the same table (e.g., finding all employees who report to the same manager, where the manager is also an employee).

To perform a self-join, you must use table aliases to treat the single table as two separate entities.

Execution Example: Employee and Manager Name

Assuming the Employees table has a manager_id column that is a Foreign Key referencing employee_id within the same table:

SELECT
    E.first_name AS EmployeeName,
    M.first_name AS ManagerName
FROM Employees AS E
INNER JOIN Employees AS M           -- Join the Employees table to itself
  ON E.manager_id = M.employee_id;  -- Where the Employee's manager_id matches the Manager's employee_id