Master SQL Joins with a Hospital Database

Practice every SQL join type using a hospital database with real scenarios.

Introduction

30 Second Summary

SQL join questions dominate data science interviews. Interviewers do not just ask you to write a query. They ask you to pick the right join type for a scenario and explain what happens to the rows that do not match.

In this project, you will build a hospital database and answer progressively harder business questions using every SQL join type. Each question maps to a real scenario that makes it obvious why one join is the right tool over another.

What You'll Build

You will open MySQL Workbench, look at a multi-table business question, immediately identify which join type solves it, write the query, and explain the NULL behavior to an interviewer.

By the end of this project, you'll have:

  • A working hospital database with four related tables that you query using INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN, CROSS JOIN, and an emulated FULL OUTER JOIN.
  • A collection of solved interview-style questions where each join type is paired with a real hospital scenario explaining why that specific join is the right tool and what the expected output means.
  • Hands-on experience with the MySQL-specific FULL OUTER JOIN workaround using UNION, and the anti-join pattern for finding rows with no match.
  • Secret Mission: expose the WHERE vs ON trap by predicting how moving a filter changes your LEFT JOIN results.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, INSERT, CREATE TABLE) and have MySQL Workbench 8.0 installed with a local MySQL server running.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. Committing to a concrete goal keeps you focused when queries get tricky.

Set Up the Hospital Database

Before you can practice joins, you need data that is specifically designed to make each join type produce different results.

In this step you will create a hospital database with four related tables. The data has intentional gaps: two patients with no appointments, one doctor with no patients, and two doctors with no supervisor. These gaps are what make each join type behave differently in later steps.

In this step, get ready to:
  • Open a new SQL tab and create the hospital_joins database.
  • Define four tables: patients, doctors, appointments, and shifts.
  • Populate the tables with sample data and verify row counts.
Open a new SQL tab

You already have MySQL Workbench open and connected to your local server. You need a fresh editor tab to write your setup script.

  • Open a new SQL query tab by clicking **File** in the menu bar, then selecting **New Query Tab**. Alternatively, press Cmd+T (macOS) or Ctrl+T (Windows).

You should see an empty SQL editor panel with a blinking cursor.

Create the database and define the tables

The database needs four tables that relate to each other through foreign keys. This structure lets you practice multi-table joins in later steps.

  • Create the database and define the patients and doctors tables by pasting the following SQL into your editor:
-- Create the hospital database
DROP DATABASE IF EXISTS hospital_joins;
CREATE DATABASE hospital_joins;
USE hospital_joins;

-- Patients table
CREATE TABLE patients (
    patient_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    gender VARCHAR(10) NOT NULL,
    blood_type VARCHAR(5) NOT NULL
);

-- Doctors table (supervisor_id references another doctor for SELF JOIN)
CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    specialty VARCHAR(50) NOT NULL,
    supervisor_id INT,
    FOREIGN KEY (supervisor_id) REFERENCES doctors(doctor_id)
);

What does this code do?

DROP DATABASE IF EXISTS hospital_joins; removes any previous version of this database so you start fresh every time.

The doctors table has a supervisor_id column that references another row in the same table. This self-referencing design is what you will query with a SELF JOIN later.

  • Below that code, paste the appointments and shifts table definitions:
-- Appointments table (links patients to doctors)
CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    diagnosis VARCHAR(200),
    cost DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Shifts table (small reference table for CROSS JOIN)
CREATE TABLE shifts (
    shift_id INT PRIMARY KEY,
    shift_name VARCHAR(20) NOT NULL
);

Why four tables?

The appointments table links patients to doctors through foreign keys, creating the many-to-many relationship you will query with INNER JOIN and LEFT JOIN.

The shifts table is deliberately small (just 3 rows) so you can see a CROSS JOIN produce a manageable 18-row grid instead of thousands of rows.

  • Execute the entire script by clicking the lightning bolt icon in the toolbar, or pressing Cmd+Shift+Return (macOS) or Ctrl+Shift+Return (Windows).

You should see green checkmarks in the Output panel at the bottom, confirming all statements executed successfully.

Seeing an error in the Output panel?

  • Check that you pasted the second block directly below the first, with no missing semicolons between them.
  • If you see a foreign key error, make sure the patients and doctors tables appear before appointments in your script. Foreign keys can only reference tables that already exist.

Help me debug my table creation script.

Populate the tables with sample data

Empty tables are not useful for practicing joins. The sample data is designed with specific gaps that you will discover as you write queries in later steps.

  • Clear the SQL editor, then paste the patient and doctor data:
-- Insert patients (8 total, 2 will have no appointments)
INSERT INTO patients VALUES
(1, 'John Smith', 45, 'Male', 'A+'),
(2, 'Maria Garcia', 32, 'Female', 'O+'),
(3, 'David Johnson', 58, 'Male', 'B+'),
(4, 'Sarah Lee', 27, 'Female', 'AB-'),
(5, 'Michael Brown', 63, 'Male', 'O-'),
(6, 'Emma Wilson', 41, 'Female', 'A-'),
(7, 'Priya Sharma', 35, 'Female', 'B+'),
(8, 'Olivia Chen', 29, 'Female', 'O+');

-- Insert doctors (6 total, 2 have no supervisor, 1 has no patients)
INSERT INTO doctors VALUES
(1, 'Dr. Sarah Wilson', 'Cardiology', NULL),
(2, 'Dr. James Carter', 'Neurology', NULL),
(3, 'Dr. Emily Chen', 'Cardiology', 1),
(4, 'Dr. Robert Kim', 'Neurology', 2),
(5, 'Dr. Lisa Patel', 'Orthopedics', 1),
(6, 'Dr. Amanda Foster', 'Dermatology', 2);

