PostgreSQL Joins & Relationships

A. Understanding Relationships

1. Relationship Types

text

1. One-to-One (1:1)
   - One user ↔ One profile
   - Primary key as foreign key

2. One-to-Many (1:M) / Many-to-One (M:1)
   - One department ↔ Many employees
   - Foreign key on the "many" side

3. Many-to-Many (M:M)
   - Many students ↔ Many courses
   - Junction/join table required

2. Sample Schema Setup

sql

-- One-to-Many example
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INTEGER REFERENCES departments(dept_id)
);

-- Many-to-Many example
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

-- Junction table for Many-to-Many
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrolled_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

B. INNER JOIN (Most Common)

1. Basic INNER JOIN

sql

-- Returns only matching rows from both tables
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
INNER JOIN departments d 
    ON e.dept_id = d.dept_id;

-- Equivalent old syntax (avoid - harder to read)
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- Example with sample data:
-- departments: (1, 'Sales'), (2, 'Engineering')
-- employees: (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', NULL)
-- Result: Alice-Sales, Bob-Engineering (Charlie excluded - no department)

2. INNER JOIN with Multiple Conditions

sql

-- Join with additional conditions
SELECT 
    e.emp_name,
    d.dept_name,
    p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON p.dept_id = d.dept_id 
    AND p.status = 'active'
WHERE d.dept_name = 'Engineering';

3. INNER JOIN with Aliases

sql

-- Table and column aliases for readability
SELECT 
    emp.emp_name AS "Employee Name",
    dept.dept_name AS "Department",
    mgr.emp_name AS "Manager"
FROM employees emp
INNER JOIN departments dept 
    ON emp.dept_id = dept.dept_id
INNER JOIN employees mgr 
    ON emp.manager_id = mgr.emp_id;

C. LEFT JOIN (All from left + matching from right)

1. Basic LEFT JOIN

sql

-- Returns ALL rows from left table (employees)
-- + matching rows from right table (departments)
-- NULLs for non-matching right side
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.dept_id;

-- With sample data:
-- employees: (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', NULL)
-- departments: (1, 'Sales'), (2, 'Engineering')
-- Result: 
--   Alice-Sales
--   Bob-Engineering  
--   Charlie-NULL (included even though no department)

2. LEFT JOIN with WHERE (Find missing relationships)

sql

-- Find employees without departments
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;  -- The key: NULL means no match

-- Result: Charlie (because his dept_id is NULL)

-- Find departments without employees
SELECT 
    d.dept_name,
    e.emp_name
FROM departments d
LEFT JOIN employees e 
    ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

3. LEFT JOIN with COALESCE for defaults

sql

-- Provide default values for NULLs
SELECT 
    e.emp_name,
    COALESCE(d.dept_name, 'No Department') AS department
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.dept_id;

-- Using multiple COALESCE
SELECT 
    e.emp_name,
    COALESCE(d.dept_name, m.dept_name, 'No Department') AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN departments m ON e.manager_dept_id = m.dept_id;

D. RIGHT JOIN (All from right + matching from left)

1. Basic RIGHT JOIN

sql

-- Returns ALL rows from right table (departments)
-- + matching rows from left table (employees)
-- NULLs for non-matching left side
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
RIGHT JOIN departments d 
    ON e.dept_id = d.dept_id;

-- With sample data:
-- employees: (1, 'Alice', 1), (2, 'Bob', 2)
-- departments: (1, 'Sales'), (2, 'Engineering'), (3, 'Marketing')
-- Result:
--   Alice-Sales
--   Bob-Engineering
--   NULL-Marketing (department with no employees)

-- Note: RIGHT JOIN is just LEFT JOIN reversed
-- This is equivalent to:
SELECT e.emp_name, d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

2. When to Use RIGHT JOIN

sql

-- Rarely used - usually better to use LEFT JOIN
-- But can be useful for clarity in complex queries

-- Find all departments and their employees, including empty ones
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM employees e
RIGHT JOIN departments d 
    ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY d.dept_name;

-- Better to write as LEFT JOIN for clarity:
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e  -- Departments is now left table
    ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

E. FULL JOIN (All rows from both tables)

1. Basic FULL JOIN

sql

-- Returns ALL rows from BOTH tables
-- NULLs where no match exists
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
FULL JOIN departments d 
    ON e.dept_id = d.dept_id;

-- With sample data:
-- employees: (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', NULL)
-- departments: (1, 'Sales'), (2, 'Engineering'), (3, 'Marketing')
-- Result:
--   Alice-Sales
--   Bob-Engineering
--   Charlie-NULL (employee without department)
--   NULL-Marketing (department without employee)

2. FULL JOIN Use Cases

sql

-- Find all mismatches (rows that don't match in either direction)
SELECT 
    COALESCE(e.emp_name, 'No Employee') AS employee,
    COALESCE(d.dept_name, 'No Department') AS department
FROM employees e
FULL JOIN departments d 
    ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL  -- Employee without department
   OR d.dept_id IS NULL; -- Department without employee

-- Data reconciliation between two systems
SELECT 
    COALESCE(old.emp_id, new.emp_id) AS emp_id,
    old.emp_name AS old_name,
    new.emp_name AS new_name,
    CASE 
        WHEN old.emp_id IS NULL THEN 'Added in new system'
        WHEN new.emp_id IS NULL THEN 'Deleted in new system'
        WHEN old.emp_name != new.emp_name THEN 'Name changed'
        ELSE 'No change'
    END AS status
FROM old_employees old
FULL JOIN new_employees new 
    ON old.emp_id = new.emp_id
WHERE old.emp_id IS NULL 
   OR new.emp_id IS NULL
   OR old.emp_name != new.emp_name;

F. CROSS JOIN (Cartesian Product)

1. Basic CROSS JOIN

sql

-- Returns ALL combinations of rows
-- No ON clause needed (or allowed)
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
CROSS JOIN departments d;

-- With 3 employees and 3 departments → 9 rows
-- Equivalent to:
SELECT e.emp_name, d.dept_name
FROM employees e, departments d;

-- Useful for generating test data or all combinations
SELECT 
    s.size,
    c.color
FROM (VALUES ('S'), ('M'), ('L'), ('XL')) AS s(size)
CROSS JOIN (VALUES ('Red'), ('Blue'), ('Green')) AS c(color);
-- Result: 12 combinations (4 sizes × 3 colors)

2. Practical CROSS JOIN Example

sql

-- Generate calendar for current month
WITH dates AS (
    SELECT generate_series(
        DATE_TRUNC('month', CURRENT_DATE),
        DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day',
        INTERVAL '1 day'
    )::DATE AS day
),
hours AS (
    SELECT generate_series(0, 23) AS hour
)
SELECT 
    d.day,
    h.hour,
    to_char(d.day, 'Dy') AS weekday
FROM dates d
CROSS JOIN hours h
ORDER BY d.day, h.hour;

G. Many-to-Many Relationships

1. Junction Table Structure

sql

-- Basic many-to-many with junction table
SELECT 
    s.student_name,
    c.course_name,
    e.enrolled_date,
    e.grade
FROM students s
INNER JOIN enrollments e 
    ON s.student_id = e.student_id
INNER JOIN courses c 
    ON e.course_id = c.course_id
ORDER BY s.student_name, c.course_name;

-- Sample data:
-- students: (1, 'Alice'), (2, 'Bob')
-- courses: (1, 'Math'), (2, 'Science'), (3, 'History')
-- enrollments: (1,1,'A'), (1,2,'B'), (2,1,'C'), (2,3,'A')
-- Result:
--   Alice-Math-A
--   Alice-Science-B
--   Bob-Math-C
--   Bob-History-A

2. Querying Many-to-Many Relationships

sql

-- Find students taking specific course
SELECT 
    s.student_name,
    e.grade
FROM students s
INNER JOIN enrollments e 
    ON s.student_id = e.student_id
WHERE e.course_id = (SELECT course_id FROM courses WHERE course_name = 'Math');

-- Find courses taken by specific student
SELECT 
    c.course_name,
    e.grade
FROM courses c
INNER JOIN enrollments e 
    ON c.course_id = e.course_id
WHERE e.student_id = (SELECT student_id FROM students WHERE student_name = 'Alice');

-- Count students per course
SELECT 
    c.course_name,
    COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e 
    ON c.course_id = e.course_id
GROUP BY c.course_name
ORDER BY student_count DESC;

3. Advanced Many-to-Many Queries

sql

-- Find students taking multiple specific courses
SELECT 
    s.student_name,
    COUNT(DISTINCT c.course_id) AS courses_taken
FROM students s
INNER JOIN enrollments e 
    ON s.student_id = e.student_id
INNER JOIN courses c 
    ON e.course_id = c.course_id
WHERE c.course_name IN ('Math', 'Science')
GROUP BY s.student_id, s.student_name
HAVING COUNT(DISTINCT c.course_id) = 2;  -- Taking both courses

-- Find common courses between two students
SELECT 
    c.course_name
FROM courses c
INNER JOIN enrollments e1 
    ON c.course_id = e1.course_id
INNER JOIN enrollments e2 
    ON c.course_id = e2.course_id
WHERE e1.student_id = (SELECT student_id FROM students WHERE student_name = 'Alice')
  AND e2.student_id = (SELECT student_id FROM students WHERE student_name = 'Bob');

H. Self Joins (Table joining with itself)

1. Basic Self Join - Employee Hierarchy

sql

-- Find employees and their managers
SELECT 
    e.emp_name AS "Employee",
    m.emp_name AS "Manager",
    d.dept_name AS "Department"
FROM employees e
LEFT JOIN employees m 
    ON e.manager_id = m.emp_id  -- Join to same table
LEFT JOIN departments d 
    ON e.dept_id = d.dept_id
ORDER BY m.emp_name NULLS FIRST, e.emp_name;

-- Sample data:
-- employees: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1)
-- Result:
--   Alice-NULL
--   Bob-Alice
--   Charlie-Alice

2. Self Join for Finding Pairs

sql

-- Find all pairs of employees in same department
SELECT 
    e1.emp_name AS employee1,
    e2.emp_name AS employee2,
    d.dept_name
FROM employees e1
INNER JOIN employees e2 
    ON e1.dept_id = e2.dept_id  -- Same department
    AND e1.emp_id < e2.emp_id   -- Avoid duplicates (A-B and B-A)
INNER JOIN departments d 
    ON e1.dept_id = d.dept_id
ORDER BY d.dept_name, e1.emp_name;

-- Without "e1.emp_id < e2.emp_id" you'd get:
-- Alice-Bob and Bob-Alice (duplicate pairs)

3. Hierarchical Queries with Self Join

sql

-- Find full management chain (limited depth)
SELECT 
    e1.emp_name AS "Employee",
    e2.emp_name AS "Manager",
    e3.emp_name AS "Manager's Manager"
FROM employees e1
LEFT JOIN employees e2 
    ON e1.manager_id = e2.emp_id
LEFT JOIN employees e3 
    ON e2.manager_id = e3.emp_id
ORDER BY e3.emp_name NULLS FIRST, e2.emp_name NULLS FIRST, e1.emp_name;

-- Recursive CTE for unlimited depth (PostgreSQL specific)
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Top-level employees (no manager)
    SELECT 
        emp_id,
        emp_name,
        manager_id,
        1 AS level,
        emp_name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Find subordinates
    SELECT 
        e.emp_id,
        e.emp_name,
        e.manager_id,
        eh.level + 1,
        eh.path || ' -> ' || e.emp_name
    FROM employees e
    INNER JOIN employee_hierarchy eh 
        ON e.manager_id = eh.emp_id
)
SELECT 
    emp_name,
    level,
    path
FROM employee_hierarchy
ORDER BY path;

I. Join Performance & Best Practices

1. Indexing for Joins

sql

-- Foreign keys should be indexed for JOIN performance
CREATE INDEX idx_employees_dept_id ON employees(dept_id);
CREATE INDEX idx_employees_manager_id ON employees(manager_id);
CREATE INDEX idx_enrollments_student_id ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

-- Composite indexes for multi-column joins
CREATE INDEX idx_enrollments_student_course ON enrollments(student_id, course_id);

2. EXPLAIN to Analyze Join Performance

sql

-- See how PostgreSQL executes the join
EXPLAIN ANALYZE
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Look for:
-- "Hash Join" or "Nested Loop" - Join algorithms used
-- "Seq Scan" vs "Index Scan" - How tables are accessed
-- Actual execution time

3. Join Best Practices

sql

-- 1. Use explicit JOIN syntax (not comma-separated)
-- Good:
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
-- Avoid:
SELECT * FROM table1, table2 WHERE table1.id = table2.id;

-- 2. Use table aliases for readability
SELECT 
    e.name,
    d.department,
    m.name AS manager
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- 3. Put filter conditions in WHERE, not ON (except for OUTER JOINs)
-- For INNER JOIN, these are equivalent:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t2.status = 'active';
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.status = 'active';

-- For LEFT JOIN, they're different!
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND t2.status = 'active';
-- Includes all t1 rows, t2 columns NULL if status not 'active'

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.status = 'active';
-- Excludes t1 rows where t2.status is not 'active' or NULL

-- 4. Use EXISTS for "exists" checks instead of JOIN
-- Find departments with employees:
-- Good (usually faster):
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

-- Less optimal (might return duplicates):
SELECT DISTINCT d.* 
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id;

4. Common Join Patterns

sql

-- Pattern 1: Get all with defaults (LEFT JOIN + COALESCE)
SELECT 
    e.emp_name,
    COALESCE(d.dept_name, 'Unassigned') AS department,
    COALESCE(m.emp_name, 'No Manager') AS manager
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Pattern 2: Find missing relationships
-- What departments have no employees?
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

-- Pattern 3: Aggregate with JOINs
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY employee_count DESC;

-- Pattern 4: Three-table chain
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Understanding joins is fundamental to working with relational databases. Each join type serves specific purposes, and choosing the right one depends on whether you want to include or exclude non-matching rows.