A join is how you ask one question across two tables. The database keeps patients in one table and appointments in another, because storing the patient's name on every appointment row would mean fixing it in ten places when it's misspelled. A join puts them back together for the length of one query.
That's the whole idea. The part that takes practice is that there are several kinds of joins, and they disagree about what to do with rows that have no partner on the other side. Pick the wrong one and rows quietly disappear from your result, and nothing warns you.
This article uses a small hospital database I built for practice: 10 patients, 10 doctors, 10 departments, 10 appointments, 10 prescriptions. Small on purpose, so you can count the rows and see which join dropped what.
The tables
CREATE SCHEMA city_hospital;
SET search_path TO city_hospital;
CREATE TABLE patients (
patient_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
age INT,
gender VARCHAR(1),
city VARCHAR(50),
blood_type VARCHAR(5)
);
CREATE TABLE doctors (
doctor_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
specialisation VARCHAR(100),
dept_id INT,
years_exp INT,
supervisor_id INT
);
CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
appt_date DATE,
diagnosis VARCHAR(200),
fee DECIMAL(8,2)
);
(There's also a prescriptions table with an appointment_id, and a departments table. And the first line I actually typed was createt schema city_hospital;, which PostgreSQL rejected with a syntax error. Typos in keywords are the most common error you'll see and the easiest to miss.)
Notice what's not there: no REFERENCES. I didn't declare foreign keys on this database. Joins still work because a join only needs the values to match. appointments.patient_id = 3 and patients.patient_id = 3 is enough. A foreign key is a rule that stops you from inserting a bad value; a join is a question you ask at query time. They're related but separate, and you can have either without the other.
Two facts about the data matter for everything below. Patients 9 and 10 (Samuel Otieno and Winnie Adhiambo) have never had an appointment. Doctors 8, 9, and 10 (Dr. Hassan Otieno, Dr. Ivy Kariuki, Dr. James Abdi) have never seen a patient. Every join type below treats those five people differently.
INNER JOIN: only the matches
SELECT a.appointment_id,
p.full_name AS patient_name,
d.full_name AS doctor_name,
a.appt_date,
a.diagnosis
FROM appointments a
INNER JOIN patients p ON p.patient_id = a.patient_id
INNER JOIN doctors d ON d.doctor_id = a.doctor_id;
10 rows. One per appointment, each with the patient's name and the doctor's name pulled in from the other two tables.
INNER JOIN keeps a row only when both sides match. Samuel Otieno and Winnie Adhiambo are not in this result, and neither are the three doctors with no appointments. That's correct for this question ("show me the appointments") and wrong for many others.
The aliases (a, p, d) aren't decoration. Both patients and doctors have a column called full_name; without a prefix, PostgreSQL wouldn't know which one you mean.
LEFT JOIN: keep everything on the left
SELECT p.full_name, a.appointment_id, a.appt_date, a.diagnosis
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id;
12 rows. The 10 appointments, plus Winnie Adhiambo and Samuel Otieno, with nothing in the appointment columns. That's what LEFT JOIN promises: every row from the table on the left of the word JOIN appears at least once, and if there's no match, the right-hand columns are NULL.
Two things about that count. It's 12, not 10 + 2 = 12 by coincidence. Mwangi Njoroge and Peter Kimani each have two appointments, so they appear twice, and two patients appear zero times on the appointment side but once thanks to the LEFT JOIN. 8 patients with appointments produce 10 rows; 2 without produce 2 rows. That's how joins multiply and preserve rows at the same time.
The anti-join: LEFT JOIN plus IS NULL
The most useful trick in this article. "Which patients have never had an appointment?" is a LEFT JOIN where you keep only the rows that failed to match:
SELECT p.full_name, p.city
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;
2 rows: Winnie Adhiambo, Nairobi; Samuel Otieno, Kisumu.
The WHERE a.appointment_id IS NULL works because appointment_id is a primary key and can never be NULL for a real appointment. If the row came back NULL, there was no appointment. Always test the IS NULL on a column that can't be NULL in the real data, or you'll pick up genuine appointments with a missing value too.
RIGHT JOIN: the same thing, the other way round
SELECT d.doctor_id, d.full_name, p.full_name AS patient, a.appointment_id
FROM appointments a
RIGHT JOIN doctors d ON a.doctor_id = d.doctor_id
LEFT JOIN patients p ON p.patient_id = a.patient_id;
13 rows: the 10 appointments, then Dr. James Abdi, Dr. Hassan Otieno, and Dr. Ivy Kariuki with empty patient and appointment columns.
RIGHT JOIN keeps every row from the table on the right of the word JOIN. I wrote this one with appointments first because the question was phrased "show ALL doctors and, if they have seen a patient, the patient's name", and I wanted the appointments to be the thing being attached. Then I needed the patient's name, which lives in a third table, so a LEFT JOIN to patients follows. It has to be LEFT, not INNER: an INNER JOIN to patients would have thrown away the three doctors again, because they have no patient to match.
In practice, most people write FROM doctors d LEFT JOIN appointments a and never use RIGHT JOIN at all. The two are mirror images. Pick one habit; I'd pick LEFT, because "the table I'm keeping goes first" is easier to read six months later.
FULL OUTER JOIN: keep both sides
SELECT d.full_name, a.appointment_id
FROM doctors d
FULL OUTER JOIN appointments a ON a.doctor_id = d.doctor_id
ORDER BY d.doctor_id;
13 rows again. Every doctor, every appointment, matched where possible, NULLs where not. On this data, it gives the same as the RIGHT JOIN, because every appointment has a valid doctor. FULL OUTER JOIN earns its place when both sides can have orphans, for example, when you're comparing two exports and want to see what's only in the first, only in the second, and in both.
Self join: doctors and their supervisors
The doctors table has a supervisor_id column that points back to doctor_id in the same table. To show each doctor's supervisor by name, join the table to itself with two different aliases:
SELECT d.full_name AS doctor,
s.full_name AS supervisor
FROM doctors d
LEFT JOIN doctors s ON d.supervisor_id = s.doctor_id
ORDER BY d.doctor_id;
Dr. Brian Wambua reports to Dr. Amina Omondi; Dr. Esther Kamau reports to Dr. Clara Mutua; Dr. Amina Omondi and Dr. James Abdi have no supervisor, so their supervisor column is NULL. That's why this is a LEFT JOIN: an INNER JOIN would drop the two department heads.
Three tables at once
Joins chain. Each JOIN ... ON attaches one more table to what you've built so far:
SELECT a.appointment_id,
p.full_name AS patient_name,
d.full_name AS doctor_name,
pr.medicine_name
FROM appointments a
INNER JOIN patients p ON a.patient_id = p.patient_id
INNER JOIN doctors d ON d.doctor_id = a.doctor_id
INNER JOIN prescriptions pr ON pr.appointment_id = a.appointment_id;
10 rows, one per appointment, with the medicine prescribed. I start from appointments because it's the table in the middle: it has a patient_id, a doctor_id, and prescriptions point at it. Starting from the middle table makes each ON clause a single hop.
The mistake that catches everyone: WHERE on a LEFT JOIN
Say you want every patient, with their appointments from 5 April onwards. The natural first attempt:
SELECT p.full_name, a.appt_date
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appt_date >= '2024-04-05';
6 rows. The LEFT JOIN did its job and kept all ten patients, and then the WHERE threw four of them out again, because NULL >= '2024-04-05' is not true. The patients with no appointment, and the ones whose only appointment was before 5 April, are gone. You wrote LEFT JOIN and got INNER JOIN behaviour.
The fix is to put the condition in the ON clause, where it decides what matches rather than which rows survive:
SELECT p.full_name, a.appt_date
FROM patients p
LEFT JOIN appointments a
ON p.patient_id = a.patient_id
AND a.appt_date >= '2024-04-05'
ORDER BY p.patient_id;
10 rows. Every patient is there; the ones with no qualifying appointment show NULL in appt_date.
The rule: a filter on the left table can go in WHERE. A filter on the right (optional) table goes in ON, unless it's the IS NULL anti-join test.
Which join, when
Ask yourself one question: if a row on one side has no partner, do I still want to see it?
No, I only want matched pairs: INNER JOIN. Appointments with their patient and doctor.
Yes, I want every row from my main table, whether or not it matched: LEFT JOIN, with the main table written first. Every patient, appointment or not.
I want the rows that didn't match: LEFT JOIN plus WHERE right_table.key IS NULL. Patients who never came.
I want everything from both sides, matched or not: FULL OUTER JOIN. Comparing two lists.
The table refers to itself: a self join, same table, two aliases. Doctors and supervisors.
Every combination of two tables, no matching at all: CROSS JOIN. 10 patients × 10 doctors = 100 rows. Rare, and usually an accident when someone forgets the ON clause.
What I'd tell someone learning joins
Count the rows before and after. If you join 10 patients to appointments and get 10 rows, ask where the two patients without appointments went. If you get 12, ask why two patients are there twice. The count is the fastest check there is.
Write the table you want to keep first, and use LEFT JOIN. RIGHT JOIN is the same thing backwards, and mixing the two in one query is how people confuse themselves.
When a LEFT JOIN result is smaller than the left table, look at the WHERE clause. Something in it is filtering on the right-hand table.
And build a tiny database like this one. With ten rows per table, you can predict every result before you run it, and the moment your prediction is wrong is the moment you learn something.
Top comments (0)