Why do some doctors have NULL for supervisor_id?

Dr. Sarah Wilson and Dr. James Carter are department heads with no one above them. Their supervisor_id is NULL. This is what makes the SELF JOIN interesting later. A LEFT JOIN will preserve these rows while an INNER JOIN would drop them.

  • Below that, paste the appointment and shift data:
-- Insert appointments (10 total, patients 7 and 8 have none, doctor 6 has none)
INSERT INTO appointments VALUES
(1, 1, 1, '2026-06-01', 'Hypertension', 250.00),
(2, 2, 3, '2026-06-02', 'Chest Pain', 300.00),
(3, 3, 2, '2026-06-03', 'Migraine', 200.00),
(4, 4, 5, '2026-06-04', 'Knee Injury', 350.00),
(5, 5, 1, '2026-06-05', 'Arrhythmia', 400.00),
(6, 1, 3, '2026-06-08', 'Follow-up', 150.00),
(7, 6, 4, '2026-06-09', 'Vertigo', 275.00),
(8, 2, 2, '2026-06-10', 'Headache', 200.00),
(9, 3, 5, '2026-06-11', 'Back Pain', 325.00),
(10, 5, 1, '2026-06-12', 'Check-up', 175.00);

-- Insert shifts (3 shifts for CROSS JOIN)
INSERT INTO shifts VALUES
(1, 'Morning'),
(2, 'Afternoon'),
(3, 'Night');

Where are the intentional gaps?

  • Patients 7 (Priya Sharma) and 8 (Olivia Chen) have no rows in appointments. LEFT JOIN will reveal them. INNER JOIN will hide them.
  • Doctor 6 (Dr. Amanda Foster) has no patients assigned to her. RIGHT JOIN will show her.
  • Doctors 1 and 2 have NULL supervisor. SELF JOIN behavior depends on whether you use LEFT or INNER.
  • Execute the script by clicking the lightning bolt icon again.

The Output panel should confirm the rows were inserted with no errors.

Before you verify the row counts, how many rows do you expect in the patients table based on the INSERT statement you just ran?

  • Clear the editor and run these verification queries:
SELECT COUNT(*) FROM patients;
SELECT COUNT(*) FROM doctors;
SELECT COUNT(*) FROM appointments;
SELECT COUNT(*) FROM shifts;

You should see four result tabs showing: 8, 6, 10, and 3 respectively.

Seeing fewer rows than expected?

  • If you get 0 rows, check that you ran USE hospital_joins; before the INSERT statements. Without it, MySQL inserts into whatever database was previously selected.
  • If you see a duplicate key error, run DROP DATABASE hospital_joins; and start again from the CREATE DATABASE step.

Why am I getting unexpected row counts in my hospital database?

✔️ Awesome, I've got everything!

Great. Make sure you see 8 patients, 6 doctors, 10 appointments, and 3 shifts confirmed in your result panels.

ⓧ I'd like to double check the full code

Your complete 01_setup.sql should contain all four sections you built above, in this order:

  • Database creation (DROP DATABASE, CREATE DATABASE, USE) plus the patients and doctors table definitions.
  • The appointments and shifts table definitions.
  • INSERT statements for patients (8 rows) and doctors (6 rows).
  • INSERT statements for appointments (10 rows) and shifts (3 rows).

Scroll up and confirm each block is present in order. If any section is missing, paste it in and re-execute the full script.

Your hospital database is ready with realistic, gap-filled data. Next up, you will write your first INNER JOIN to pull patient visit logs and discover what data it silently drops.

Write INNER JOIN Queries

Your hospital database is live with patients, doctors, and appointments spread across three tables. Now it's time to answer the first real business question: "Which patients saw which doctors?"

INNER JOIN connects rows that have matching values across multiple tables. It is the most common join type in SQL interviews. Understanding what it returns is important. Understanding what it silently drops is even more important.

In this step, get ready to:
  • Write a three-table INNER JOIN query that produces a complete visit log.
  • Identify which patients and doctors are silently excluded from the results.
  • Write a revenue aggregation query that reveals another gap caused by INNER JOIN.
Write the visit log query

The hospital administrator needs a complete visit log showing which patient saw which doctor, when, and what the diagnosis was. The data you need lives in three tables: patients, appointments, and doctors.

  • In MySQL Workbench, open a new SQL tab by clicking the **New SQL Tab** button (the "+" icon above the query editor) or pressing Cmd+T (macOS) or Ctrl+T (Windows).
  • Write the three-table INNER JOIN query by pasting this code into the new tab:
USE hospital_joins;

-- SCENARIO: The hospital administrator needs a complete visit log.
-- WHY INNER JOIN: You only want rows where a patient actually had an appointment
-- with a doctor. No appointment = no row in the results.

-- Query 1: Show every appointment with patient name, doctor name, date, and diagnosis
-- EXPECTED OUTCOME: 10 rows. Every row is fully populated. No NULLs.
-- Patients Priya Sharma and Olivia Chen are MISSING (no appointments).
-- Dr. Amanda Foster is MISSING (no patients yet).
SELECT
    p.name AS patient_name,
    d.name AS doctor_name,
    a.appointment_date,
    a.diagnosis
FROM patients p
INNER JOIN appointments a ON p.patient_id = a.patient_id
INNER JOIN doctors d ON a.doctor_id = d.doctor_id
ORDER BY a.appointment_date;

What does this query do?

This query chains two INNER JOINs together to connect three tables.

  • patients p INNER JOIN appointments a ON p.patient_id = a.patient_id links each patient to their appointments. If a patient has no appointment, that patient is excluded.
  • INNER JOIN doctors d ON a.doctor_id = d.doctor_id then links each appointment to the doctor who conducted it. If a doctor has no appointments, they are excluded.
  • The result contains only rows where ALL three tables have a matching record.

