Joining Tables

What Are Joins?

Joins in PostgreSQL allow you to combine data from multiple tables based on matching values in specific columns. They provide a way to query data across related tables. To explain the join concepts, let us take the example of employees table and departments. We will use these two tables to explain different Join types.

Tables: employees and departments

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT
);

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

INSERT INTO employees (employee_id, employee_name, department_id)
VALUES (1, 'Alice', 101), (2, 'Bob', 102), (3, 'Carol', NULL);

INSERT INTO departments (department_id, department_name)
VALUES (101, 'HR'), (102, 'Engineering'), (103, 'Finance');

Common Types of Joins:

1. Inner Join:

   

   • Returns records with matching values in both tables.

   • Example: Combining employees and departments tables based on matching department IDs.

   • SQL:

     SELECT e.employee_id, e.employee_name, d.department_name
     FROM employees e
     INNER JOIN departments d ON e.department_id = d.department_id;
     
     

   • Output:

     employee_id | employee_name | department_name
     ------------+---------------+-----------------
     1           | Alice         | HR
     2           | Bob           | Engineering
     
     

2. Left Join (Left Outer Join):

   

   • Includes all records from the left table and matched records from the right table.

   • If no match exists, NULL values are returned for right table columns.

   • Example: Retrieving all employees along with their department names (even if they don’t belong to any department).

   • SQL:

     SELECT e.employee_id, e.employee_name, d.department_name
     FROM employees e
     LEFT JOIN departments d ON e.department_id = d.department_id;
     
     

   • Output:

     employee_id | employee_name | department_name
     ------------+---------------+-----------------
     1           | Alice         | HR
     2           | Bob           | Engineering
     3           | Carol         | NULL
     
     

3. Right Join (Right Outer Join):

   

   • Similar to left join but prioritizes the right table.

   • Example: Retrieving all departments along with their employees (even if there are no employees in a department).

   • SQL:

     SELECT e.employee_id, e.employee_name, d.department_name
     FROM employees e
     RIGHT JOIN departments d ON e.department_id = d.department_id;
     
     

   • Output:

     employee_id | employee_name | department_name
     ------------+---------------+-----------------
     1           | Alice         | HR
     2           | Bob           | Engineering
     NULL        | NULL          | Finance
     
     

4. Full Outer Join:

   

   • Combines results from both left and right outer joins.

   • Includes unmatched records from both tables.

   • Example: Getting all employees and their department names.

   • SQL:

     SELECT e.employee_id, e.employee_name, d.department_name
     FROM employees e
     FULL OUTER JOIN departments d ON e.department_id = d.department_id;
     
     

   • Output:

     employee_id | employee_name | department_name
     ------------+---------------+-----------------
     1           | Alice         | HR
     2           | Bob           | Engineering
     3           | Carol         | NULL
     NULL        | NULL          | Finance
     
     

Performance Considerations:

• Use smaller data types to reduce memory and disk space usage.
• Tune PostgreSQL settings (e.g., shared_buffers, work_mem) for caching and sorting.
• Consider using faster storage (e.g., SSDs) for disk-intensive joins.