If you’re working with PostgreSQL and need to pull data from multiple tables, you’ll need SQL joins. And when you want to add logic to your queries — like categorizing values or handling nulls — that’s where CASE statements come in. This post covers both with practical examples you can run directly in psql.
We’ll set up sample tables, insert some data, and then walk through each join type and conditional pattern with expected output so you can see exactly what each query returns.
Prerequisites
- PostgreSQL installed — follow How to Install PostgreSQL on WSL Ubuntu 22.04 if you need to set it up
- Access to
psqlor any PostgreSQL client like pgAdmin 4
Setting Up Sample Data
First, create three tables we’ll use throughout the examples: employees, departments, and bonuses.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary INT
);
CREATE TABLE bonuses (
employee_id INT,
bonus_amount INT
);Now insert some data. Notice that not every employee has a bonus, and not every department has employees — this is intentional so the join examples produce meaningful results.
INSERT INTO departments VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales'),
(4, 'HR');
INSERT INTO employees VALUES
(101, 'Alice', 1, 75000),
(102, 'Bob', 1, 65000),
(103, 'Carol', 2, 55000),
(104, 'Dave', 3, 45000),
(105, 'Eve', NULL, 40000);
INSERT INTO bonuses VALUES
(101, 5000),
(103, 3000),
(106, 2000);Key things about this data:
- Eve (105) has no department (
NULL) - HR department (4) has no employees
- Employee 106 in the bonuses table doesn’t exist in the employees table
These gaps are what make joins interesting.
SQL Joins in PostgreSQL
INNER JOIN
Returns only rows that have a match in both tables. If there’s no match, the row is excluded.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;Result:
name | department_name
-------+-----------------
Alice | Engineering
Bob | Engineering
Carol | Marketing
Dave | SalesEve is missing because her department_id is NULL — there’s no matching department. HR is also missing because no employee belongs to it. That’s the thing with INNER JOIN: both sides need a match.
LEFT JOIN
Returns all rows from the left table, and matching rows from the right table. If there’s no match on the right side, you get NULL.
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id; name | department_name
-------+-----------------
Alice | Engineering
Bob | Engineering
Carol | Marketing
Dave | Sales
Eve | NULLEve shows up now with NULL for department — the left table (employees) is fully preserved. This is probably the join you’ll use most often. Anytime you want “give me all records from this table, plus any matching info from that other table,” LEFT JOIN is what you want.
RIGHT JOIN
The opposite of LEFT JOIN — returns all rows from the right table, with matching data from the left.
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id; name | department_name
-------+-----------------
Alice | Engineering
Bob | Engineering
Carol | Marketing
Dave | Sales
NULL | HRHR appears with NULL for the employee name since no one is assigned to it. Eve is gone because she’s on the left side with no match. In practice, most people just flip the table order and use LEFT JOIN instead of RIGHT JOIN — it reads more naturally.
FULL OUTER JOIN
Returns all rows from both tables. Where there’s no match on either side, you get NULL.
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id; name | department_name
-------+-----------------
Alice | Engineering
Bob | Engineering
Carol | Marketing
Dave | Sales
Eve | NULL
NULL | HRBoth Eve and HR show up. You don’t use FULL OUTER JOIN as often, but it’s useful when you need to find unmatched records on both sides — like reconciling data between two systems.
Quick Reference
| Join Type | What It Returns |
|---|---|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN | All left table rows + matching right rows |
| RIGHT JOIN | All right table rows + matching left rows |
| FULL OUTER JOIN | All rows from both tables |
Joining More Than Two Tables
You can chain multiple joins. Here’s how to get employee names with their department and bonus in one query:
SELECT
e.name,
d.department_name,
b.bonus_amount
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN bonuses b ON e.employee_id = b.employee_id; name | department_name | bonus_amount
-------+-----------------+--------------
Alice | Engineering | 5000
Bob | Engineering | NULL
Carol | Marketing | 3000
Dave | Sales | NULL
Eve | NULL | NULLLEFT JOIN on both so we keep all employees, even if they don’t have a department or a bonus.
Conditional Statements with CASE
The CASE statement works like if/else logic inside your SQL query. You use it to create computed columns, categorize data, or handle conditional formatting right in the query.
Basic CASE Statement
Categorize employees by salary range:
SELECT
name,
salary,
CASE
WHEN salary >= 70000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees; name | salary | salary_level
-------+--------+--------------
Alice | 75000 | High
Bob | 65000 | Medium
Carol | 55000 | Medium
Dave | 45000 | Low
Eve | 40000 | LowThe CASE evaluates conditions top to bottom and returns the first match. The ELSE catches everything that didn’t match any WHEN clause.
CASE with NULL Handling
A common real-world pattern — labeling rows that have missing data:
SELECT
name,
CASE
WHEN department_id IS NULL THEN 'Unassigned'
ELSE 'Assigned'
END AS assignment_status
FROM employees; name | assignment_status
-------+-------------------
Alice | Assigned
Bob | Assigned
Carol | Assigned
Dave | Assigned
Eve | UnassignedUsing COALESCE for NULL Defaults
If you just need to replace NULL with a default value, COALESCE is shorter than writing a full CASE block:
SELECT
e.name,
COALESCE(b.bonus_amount, 0) AS bonus
FROM employees e
LEFT JOIN bonuses b ON e.employee_id = b.employee_id; name | bonus
-------+-------
Alice | 5000
Bob | 0
Carol | 3000
Dave | 0
Eve | 0COALESCE returns the first non-NULL value from its arguments. You’ll use this a lot when working with LEFT JOINs since unmatched rows come back as NULL.
Combining Joins and CASE
This is where it gets practical. Let’s build a query that brings everything together — employee info, department, bonus, and a computed column:
SELECT
e.name,
COALESCE(d.department_name, 'No Department') AS department,
e.salary,
COALESCE(b.bonus_amount, 0) AS bonus,
e.salary + COALESCE(b.bonus_amount, 0) AS total_compensation,
CASE
WHEN e.salary + COALESCE(b.bonus_amount, 0) >= 70000 THEN 'Senior'
WHEN e.salary + COALESCE(b.bonus_amount, 0) >= 50000 THEN 'Mid'
ELSE 'Junior'
END AS level
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN bonuses b ON e.employee_id = b.employee_id
ORDER BY total_compensation DESC; name | department | salary | bonus | total_compensation | level
-------+---------------+--------+-------+--------------------+--------
Alice | Engineering | 75000 | 5000 | 80000 | Senior
Bob | Engineering | 65000 | 0 | 65000 | Mid
Carol | Marketing | 55000 | 3000 | 58000 | Mid
Dave | Sales | 45000 | 0 | 45000 | Junior
Eve | No Department | 40000 | 0 | 40000 | JuniorThis query joins three tables, handles NULLs with COALESCE, creates a computed column for total compensation, and categorizes each employee with a CASE statement. This kind of pattern shows up constantly in reporting queries.
CASE in WHERE and ORDER BY
CASE isn’t limited to the SELECT clause. You can use it in ORDER BY to create custom sort orders:
SELECT name, salary
FROM employees
ORDER BY
CASE
WHEN department_id = 1 THEN 1
WHEN department_id = 3 THEN 2
ELSE 3
END;This puts Engineering first, then Sales, then everyone else — without changing the data itself.
You can also use it in aggregate functions to do conditional counting:
SELECT
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary >= 50000 THEN 1 END) AS above_50k,
COUNT(CASE WHEN salary < 50000 THEN 1 END) AS below_50k
FROM employees; total_employees | above_50k | below_50k
-----------------+-----------+-----------
5 | 3 | 2This is a handy trick to avoid writing separate queries for each count.
Cleanup
When you’re done experimenting, drop the tables:
DROP TABLE bonuses;
DROP TABLE employees;
DROP TABLE departments;Conclusion
Joins and CASE statements are the two things you’ll reach for the most when writing anything beyond simple SELECT queries. Once you’re comfortable combining LEFT JOINs with COALESCE and CASE, you can handle most reporting and data extraction tasks without needing anything more complex.
If you don’t have PostgreSQL set up yet, check How to Install PostgreSQL on WSL Ubuntu 22.04. For a GUI to run these queries more comfortably, How to Access PostgreSQL in Ubuntu WSL with pgAdmin 4 walks you through setting up pgAdmin.