Before you run this, think: you have 8 patients and 6 doctors. How many rows do you expect to get back?

  • Run the query by clicking the lightning bolt icon or pressing Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows).

You should see exactly 10 rows in the result grid. Every row has all four columns filled: patient_name, doctor_name, appointment_date, and diagnosis. No NULLs anywhere.

Seeing 0 rows or an error?

  • If you see an "Unknown database" error, make sure you ran the full setup script from Step 1 first. The hospital_joins database must already exist.
  • If you see a syntax error, check that the ON clauses reference the correct column names. Column names are case-sensitive on some systems.

Help me debug my INNER JOIN query that returns no results.

Observe what INNER JOIN silently drops

You got 10 rows. But your database has 8 patients and 6 doctors. Scroll through the results and look for two names that are completely absent.

  • Scan the patient_name column. You will not find Priya Sharma or Olivia Chen anywhere in the results.
  • Scan the doctor_name column. Dr. Amanda Foster does not appear.

Why are they missing?

INNER JOIN only returns rows where both sides have a match. Priya Sharma and Olivia Chen have no appointments, so there is nothing for their patient_id to match on in the appointments table. They are silently excluded.

Dr. Amanda Foster has no patients assigned yet, so her doctor_id never appears in the appointments table. INNER JOIN drops unmatched rows from BOTH sides with no warning.

This silent exclusion is exactly why you cannot always rely on INNER JOIN. In the next step, you will learn LEFT JOIN, which preserves those missing rows.

Write a revenue aggregation query

Now imagine the finance team asks: "How many appointments does each doctor have, and what is their total revenue?" This requires grouping and summing across a join.

  • Add the revenue aggregation query below your first query in the same tab:
-- Query 2: Revenue per doctor
-- EXPECTED OUTCOME: 5 rows (not 6). Dr. Foster is excluded because
-- INNER JOIN drops her (she has zero appointments to match on).
SELECT
    d.name AS doctor_name,
    d.specialty,
    COUNT(a.appointment_id) AS total_appointments,
    SUM(a.cost) AS total_revenue
FROM doctors d
INNER JOIN appointments a ON d.doctor_id = a.doctor_id
GROUP BY d.doctor_id, d.name, d.specialty
ORDER BY total_revenue DESC;

What does this query do?

  • INNER JOIN appointments a ON d.doctor_id = a.doctor_id pairs each doctor with their appointments. Doctors without appointments get no pairing.
  • GROUP BY d.doctor_id, d.name, d.specialty collapses all of one doctor's appointments into a single summary row.
  • COUNT(a.appointment_id) counts how many appointments that doctor had. SUM(a.cost) adds up the total revenue they generated.

Before running this, predict: you have 6 doctors. How many rows will appear in the result?

  • Highlight only Query 2 (select it with your cursor) and run it by pressing Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows).

You should see exactly 5 rows, not 6. Dr. Amanda Foster is missing because she has zero appointments. INNER JOIN cannot produce a row for her because there is nothing to match on.

Why this matters in interviews

If someone asks "show revenue for ALL doctors including those with zero appointments," INNER JOIN is the wrong tool. You cannot see zeros with INNER JOIN because zeros mean no match, and no match means no row. You will solve this exact problem with LEFT JOIN in the next step.

  • Save this file as 02_inner_join.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Getting more or fewer than 5 rows?

  • If you see 10 rows, you may have run both queries together. Highlight only Query 2 before executing.
  • If you see 6 rows, check that you used INNER JOIN and not LEFT JOIN. LEFT JOIN would preserve Dr. Foster with NULL values.

Help me understand why my aggregation query returns unexpected rows.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file as 02_inner_join.sql.

ⓧ I'd like to double check the full code

Here is the complete 02_inner_join.sql file. Make sure your code matches this exactly:

USE hospital_joins;

-- SCENARIO: The hospital administrator needs a complete visit log.
-- WHY INNER JOIN: You only want rows where a patient actually had an appointment
-- with a doctor. No appointment = no row in the results.

-- Query 1: Show every appointment with patient name, doctor name, date, and diagnosis
-- EXPECTED OUTCOME: 10 rows. Every row is fully populated. No NULLs.
-- Patients Priya Sharma and Olivia Chen are MISSING (no appointments).
-- Dr. Amanda Foster is MISSING (no patients yet).
SELECT
    p.name AS patient_name,
    d.name AS doctor_name,
    a.appointment_date,
    a.diagnosis
FROM patients p
INNER JOIN appointments a ON p.patient_id = a.patient_id
INNER JOIN doctors d ON a.doctor_id = d.doctor_id
ORDER BY a.appointment_date;
-- Query 2: Revenue per doctor
-- EXPECTED OUTCOME: 5 rows (not 6). Dr. Foster is excluded because
-- INNER JOIN drops her (she has zero appointments to match on).
SELECT
    d.name AS doctor_name,
    d.specialty,
    COUNT(a.appointment_id) AS total_appointments,
    SUM(a.cost) AS total_revenue
FROM doctors d
INNER JOIN appointments a ON d.doctor_id = a.doctor_id
GROUP BY d.doctor_id, d.name, d.specialty
ORDER BY total_revenue DESC;

You have seen INNER JOIN in action and identified its blind spot: it silently drops any row without a match on both sides. Next up, you will use LEFT JOIN and RIGHT JOIN to keep those missing rows visible.

Write LEFT JOIN and RIGHT JOIN Queries

In the last step, you saw INNER JOIN silently drop Priya Sharma and Olivia Chen (no appointments) and Dr. Amanda Foster (no patients). That's a problem when you need a report that includes everyone.

