PostgreSQL Joins

PostgreSQL - Modern Guide to JOINS

The JOIN clause is a fundamental part of SQL, used to combine rows from two or more tables based on a related column between them. Mastering JOINs is crucial for querying relational data effectively.

Setting Up the Sample Schema

Let’s use a modern, well-defined schema. We will have an employees table and a departments table, linked by a foreign key. This enforces data integrity.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2),
    department_id INT,
    CONSTRAINT fk_department
        FOREIGN KEY(department_id)
        REFERENCES departments(id)
);

-- Populate tables
INSERT INTO departments (id, name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'HR');

INSERT INTO employees (id, name, salary, department_id) VALUES
(1, 'Alice', 95000, 1),
(2, 'Bob', 80000, 2),
(3, 'Charlie', 110000, 1),
(4, 'Diana', 72000, NULL); -- Diana is not assigned to a department

The INNER JOIN

INNER JOIN returns only the rows where the join condition is met. It selects all rows that have matching values in both tables. This is the most common type of join.

Visual Representation (Venn Diagram):

      Table A         Table B
    (*********)     (*********)
   (***(XXXXX)*******)***)
  (****(XXXXX)*********)
 (*****(XXXXX)********)
  (****(XXXXX)*********)
   (***(XXXXX)*******)***)
    (*********)_..._/***/)
             ^--- INNER JOIN (Intersection)

SELECT e.name, d.name AS department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

Result (Note: Diana is excluded because her department_id is NULL):

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
(3 rows)

The LEFT JOIN (or LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table (employees), and the matched rows from the right table (departments). If there is no match, the columns from the right table will be NULL.

Visual Representation:

      Table A         Table B
    (XXXXXXXXX)     (*********)
   (XXXXXXXXX(*******)***)
  (XXXXXXXXX(*********)
 (XXXXXXXXX(********)
  (XXXXXXXXX(*********)
   (XXXXXXXXX(*******)***)
    (XXXXXXXXX)_____/***/)
      ^--- LEFT JOIN (All of A, plus matching B)

SELECT e.name, d.name AS department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

Result (Diana is now included):

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
 Diana   | NULL
(4 rows)

The RIGHT JOIN (or RIGHT OUTER JOIN)

RIGHT JOIN is the reverse of LEFT JOIN. It returns all rows from the right table (departments) and the matched rows from the left table (employees). If there is no match, the columns from the left table will be NULL.

SELECT e.name, d.name AS department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

Result (The ‘HR’ department is included, even with no employees):

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Charlie | Engineering
 Bob     | Sales
 NULL    | HR
(4 rows)

The FULL OUTER JOIN

FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all rows from both tables, with NULL values in columns where a match does not exist in the other table.

SELECT e.name, d.name AS department_name
FROM employees AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.id;

Result (Includes Diana from employees and ‘HR’ from departments):

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
 Diana   | NULL
 NULL    | HR
(5 rows)

Other Join Types

CROSS JOIN

CROSS JOIN creates a Cartesian product of the two tables—every row from the first table is combined with every row from the second table. Use with caution as it can produce very large result sets.

SELECT e.name, d.name FROM employees AS e CROSS JOIN departments AS d;

SELF JOIN

A SELF JOIN is a regular join, but the table is joined with itself. This is useful for querying hierarchical data or comparing rows within the same table.

-- Find employees who earn more than their colleague 'Bob'
SELECT e1.name, e1.salary
FROM employees AS e1, employees AS e2
WHERE e2.name = 'Bob' AND e1.salary > e2.salary;

Best Practices: Avoid NATURAL JOIN

Industry Best Practice: Avoid using NATURAL JOIN. It implicitly joins on all columns with the same name. This can lead to unexpected and incorrect results if schemas change, making queries fragile and hard to debug. Always be explicit with your join conditions.

A safer, more concise alternative when join columns have the same name is USING:

-- This is a bad idea:
-- SELECT * FROM employees NATURAL JOIN departments;

-- A better alternative to ON when column names are the same:
-- (Assuming the FK column was named 'id' instead of 'department_id')
-- SELECT e.name, d.name FROM employees e JOIN departments d USING(id);

Performance and Final Tips

• Use Aliases: Always use short, clear table aliases (e.g., e for employees) to improve readability.
• Index Foreign Keys: For good join performance, ensure that the columns used in ON clauses are indexed. Foreign key constraints often create these indexes automatically, but it’s crucial to verify.
• Analyze Queries: Use EXPLAIN ANALYZE to see how PostgreSQL is executing your join. This helps you identify performance bottlenecks and understand if indexes are being used correctly.