LEFT JOIN and RIGHT JOIN solve this by preserving ALL rows from one side and filling the other side with NULL when there is no match. LEFT JOIN alone accounts for roughly 30% of interview join questions.

In this step, get ready to:
  • Use the anti-join pattern (LEFT JOIN + WHERE IS NULL) to find patients who never visited.
  • Generate a revenue report for ALL patients using LEFT JOIN with COALESCE.
  • Use RIGHT JOIN to find doctors with no patients.
Find patients who never visited (designed obstacle)

The hospital administrator asks: "Which patients have NEVER visited the hospital?" Your instinct might be to use INNER JOIN with a NULL check. Let's try that first.

  • In MySQL Workbench, open a new SQL tab by clicking the new tab icon (or pressing Cmd+T on macOS or Ctrl+T on Windows).
  • Start your file with the header comments and your first INNER JOIN attempt by typing this code:
USE hospital_joins;

-- ============================================================
-- LEFT JOIN: Preserves ALL rows from the LEFT table.
-- Right side fills with NULL when there is no match.
-- ============================================================

-- SCENARIO: Find patients who have NEVER visited the hospital.
-- WHY LEFT JOIN: INNER JOIN drops non-matching rows. LEFT JOIN keeps them.
-- The anti-join pattern (LEFT JOIN + WHERE IS NULL) finds the "missing" rows.

-- DESIGNED OBSTACLE: Try with INNER JOIN first - returns ZERO rows
SELECT p.name
FROM patients p
INNER JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

What does this query attempt?

This query joins patients to appointments (INNER JOIN), then tries to filter for rows where appointment_id IS NULL. The logic seems sound: "show me patients whose appointment is NULL."

Before you run this, predict: how many rows do you think you'll get back?

  • Select the INNER JOIN query (lines starting from SELECT p.name to the semicolon) and click the lightning bolt icon to execute it.

You get ZERO rows. The result grid is completely empty.

Why did this return zero rows?

INNER JOIN already excluded patients with no appointments BEFORE the WHERE clause ran. Priya Sharma and Olivia Chen were dropped at the join step, so the WHERE filter has nothing to find.

This is exactly why INNER JOIN cannot solve "find rows with no match" problems. You need a join that preserves unmatched rows first.

Now write the correct solution using the anti-join pattern.

  • Add the LEFT JOIN anti-join query below your INNER JOIN attempt:
-- CORRECT: Anti-join pattern
-- EXPECTED OUTCOME: 2 rows - Priya Sharma and Olivia Chen
SELECT
    p.name,
    p.blood_type
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

What does the anti-join pattern do?

  • LEFT JOIN keeps ALL 8 patients. Those with no appointments get NULL in the right-side columns.
  • WHERE a.appointment_id IS NULL filters to ONLY the NULL-padded rows (patients who had no match).
  • The result: only patients who never visited. This pattern appears in nearly every SQL interview.
  • Select the LEFT JOIN query and execute it.

You should see exactly 2 rows: Priya Sharma and Olivia Chen. These are the patients INNER JOIN silently dropped in the previous step.

Seeing zero rows or more than 2?

  • If you still see zero rows, make sure you changed INNER JOIN to LEFT JOIN in the new query. The keyword matters.
  • If you see more than 2 rows, check that your WHERE clause uses a.appointment_id IS NULL (not p.patient_id IS NULL).
  • Make sure you highlighted ONLY the LEFT JOIN query before clicking execute. Running both queries at once shows the last result.

Why is my LEFT JOIN anti-join query not returning 2 rows?

Generate a revenue report for all patients

The finance team needs a spending report for ALL patients, including those who spent $0. Without COALESCE, patients with no appointments would show NULL instead of a clean zero.

  • Add the revenue report query below your anti-join query in 03_left_right_join.sql:
-- SCENARIO: Revenue report for ALL patients, including $0 for non-visitors.
-- WHY LEFT JOIN + COALESCE: We need every patient in the report.
-- EXPECTED OUTCOME: 8 rows. Priya and Olivia show 0 visits, $0.00 spent.
SELECT
    p.name AS patient_name,
    COUNT(a.appointment_id) AS total_visits,
    COALESCE(SUM(a.cost), 0) AS total_spent
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
GROUP BY p.patient_id, p.name
ORDER BY total_spent DESC;

What does COALESCE do here?

  • LEFT JOIN keeps all 8 patients. Those with no appointments get NULL in the cost column.
  • SUM(a.cost) on NULL values produces NULL, not 0.
  • COALESCE(SUM(a.cost), 0) says "if the sum is NULL, use 0 instead." This gives clean numeric output for reports.
  • COUNT(a.appointment_id) correctly returns 0 for non-visitors because COUNT skips NULLs.
  • Select the revenue report query and execute it.

You should see 8 rows. Scroll to the bottom of the results. Priya Sharma and Olivia Chen appear with 0 visits and $0.00 spent.

Seeing only 6 rows?

If you only see 6 rows, you likely used INNER JOIN instead of LEFT JOIN. INNER JOIN drops the two patients who have no appointments. Change it to LEFT JOIN and re-run.

Why is my revenue report LEFT JOIN query returning 6 rows instead of 8?

Use RIGHT JOIN to find doctors with no patients

RIGHT JOIN is the mirror of LEFT JOIN. It preserves ALL rows from the RIGHT table and fills the left side with NULL when there is no match.

  • Add the RIGHT JOIN section below your revenue report query:
-- ============================================================
-- RIGHT JOIN: Preserves ALL rows from the RIGHT table.
-- Left side fills with NULL when there is no match.
-- It is the mirror image of LEFT JOIN.
-- ============================================================

-- SCENARIO: Show all doctors and their patients, including doctors with no patients.
-- WHY RIGHT JOIN: We want ALL doctors preserved (right table), even if they have
-- no matching appointments/patients on the left.
-- EXPECTED OUTCOME: 11 rows. Dr. Amanda Foster appears with NULL patient info.
SELECT
    p.name AS patient_name,
    a.diagnosis,
    d.name AS doctor_name,
    d.specialty
FROM patients p
INNER JOIN appointments a ON p.patient_id = a.patient_id
RIGHT JOIN doctors d ON a.doctor_id = d.doctor_id
ORDER BY d.name;

What does this RIGHT JOIN do?

  • The right-most table (doctors) is preserved. Every doctor appears even if they have no matching rows on the left.
  • Dr. Amanda Foster has zero appointments, so her patient_name and diagnosis columns fill with NULL.
  • You get 11 rows: 10 matched appointment rows plus 1 NULL-padded row for Dr. Foster.

Why not always use LEFT JOIN instead?

MySQL's official documentation recommends using LEFT JOIN instead of RIGHT JOIN for portability across databases. RIGHT JOIN is just LEFT JOIN with the table order reversed. However, interviewers test whether you understand both and can convert between them.

Before you run this, predict: how many rows will you see? Remember there are 10 appointments and 6 doctors.

  • Select the RIGHT JOIN query and execute it.

You should see 11 rows. Find Dr. Amanda Foster in the results. Her patient_name and diagnosis columns show NULL because she has no appointments yet.

  • Save your file as 03_left_right_join.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Seeing 10 rows instead of 11?

If Dr. Foster is missing from your results, check that you wrote RIGHT JOIN doctors d and not INNER JOIN doctors d. INNER JOIN drops her because she has no matching appointments.

Why is my RIGHT JOIN query not showing Dr. Amanda Foster?

Verify your LEFT JOIN and RIGHT JOIN results

Let's confirm all three queries work correctly by running them one more time.

  • Select and run the anti-join query (the LEFT JOIN with WHERE a.appointment_id IS NULL). Confirm you get exactly 2 rows: Priya Sharma and Olivia Chen.
  • Select and run the RIGHT JOIN query. Confirm Dr. Amanda Foster appears with NULL for patient info, and you see 11 total rows.

✔️ Awesome, I've got everything!

Great. Make sure you've saved 03_left_right_join.sql.

ⓧ I'd like to double check the full code

Scroll through your 03_left_right_join.sql file and confirm these sections appear in order:

  • USE hospital_joins; and the LEFT JOIN header comments.
  • The designed obstacle INNER JOIN query (returns 0 rows).
  • The anti-join pattern query (LEFT JOIN ... WHERE a.appointment_id IS NULL).
  • The revenue report query (LEFT JOIN with COALESCE and GROUP BY).
  • The RIGHT JOIN section header comments.
  • The RIGHT JOIN query showing all doctors and their patients.

You now have two powerful patterns in your toolkit. The anti-join pattern finds "missing" data (patients who never visited), and COALESCE converts NULLs to zeros for clean reports. Next up, you'll query hierarchical data by joining a table to itself.

Write SELF JOIN Queries

You have used INNER JOIN to match rows across different tables, LEFT JOIN to preserve unmatched rows, and RIGHT JOIN to mirror that behavior. Now you face a different problem: the doctors table has a supervisor_id column that points back to another doctor_id in the same table.

Querying hierarchical data like employee-manager or doctor-supervisor relationships requires joining a table to itself. Self-joins appear in ~7% of interview questions but trip up candidates who have never seen the pattern.

In this step, get ready to:
  • Write a LEFT JOIN self-join to build an org chart of doctors and their supervisors.
  • Write an INNER JOIN self-join to find cross-specialty mentorship relationships.
  • Articulate the self-join alias pattern for interview use.
Write the org chart self-join query

HR needs an org chart showing each doctor alongside their supervisor's name. The supervisor_id column in the doctors table references doctor_id in the same table. You need two "copies" of the table: one for the doctor, one for the supervisor.

  • Open a new SQL tab in MySQL Workbench.
  • Add the following code to create your self-join org chart query:
USE hospital_joins;

-- ============================================================
-- SELF JOIN: A table joined to ITSELF using different aliases.
-- Used for hierarchical data (employee-manager, doctor-supervisor).
-- ============================================================

-- SCENARIO: HR needs an org chart showing each doctor and their supervisor.
-- WHY SELF JOIN: The supervisor_id column references doctor_id in the SAME table.
-- We need two "copies" of the table: one for the doctor, one for the supervisor.
-- WHY LEFT JOIN: Department heads have NULL supervisor_id. INNER JOIN would drop them.

-- EXPECTED OUTCOME: 6 rows.
-- Dr. Wilson and Dr. Carter show NULL supervisor (they are department heads).
-- The other four doctors show their supervisor's name.
SELECT
    d.name AS doctor_name,
    d.specialty,
    s.name AS supervisor_name
FROM doctors d
LEFT JOIN doctors s ON d.supervisor_id = s.doctor_id;

What does this code do?

The key trick is the two aliases. d represents the doctor row, and s represents the supervisor row. Both point at the same doctors table.

  • d.supervisor_id = s.doctor_id connects each doctor to their supervisor's row within the same table.
  • LEFT JOIN is critical here. Dr. Wilson and Dr. Carter have NULL in supervisor_id (they are department heads). INNER JOIN would silently drop them.

Before you run this, how many rows do you expect to see? Think about it: there are 6 doctors, and each doctor gets a row regardless of whether they have a supervisor.

  • Select all the code and click the lightning bolt icon to execute it.

You should see 6 rows. Dr. Sarah Wilson and Dr. James Carter show NULL for supervisor_name (they are department heads). The other four doctors show their supervisor's name.

Seeing fewer than 6 rows?

  • If you see 4 rows, you may have typed INNER JOIN instead of LEFT JOIN. INNER JOIN drops the two department heads who have no supervisor.
  • Make sure the ON clause reads d.supervisor_id = s.doctor_id. Reversing the direction (d.doctor_id = s.supervisor_id) produces incorrect results.

Why is my self-join returning the wrong number of rows?

Write the cross-specialty comparison query

The hospital wants to identify cross-specialty mentorship relationships. This means finding doctors whose supervisor is in a different specialty. Self-joins let you compare any column across different rows in the same table.

  • Add this query below your first one in the same file:
-- SCENARIO: Find cross-specialty mentorship (doctor and supervisor in different specialties).
-- WHY INNER JOIN here: We only want doctors who HAVE a supervisor (exclude heads).
-- EXPECTED OUTCOME: Dr. Lisa Patel (Orthopedics) supervised by Dr. Sarah Wilson (Cardiology),
-- and Dr. Amanda Foster (Dermatology) supervised by Dr. James Carter (Neurology).
SELECT
    d.name AS doctor_name,
    d.specialty AS doctor_specialty,
    s.name AS supervisor_name,
    s.specialty AS supervisor_specialty
FROM doctors d
INNER JOIN doctors s ON d.supervisor_id = s.doctor_id
WHERE d.specialty != s.specialty;

Why INNER JOIN instead of LEFT JOIN this time?

You only want doctors who HAVE a supervisor. Department heads have no supervisor to compare specialties with, so including them would be meaningless. INNER JOIN naturally excludes them because their supervisor_id is NULL and cannot match any doctor_id.

The WHERE d.specialty != s.specialty filter then keeps only pairs where the doctor and supervisor are in different fields.

Before you run this, predict: how many of the 4 supervised doctors have a supervisor in a different specialty?

  • Select only the second query and execute it.

You should see 2 rows: Dr. Lisa Patel (Orthopedics) supervised by Dr. Sarah Wilson (Cardiology), and Dr. Amanda Foster (Dermatology) supervised by Dr. James Carter (Neurology).

Why not Dr. Emily Chen or Dr. Robert Kim?

Dr. Emily Chen is in Cardiology and her supervisor Dr. Sarah Wilson is also in Cardiology. Dr. Robert Kim is in Neurology and his supervisor Dr. James Carter is also in Neurology. Same specialty on both sides, so the != filter excludes them.

  • Save this file as 04_self_join.sql.

Getting 0 rows from the cross-specialty query?

  • Check that you used != (not equals) and not = in the WHERE clause. Using = would find same-specialty pairs instead.
  • Make sure the ON clause still uses d.supervisor_id = s.doctor_id. If you accidentally wrote d.doctor_id = s.supervisor_id, you are looking up subordinates instead of supervisors.

Help me debug my self-join cross-specialty query.

Understand the self-join pattern

When do I use a self-join?

A self-join always needs two aliases for the same table (like d and s, or employee and manager). The join condition connects the foreign key to the primary key within the same table.

Use LEFT JOIN when you need to keep root nodes (rows with a NULL parent, like department heads). Use INNER JOIN when you only want rows that have a parent. The choice between LEFT and INNER is what determines whether top-level items appear in your results.

✔️ Awesome, I've got everything!

Great. Double check you have saved 04_self_join.sql.

ⓧ I'd like to double check the full code

Your 04_self_join.sql file should contain these two sections in order:

  • USE hospital_joins; at the top, followed by the section header comments about SELF JOIN.
  • The org chart query: a SELECT with LEFT JOIN doctors s ON d.supervisor_id = s.doctor_id that returns 6 rows.
  • The cross-specialty query: a SELECT with INNER JOIN doctors s ON d.supervisor_id = s.doctor_id and WHERE d.specialty != s.specialty that returns 2 rows.

Scroll up and confirm each chunk matches what you wrote.

You can now query hierarchical data within a single table. Next up, you will generate every possible combination with CROSS JOIN and tackle the MySQL-specific FULL OUTER JOIN workaround.

Write CROSS JOIN and FULL OUTER JOIN Queries

You have now used INNER JOIN to match rows, LEFT/RIGHT JOIN to preserve one side, and SELF JOIN to query a hierarchy. Two patterns remain that complete your join toolkit.

CROSS JOIN generates every possible combination between two tables with no matching condition. FULL OUTER JOIN preserves unmatched rows from BOTH sides. MySQL does not support it natively, so the workaround itself is a direct interview question.

In this step, get ready to:
  • Generate a full doctor-shift schedule grid using CROSS JOIN.
  • Emulate FULL OUTER JOIN to reconcile scheduled vs actual visits.
  • Compare row counts across all four join types to see the pattern.
Generate a shift coverage grid with CROSS JOIN

The hospital scheduler needs to see every possible doctor-shift slot before assigning coverage. That means pairing each of the 6 doctors with each of the 3 shifts. CROSS JOIN produces this Cartesian product with no ON clause.

  • In MySQL Workbench, open a new SQL tab by clicking the New Query Tab button (the + icon above the query editor).
  • Write the CROSS JOIN query by pasting the following code:
USE hospital_joins;

-- ============================================================
-- CROSS JOIN: Cartesian product. Every row from table A paired
-- with every row from table B. NO ON clause.
-- ============================================================

-- SCENARIO: Generate a shift coverage grid for the scheduler.
-- WHY CROSS JOIN: We need ALL possible doctor-shift combinations before
-- assigning actual coverage. This is a grid/scaffold generation pattern.
-- EXPECTED OUTCOME: 18 rows (6 doctors x 3 shifts). Every doctor appears
-- with Morning, Afternoon, and Night.
SELECT
    d.name AS doctor_name,
    d.specialty,
    s.shift_name
FROM doctors d
CROSS JOIN shifts s
ORDER BY d.name, s.shift_id;

What does this query do?

CROSS JOIN has no ON clause. It pairs every row from doctors with every row from shifts. With 6 doctors and 3 shifts, you get 6 x 3 = 18 rows.

This is intentional when you NEED all combinations (schedules, date spines, grids). It becomes dangerous accidentally. Missing an ON clause on a regular join creates a silent Cartesian product that can produce millions of rows.

Before you run this, how many rows do you expect to see?

  • Click the lightning bolt icon (or press Cmd+Shift+Enter on macOS or Ctrl+Shift+Enter on Windows) to execute all statements.

You should see 18 rows in the result grid. Every doctor appears three times, once for Morning, once for Afternoon, once for Night.

Not seeing 18 rows?

  • Make sure your shifts table has 3 rows. Run SELECT COUNT(*) FROM shifts; to confirm.
  • If you accidentally added an ON clause, remove it. CROSS JOIN should have no join condition.
  • Why is my CROSS JOIN returning fewer rows than expected?
Emulate FULL OUTER JOIN with UNION

FULL OUTER JOIN keeps unmatched rows from BOTH sides. LEFT JOIN keeps all left rows. RIGHT JOIN keeps all right rows. FULL OUTER JOIN keeps everything from both.

MySQL does not support FULL OUTER JOIN natively. The standard workaround combines a LEFT JOIN with a RIGHT JOIN using UNION. This workaround itself is a common interview question.

Why doesn't MySQL support FULL OUTER JOIN?

The MySQL documentation confirms that only INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN are supported. FULL OUTER JOIN was never implemented. The UNION workaround achieves the same result by combining what LEFT JOIN captures (all left-side rows) with what RIGHT JOIN captures (all right-side rows), then deduplicating with UNION.

  • Create two temporary tables with overlapping and non-overlapping data by adding this code below your CROSS JOIN query:
-- ============================================================
-- FULL OUTER JOIN (emulated): Keeps unmatched rows from BOTH sides.
-- MySQL does NOT support FULL OUTER JOIN natively.
-- Workaround: UNION of LEFT JOIN + RIGHT JOIN.
-- ============================================================

-- SCENARIO: Reconcile scheduled appointments vs actual walk-in visits.
-- Some patients were scheduled but didn't show (no-shows).
-- Some patients walked in without a prior appointment (walk-ins).
-- Some patients both scheduled and showed up (matched).
-- WHY FULL OUTER JOIN: We need to see ALL records from BOTH sources,
-- with NULLs filling in where one side has no match.

-- Create temporary tables for the reconciliation demo
CREATE TEMPORARY TABLE scheduled_visits (
    visit_id INT,
    patient_name VARCHAR(100),
    visit_date DATE
);

CREATE TEMPORARY TABLE actual_visits (
    visit_id INT,
    patient_name VARCHAR(100),
    visit_date DATE
);

What are these temporary tables?

These simulate two data sources the hospital would reconcile. scheduled_visits holds who was booked to come in. actual_visits holds who actually showed up. Some overlap (John Smith), some don't.

  • Populate both tables with sample data by adding these INSERT statements:
INSERT INTO scheduled_visits VALUES
(1, 'John Smith', '2026-07-01'),
(2, 'Maria Garcia', '2026-07-01'),
(3, 'David Johnson', '2026-07-02');

INSERT INTO actual_visits VALUES
(1, 'John Smith', '2026-07-01'),
(4, 'Emma Wilson', '2026-07-01'),
(5, 'Sarah Lee', '2026-07-02');

What's the overlap?

  • John Smith (visit_id 1) appears in BOTH tables. He was scheduled and showed up.
  • Maria Garcia and David Johnson are ONLY in scheduled_visits. They are no-shows.
  • Emma Wilson and Sarah Lee are ONLY in actual_visits. They are walk-ins.
  • Select all the code you have added so far (from the CREATE TEMPORARY TABLE statements through the INSERT statements) and execute it to create and populate the tables.

You should see success messages for each statement in the output panel. No result grid appears because these are DDL/DML statements, not SELECT queries.

  • Now write the FULL OUTER JOIN emulation by adding this UNION query below your INSERT statements:
-- FULL OUTER JOIN emulation: UNION of LEFT JOIN + RIGHT JOIN
-- EXPECTED OUTCOME: 5 rows total
-- Row 1: John Smith / John Smith (matched - scheduled AND showed up)
-- Row 2: Maria Garcia / NULL (no-show - scheduled but didn't come)
-- Row 3: David Johnson / NULL (no-show - scheduled but didn't come)
-- Row 4: NULL / Emma Wilson (walk-in - showed up without appointment)
-- Row 5: NULL / Sarah Lee (walk-in - showed up without appointment)
SELECT
    sv.patient_name AS scheduled_patient,
    av.patient_name AS actual_patient,
    COALESCE(sv.visit_date, av.visit_date) AS visit_date
FROM scheduled_visits sv
LEFT JOIN actual_visits av ON sv.visit_id = av.visit_id

UNION

SELECT
    sv.patient_name AS scheduled_patient,
    av.patient_name AS actual_patient,
    COALESCE(sv.visit_date, av.visit_date) AS visit_date
FROM scheduled_visits sv
RIGHT JOIN actual_visits av ON sv.visit_id = av.visit_id;

How does this UNION trick work?

  • The first SELECT uses LEFT JOIN: it keeps all scheduled patients, filling actual_patient with NULL for no-shows.
  • The second SELECT uses RIGHT JOIN: it keeps all actual visitors, filling scheduled_patient with NULL for walk-ins.
  • UNION combines both results and removes duplicate rows (John Smith appears in both LEFT and RIGHT results, but UNION deduplicates him to one row).
  • COALESCE picks the first non-NULL date from either side so every row has a date.

Before you run this, predict: how many total rows will you see?

  • Select only the UNION query (from the first SELECT down to the final semicolon) and execute it.

You should see 5 rows. John Smith appears once with both columns filled (matched). Maria Garcia and David Johnson have NULL in the actual_patient column (no-shows). Emma Wilson and Sarah Lee have NULL in the scheduled_patient column (walk-ins).

Seeing duplicate rows or wrong count?

  • If you see more than 5 rows, check that you used UNION (not UNION ALL). UNION ALL keeps duplicates.
  • If you get an error about the temporary table not existing, you may need to re-run the CREATE TEMPORARY TABLE and INSERT statements first. Temporary tables disappear when you close the connection.
  • My FULL OUTER JOIN emulation is returning unexpected results. Help me debug.
Compare row counts across all join types

You just saw the FULL OUTER JOIN emulation return 5 rows. To solidify the pattern, think about what each join type would return against these same two temporary tables.

  • Add this comparison comment at the bottom of your file:
-- COMPARISON: Run each join type separately to see the difference
-- INNER JOIN: 1 row (only John Smith matches)
-- LEFT JOIN: 3 rows (all scheduled + matched)
-- RIGHT JOIN: 3 rows (all actual + matched)
-- FULL OUTER (UNION): 5 rows (everything from both sides, deduplicated)

Why these numbers?

  • INNER JOIN: only visit_id 1 (John Smith) exists in both tables. Result: 1 row.
  • LEFT JOIN: all 3 scheduled patients kept, with NULLs for the 2 who didn't show. Result: 3 rows.
  • RIGHT JOIN: all 3 actual visitors kept, with NULLs for the 2 walk-ins. Result: 3 rows.
  • FULL OUTER (UNION): LEFT (3) + RIGHT (3) minus the 1 duplicate (John Smith). Result: 5 rows.

The one sentence to remember: FULL OUTER JOIN = LEFT JOIN results + RIGHT JOIN results, deduplicated.

  • Save your file as 05_cross_join_full_outer.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Your 05_cross_join_full_outer.sql file should contain these sections in order. Scroll up and confirm each one is present:

  • USE hospital_joins; at the top.
  • The CROSS JOIN query selecting from doctors d CROSS JOIN shifts s with ORDER BY.
  • Two CREATE TEMPORARY TABLE statements (scheduled_visits and actual_visits).
  • Two INSERT INTO statements populating both tables (3 rows each).
  • The UNION emulation query (LEFT JOIN UNION RIGHT JOIN).
  • The comparison comment at the bottom showing INNER=1, LEFT=3, RIGHT=3, UNION=5.

You now have every SQL join type in your toolkit. Next up, you will put this knowledge to the ultimate test in the Secret Mission.

Secret mission

The WHERE vs ON Trap

Moving a single filter condition from one clause to another can silently destroy your LEFT JOIN results. In this challenge, you will predict the output of two nearly identical queries and discover why this is the single most common interview trap for SQL joins.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally on your MySQL server, so there are no ongoing costs.

Resources you used:

  • The hospital_joins database on your local MySQL server (4 tables, sample data).
  • SQL script files saved locally (01_setup.sql through 06_secret_mission_where_vs_on.sql).

Keep everything running

No action needed. Choose this if you want to keep practicing joins or revisiting your queries.

  • The hospital_joins database uses negligible disk space and is useful for continued practice.
  • Your SQL script files stay in whatever folder you saved them to.

Pause - I'll come back to this later

Since everything is local and uses minimal disk space, there is nothing to pause. The database sits idle on your MySQL server until you query it again.

  • You can close MySQL Workbench. The database persists on your MySQL server and will be there when you reconnect.

Delete - I don't want to use this again

Remove the database and local files to start fresh.

  • Drop the database by running this command in a MySQL Workbench SQL tab:
DROP DATABASE hospital_joins;
  • Confirm the database is gone by running:
SHOW DATABASES;

You should no longer see hospital_joins in the list.

  • Delete your local SQL script files. On macOS or Linux, navigate to the folder where you saved them and run:
rm 01_setup.sql 02_inner_join.sql 03_left_right_join.sql 04_self_join.sql 05_cross_join_full_outer.sql 06_secret_mission_where_vs_on.sql

On Windows, select all six SQL files in File Explorer and press the Delete key.

Nice Work!

Nice Work!

You just built a complete hospital database and answered progressively harder business questions using every major SQL join type. You can now look at any multi-table question and immediately identify which join solves it, write the query, and explain the NULL behavior.

You've learned how to:

  • Query a 4-table hospital database using INNER JOIN, LEFT JOIN, and RIGHT JOIN to answer real business questions about patient visits, find patients who never visited (the anti-join pattern), and generate revenue reports that include zeros using COALESCE.
  • Use SELF JOIN to navigate a doctor-supervisor hierarchy from a single table and CROSS JOIN to generate a complete doctor-shift scheduling grid.
  • Emulate FULL OUTER JOIN in MySQL using the UNION workaround to reconcile two data sources and see gaps on both sides.
  • Secret Mission: exposed the WHERE vs ON trap and can explain why moving a filter from the ON clause to WHERE silently converts a LEFT JOIN into an INNER JOIN.

Ready to quiz yourself?