SQL Join Shortcuts with a Hospital DB

Master table aliases, USING, and NATURAL JOIN on a hospital database.

Introduction

30 Second Summary

Think about a hospital. Patient names live in one place, doctor schedules in another, and prescriptions in yet another. To answer a question like "which doctor prescribed what to whom," you need to pull information from multiple sources at once.

In this project, you will load a hospital database into MySQL Workbench and write progressively cleaner SQL queries that join multiple tables together. You will start with verbose, repetitive syntax, then discover shortcuts that make the same queries shorter and easier to read.

What You'll Build

You will run a single readable query that pulls a full appointment report across four connected tables, using shortcut syntax that keeps your SQL concise and clear.

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

  • Queries that join two tables to show which doctors work in which departments, using table aliases and the USING clause for clean, readable output.
  • A three-table join that pulls full appointment details (patient name, doctor name, diagnosis) using shortcut syntax you can read at a glance.
  • Confidence to choose between ON, USING, and NATURAL JOIN based on your column names and context.
  • Secret Mission: Write a hospital department report joining four tables with aggregation and all shortcut techniques combined.

Are there any prerequisites?

You should be comfortable writing basic SELECT and WHERE queries in SQL. You also need MySQL 8.0 and MySQL Workbench already installed on your machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to load a hospital database into MySQL Workbench, study how its tables connect, then write increasingly clean join queries that answer real hospital questions.

Load the Hospital Database

To practice join queries, you need a database with multiple related tables and realistic data already in place.

In this step, you will load a pre-built hospital database with 6 tables (departments, doctors, patients, appointments, medications, and prescriptions) into your local MySQL server. This gives you a rich set of relationships to query without spending time inserting data by hand.

In this step, get ready to:
  • Create the hospital database with 6 related tables.
  • Populate the tables with sample hospital data.
  • Set the hospital schema as your default and verify the data loaded.
Create the database and table structure

The hospital database has 6 tables connected by foreign keys. You will paste the structure into a new query tab and execute it.

  • In MySQL Workbench, open a new query tab by clicking File in the menu bar, then New Query Tab.
  • Paste the following SQL into the empty editor. This creates the hospital database and defines the first three tables (departments, doctors, patients):
-- Hospital Database Setup Script
DROP DATABASE IF EXISTS hospital;
CREATE DATABASE hospital;
USE hospital;

-- Departments
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    floor_number INT NOT NULL
);

-- Doctors
CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Patients
CREATE TABLE patients (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender CHAR(1) NOT NULL,
    phone VARCHAR(15)
);

What does this code do?

DROP DATABASE IF EXISTS hospital removes any old copy of the database so you start fresh.

Each CREATE TABLE statement defines a table's columns and data types. The FOREIGN KEY lines tell MySQL how tables relate to each other (e.g. each doctor belongs to one department).

  • Place your cursor at the end of the code you just pasted, press Enter to start a new line, then paste the remaining three tables below:
-- Appointments
CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    diagnosis VARCHAR(200),
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Medications
CREATE TABLE medications (
    medication_id INT PRIMARY KEY AUTO_INCREMENT,
    medication_name VARCHAR(100) NOT NULL,
    dosage_form VARCHAR(50) NOT NULL,
    manufacturer VARCHAR(100) NOT NULL
);

-- Prescriptions
CREATE TABLE prescriptions (
    prescription_id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_id INT NOT NULL,
    medication_id INT NOT NULL,
    dosage VARCHAR(50) NOT NULL,
    duration_days INT NOT NULL,
    FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id),
    FOREIGN KEY (medication_id) REFERENCES medications(medication_id)
);

What do these tables store?

appointments links a patient to a doctor on a specific date with a diagnosis. medications lists available drugs. prescriptions ties a medication to an appointment, recording dosage and duration.

  • Execute the entire script by pressing Cmd+Shift+Return (macOS) or Ctrl+Shift+Enter (Windows).
  • In the Navigator panel on the left, click the refresh icon (two curved arrows) next to SCHEMAS. You should see hospital appear in the schema list.
  • Expand the hospital schema, then expand Tables. You should see all 6 tables listed: appointments, departments, doctors, medications, patients, prescriptions.

Don't see the hospital schema?

  • Make sure you pasted both code blocks into the same query tab, with the second block directly below the first.
  • Check the Output panel at the bottom for red error messages. A common cause is a typo introduced while pasting.
  • If you see an error about the database already existing, that is fine. The DROP DATABASE IF EXISTS line handles this automatically.

Still stuck? Help me troubleshoot why my hospital database tables did not appear in MySQL Workbench.

Populate the tables with sample data

The table structure is ready. Now you will insert the sample records that give you realistic data to query.

  • Select all the code currently in your query tab and delete it (Cmd+A then Delete on macOS, Ctrl+A then Delete on Windows).
  • Paste the following INSERT statements to add departments, doctors, and patients:
USE hospital;

-- Insert Departments
INSERT INTO departments (department_name, floor_number) VALUES
('Cardiology', 3),
('Neurology', 4),
('Pediatrics', 2),
('Orthopedics', 5),
('General Medicine', 1);

-- Insert Doctors
INSERT INTO doctors (first_name, last_name, specialty, department_id) VALUES
('Sarah', 'Chen', 'Interventional Cardiology', 1),
('James', 'Wilson', 'Electrophysiology', 1),
('Maria', 'Garcia', 'Neurological Surgery', 2),
('David', 'Kim', 'Stroke Medicine', 2),
('Emily', 'Brown', 'Neonatal Care', 3),
('Robert', 'Taylor', 'Sports Medicine', 4),
('Lisa', 'Anderson', 'Joint Replacement', 4),
('Michael', 'Patel', 'Internal Medicine', 5);
  • Execute this block by pressing Cmd+Shift+Return (macOS) or Ctrl+Shift+Enter (Windows).
  • Check the Output panel at the bottom. You should see green checkmarks confirming 5 rows inserted into departments and 8 rows into doctors.
  • Clear the editor again (Cmd+A then Delete), then paste the patient and appointment data:
USE hospital;

-- Insert Patients
INSERT INTO patients (first_name, last_name, date_of_birth, gender, phone) VALUES
('John', 'Smith', '1985-03-15', 'M', '555-0101'),
('Emma', 'Johnson', '1992-07-22', 'F', '555-0102'),
('William', 'Davis', '1978-11-08', 'M', '555-0103'),
('Olivia', 'Martinez', '2001-01-30', 'F', '555-0104'),
('Noah', 'Thompson', '1965-09-12', 'M', '555-0105'),
('Sophia', 'White', '1988-05-25', 'F', '555-0106'),
('Liam', 'Harris', '2015-12-03', 'M', '555-0107'),
('Ava', 'Clark', '1972-08-17', 'F', '555-0108');

-- Insert Appointments
INSERT INTO appointments (patient_id, doctor_id, appointment_date, diagnosis) VALUES
(1, 1, '2026-07-01', 'Atrial fibrillation'),
(2, 3, '2026-07-02', 'Migraine with aura'),
(3, 6, '2026-07-03', 'ACL tear'),
(4, 5, '2026-07-05', 'Bronchiolitis'),
(5, 1, '2026-07-06', 'Hypertension'),
(6, 4, '2026-07-08', 'Transient ischemic attack'),
(7, 5, '2026-07-10', 'Ear infection'),
(8, 8, '2026-07-11', 'Type 2 diabetes'),
(1, 2, '2026-07-15', 'Heart palpitations'),
(3, 7, '2026-07-18', 'Knee osteoarthritis'),
(5, 8, '2026-07-20', 'Annual checkup'),
(6, 3, '2026-07-22', 'Epilepsy follow-up');
  • Execute this block. The Output panel should show 8 rows inserted into patients and 12 rows into appointments.
  • Clear the editor one more time, then paste the medications and prescriptions data:
USE hospital;

-- Insert Medications
INSERT INTO medications (medication_name, dosage_form, manufacturer) VALUES
('Metoprolol', 'Tablet', 'AstraZeneca'),
('Sumatriptan', 'Tablet', 'GlaxoSmithKline'),
('Ibuprofen', 'Tablet', 'Pfizer'),
('Amoxicillin', 'Suspension', 'Teva'),
('Lisinopril', 'Tablet', 'Merck'),
('Clopidogrel', 'Tablet', 'Sanofi'),
('Metformin', 'Tablet', 'Bristol-Myers Squibb'),
('Levetiracetam', 'Tablet', 'UCB');

-- Insert Prescriptions
INSERT INTO prescriptions (appointment_id, medication_id, dosage, duration_days) VALUES
(1, 1, '50mg twice daily', 30),
(1, 6, '75mg once daily', 90),
(2, 2, '100mg as needed', 14),
(3, 3, '400mg three times daily', 10),
(4, 4, '250mg twice daily', 7),
(5, 5, '10mg once daily', 60),
(5, 1, '25mg once daily', 60),
(6, 6, '75mg once daily', 90),
(7, 4, '125mg twice daily', 5),
(8, 7, '500mg twice daily', 90),
(9, 1, '50mg once daily', 30),
(10, 3, '600mg twice daily', 14),
(12, 8, '500mg twice daily', 180);
  • Execute this block. You should see 8 rows inserted into medications and 13 rows into prescriptions.

What data did you just load?

You now have a small hospital with 5 departments, 8 doctors, 8 patients, 12 appointments, 8 medications, and 13 prescriptions. Every table connects to at least one other table through foreign keys. These connections are exactly what you will query using joins.

🙋‍♀️ Seeing a foreign key constraint error?

This means you ran the INSERT blocks out of order. Departments and doctors must be inserted before appointments, because appointments references their IDs. Clear the editor, go back to the first INSERT block above, and run each in sequence.

Help me fix a foreign key constraint error when inserting hospital data.

Set the default schema and verify
  • In the Navigator panel on the left, right-click the hospital schema.
  • Select Set as Default Schema. The schema name becomes bold in the Navigator, meaning all future queries target this database automatically.

Why set a default schema?

Setting a default schema means you do not need to write USE hospital; at the top of every query. MySQL Workbench will route all statements to the hospital database automatically.

  • Clear the editor, then paste and run this quick check to confirm the patient data loaded:
SELECT * FROM patients;

The Result Grid shows 8 rows of patient records with names, dates of birth, and phone numbers.

Before you run the next query, do you think the appointments table has more or fewer rows than the patients table?

  • Clear the editor, then run the final verification query:
SELECT COUNT(*) FROM appointments;

The Result Grid shows a count of 12. Some patients have multiple appointments, which is why there are more appointment rows than patient rows.

✔️ Awesome, I've got everything!

Great. Your hospital database is loaded and ready for join queries.

ⓧ I'd like to double check the full code

Scroll up and confirm you executed all of the following blocks in order:

  • Block 1: DROP DATABASE + CREATE DATABASE + CREATE TABLE for departments, doctors, patients.
  • Block 2: CREATE TABLE for appointments, medications, prescriptions.
  • Block 3: INSERT INTO departments, doctors.
  • Block 4: INSERT INTO patients, appointments.
  • Block 5: INSERT INTO medications, prescriptions.

If any block was missed, paste and execute it now. The order matters because later tables reference earlier ones through foreign keys.

Your hospital database is loaded and set as the default schema. Next up, you will generate the ERD to see how all 6 tables connect visually, then write your first join query.

Read the ERD and Write Your First Join

Your hospital database is loaded and ready to query. Before writing any joins, you need to understand how the tables relate to each other.

An ERD (Entity-Relationship Diagram) is a visual map showing every table and the lines that connect them through foreign keys. Once you can see which columns link two tables, writing a join becomes straightforward. In this step, you will generate that map and write your first two-table join the fully explicit way.

In this step, get ready to:
  • Generate an ERD to visualize how the 6 hospital tables connect.
  • Write a two-table INNER JOIN using full verbose syntax with the ON clause.
Generate the ERD in MySQL Workbench

MySQL Workbench can reverse engineer your live database into a visual diagram. This shows you every table, its columns, and the foreign key lines connecting them.

  • In the top menu bar of MySQL Workbench, click Database.
  • Click Reverse Engineer.

What is the Reverse Engineer wizard?

This wizard reads the structure of your live database and generates an EER (Enhanced Entity-Relationship) diagram. It walks you through several steps: selecting a connection, choosing which schemas to include, and selecting which objects to import.

  • On the first page of the wizard, your existing connection to localhost should already be selected. Click Next.
  • Review the connection results to confirm there are no errors. Click Next.
  • On the schema selection page, check the box next to hospital. Uncheck any other schemas. Click Next.
  • Review the tasks the wizard performed. Click Next.
  • On the Select Objects page, leave all object types selected (tables are checked by default). Make sure Place imported objects on a diagram is also checked. Click Execute.
  • Wait for the import to complete, then click Next and Finish.

An EER Diagram tab opens showing all 6 hospital tables as boxes on a canvas. Lines between the boxes represent foreign key relationships.

What should I see in the ERD?

Look for the line connecting doctors to departments. That line means doctors.department_id references departments.department_id. Every foreign key in your schema appears as a connecting line on the diagram.

You should also see lines from appointments to both patients and doctors, and from prescriptions to both appointments and medications.

🙋‍♀️ Don't see a diagram?

If the wizard completed but no diagram appeared, make sure Place imported objects on a diagram was checked on the Select Objects page. You can re-run the wizard from Database > Reverse Engineer with that option enabled.

If you see an error about an existing schema with the same name, close the current model tab first, then try again.

help me generate an ERD from my hospital database in MySQL Workbench

Query the table relationships with SQL

The ERD gives you a visual map, but you can also ask MySQL directly for a text-based list of every foreign key connection. This confirms exactly which column in which table points to which column in another table.

  • Run the following query to list all foreign key relationships in your hospital database:
-- Show all foreign key relationships in the hospital database
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'hospital'
AND REFERENCED_TABLE_NAME IS NOT NULL;

What does this query do?

INFORMATION_SCHEMA.KEY_COLUMN_USAGE is a built-in MySQL table that stores metadata about every constraint in your databases. By filtering where REFERENCED_TABLE_NAME IS NOT NULL, you get only the foreign key constraints (not primary keys or unique keys).

Each row in the result tells you: this column in this table points to that column in that other table.

The Result Grid shows 5 rows. These are the 5 foreign key connections that hold your hospital database together:

  • doctors.department_id → departments.department_id
  • appointments.patient_id → patients.patient_id
  • appointments.doctor_id → doctors.doctor_id
  • prescriptions.appointment_id → appointments.appointment_id
  • prescriptions.medication_id → medications.medication_id

Every join you write in this project uses one of these 5 connections. The column on the left is the foreign key, and the column on the right is the primary key it references.

Write your first two-table join

The ERD shows that doctors connects to departments through department_id. You can use a join to combine rows from both tables and answer the question: which doctor works in which department?

  • Switch back to your SQL query tab in MySQL Workbench. If you closed it, open a new one by clicking File > New Query Tab.
  • Write the following query using full table names and the ON clause to join doctors with their departments:
-- Doctors with their department names (full table names, ON clause)
SELECT doctors.first_name, doctors.last_name, departments.department_name
FROM doctors
INNER JOIN departments ON doctors.department_id = departments.department_id;

What does this query do?

The FROM doctors line picks the starting table. INNER JOIN departments adds a second table to the query.

The ON doctors.department_id = departments.department_id clause tells MySQL how to match rows. It pairs each doctor with the department row that shares the same department_id value. Without this ON clause, MySQL would not know which department belongs to which doctor.

Before you run this, predict: how many rows do you expect? There are 8 doctors in the database, and each doctor belongs to exactly one department.

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

The Result Grid shows 8 rows. Each row pairs a doctor's name with their department name. For example, you see "Sarah" "Chen" alongside "Cardiology".

Why is the syntax so repetitive?

Notice how you had to type doctors. and departments. in front of every column name. With just two tables this is manageable. But imagine joining three or four tables this way. The repetition gets tedious fast. That is exactly the problem the shortcut techniques in later steps will solve.

🙋‍♀️ Seeing an error instead of results?

Make sure hospital is still your default schema. In the Navigator panel on the left, right-click hospital and select Set as Default Schema. Then run the query again.

Check for typos in column names. The join condition must reference department_id in both tables, spelled exactly.

help me fix my INNER JOIN query on the hospital database

✔️ Awesome, I've got everything!

Great. Make sure your query is saved in this tab for reference in the next step.

ⓧ I'd like to double check the full code

Here is the complete query you should have in your SQL tab:

-- Doctors with their department names (full table names, ON clause)
SELECT doctors.first_name, doctors.last_name, departments.department_name
FROM doctors
INNER JOIN departments ON doctors.department_id = departments.department_id;

You have your ERD as a reference map and a working two-table join under your belt. Next up, you will join three tables together. The verbose syntax is about to get painful.

Write a Multi-Table Join the Verbose Way

Your two-table join from the previous step connected doctors to departments. Real hospital questions often need data scattered across three or more tables at once.

In this step, you'll answer a common hospital question: "Show me each appointment with the patient's name, the doctor's name, and the diagnosis." This requires pulling columns from three different tables in a single query.

In this step, get ready to:
  • Write a three-table INNER JOIN using full verbose syntax.
  • See exactly what happens to column headers when multiple tables share the same column names.
Join three tables to show appointment details

You need to show the patient's full name, the doctor's full name, the appointment date, and the diagnosis for every appointment. The appointments table is the bridge. It holds patient_id and doctor_id foreign keys that connect to the other two tables.

  • In MySQL Workbench, switch back to your join_queries.sql tab.
  • Add the following query below your Step 2 query:
-- ============================================
-- STEP 3: Multi-Table Join (Verbose - Feel the Pain)
-- ============================================

-- Appointment details: patient name, doctor name, date, diagnosis
SELECT patients.first_name, patients.last_name,
       doctors.first_name, doctors.last_name,
       appointments.appointment_date, appointments.diagnosis
FROM appointments
INNER JOIN patients ON appointments.patient_id = patients.patient_id
INNER JOIN doctors ON appointments.doctor_id = doctors.doctor_id;

What does this query do?

  • FROM appointments starts with the appointments table as the base.
  • The first INNER JOIN connects each appointment to its patient using the shared patient_id column.
  • The second INNER JOIN connects each appointment to its doctor using doctor_id.
  • The SELECT line pulls first and last names from both the patients and doctors tables, plus the date and diagnosis from appointments.

Before you run this query, look at the SELECT line. You're selecting patients.first_name, patients.last_name, doctors.first_name, and doctors.last_name. What do you think the column headers will look like in the Result Grid?

  • Highlight only the SELECT query (not the comment lines above it).
  • Click the lightning bolt icon (or press Ctrl+Shift+Enter on Windows, Cmd+Shift+Enter on macOS) to execute the selected query.

The Result Grid shows 12 rows of appointment data. The query works. But look at the column headers: first_name, last_name, first_name, last_name.

Two columns are both called first_name and two are both called last_name. You cannot tell which belongs to the patient and which belongs to the doctor.

Why are the headers duplicated?

Both the patients and doctors tables have columns named first_name and last_name. MySQL uses the column name as the header, so when you select both, the header appears twice with no way to distinguish them.

On top of that, notice how many times you typed full table names like appointments. and patients. throughout the query. This repetition makes the query harder to read and slower to write. These are the two problems that the next step solves.

🙋‍♀️ Seeing 0 rows or an error?

  • Make sure you highlighted only the SELECT statement (from SELECT through the final ;) before executing. If you highlight the comment lines, MySQL tries to run them as SQL.
  • Check that hospital is still your default schema. Look in the Navigator panel on the left. The schema name should be bold.
  • Confirm the table names match exactly: appointments, patients, doctors. A typo in any table name produces an error.

Why is my three-table join returning an error or zero rows in MySQL Workbench?

✔️ Awesome, I've got everything!

Great. Make sure you've saved your join_queries.sql file with the new Step 3 query added.

ⓧ I'd like to double check the full code

Here is what your complete join_queries.sql file should look like at this point:

-- Join Queries Workbook
-- Run these queries one at a time in MySQL Workbench

USE hospital;

-- ============================================
-- STEP 2: First Join (Verbose Syntax)
-- ============================================

-- Doctors with their department names (full table names, ON clause)
SELECT doctors.first_name, doctors.last_name, departments.department_name
FROM doctors
INNER JOIN departments ON doctors.department_id = departments.department_id;

-- ============================================
-- STEP 3: Multi-Table Join (Verbose - Feel the Pain)
-- ============================================

-- Appointment details: patient name, doctor name, date, diagnosis
SELECT patients.first_name, patients.last_name,
       doctors.first_name, doctors.last_name,
       appointments.appointment_date, appointments.diagnosis
FROM appointments
INNER JOIN patients ON appointments.patient_id = patients.patient_id
INNER JOIN doctors ON appointments.doctor_id = doctors.doctor_id;

Your query returns the correct data, but the output is confusing and the query itself is cluttered. Next up, you'll use table aliases and column aliases to make both the query and its results crystal clear.

Clean Up with Table Aliases

Your three-table join from the last step returned the right data. But the result was frustrating to read. Two columns both called first_name and two called last_name make it impossible to tell patient from doctor at a glance.

Table aliases let you give short nicknames to tables so you type less. Column aliases let you rename output columns so headers are clear. In this step, you'll rewrite the same query so it's both shorter and easier to read.

In this step, get ready to:
  • Rewrite the three-table join using table aliases and column aliases.
  • Confirm the AS keyword is optional for aliases in MySQL.
Rewrite the query with table and column aliases

A table alias is a short nickname you declare right after the table name in your FROM or JOIN clause. Once declared, you use that nickname everywhere else in the query instead of the full table name.

A column alias uses the AS keyword to rename an output column. This solves the duplicate-header problem from Step 3.

  • In your join_queries.sql tab in MySQL Workbench, add a new section header and the aliased query below your Step 3 code by pasting this:
-- ============================================
-- STEP 4: Table Aliases and Column Aliases
-- ============================================

-- Same query with aliases (short and clear)
SELECT p.first_name AS patient_first, p.last_name AS patient_last,
       d.first_name AS doctor_first, d.last_name AS doctor_last,
       a.appointment_date, a.diagnosis
FROM appointments a
INNER JOIN patients p ON a.patient_id = p.patient_id
INNER JOIN doctors d ON a.doctor_id = d.doctor_id;

What does this query do?

  • appointments a declares a as a shortcut for appointments. From here on, a.patient_id means the same thing as appointments.patient_id.
  • p.first_name AS patient_first renames the output column from a generic first_name to a clear patient_first so you can tell it apart from the doctor's name.
  • The data and the joins are identical to Step 3. Only the readability changed.

Before you run this, what do you think will be different about the column headers compared to your Step 3 result?

  • Highlight just the SELECT statement (from SELECT down to the final ;) and click the lightning bolt icon to execute it.

The Result Grid shows the same 12 rows as Step 3. But now the column headers read patient_first, patient_last, doctor_first, doctor_last, appointment_date, and diagnosis. No more guessing which name belongs to whom.

Seeing an error or wrong column names?

  • Make sure you highlighted only the new query (not the Step 3 query above it). MySQL Workbench runs whatever is selected.
  • Check that the alias declarations come right after each table name with a space: appointments a not appointments, a.
  • Verify the AS keyword sits between the column reference and its alias: p.first_name AS patient_first.

Still stuck? Ask for help.

Try aliases without the AS keyword

MySQL allows you to drop the AS keyword entirely. Both tbl_name AS alias_name and tbl_name alias_name are valid syntax.

  • Add this shorter variation to the bottom of your join_queries.sql file:
-- Aliases without the AS keyword (also valid in MySQL)
SELECT p.first_name patient_first, d.first_name doctor_first
FROM appointments a
INNER JOIN patients p ON a.patient_id = p.patient_id
INNER JOIN doctors d ON a.doctor_id = d.doctor_id;

What changed here?

The AS keyword is gone. p.first_name patient_first does the same thing as p.first_name AS patient_first. Some teams prefer keeping AS for readability. Others drop it to save space. Both are correct.

  • Highlight the new query and execute it with the lightning bolt.

The Result Grid shows patient_first and doctor_first as column headers. The alias works the same way without AS.

Seeing unexpected results?

If you see first_name instead of patient_first in the header, make sure there is a space between the column reference and the alias name. Missing that space means MySQL treats it as a syntax error.

Need more help?

✔️ Awesome, I've got everything!

Great. Make sure you've saved join_queries.sql with both new queries.

ⓧ I'd like to double check the full code

Your join_queries.sql should now have these sections in order. Scroll up through your file and confirm each is present:

  • USE hospital; at the top.
  • STEP 2 section: the doctors-to-departments join using full table names.
  • STEP 3 section: the three-table join using full table names (patients, appointments, doctors).
  • STEP 4 section: the aliased three-table join with AS column aliases, followed by the shorter version without AS.

Aliases made your query shorter and your output clearer. But you're still writing ON a.patient_id = p.patient_id every time two tables share the same column name. Next up, you'll learn two more shortcuts that eliminate even that repetition.

Use USING and NATURAL JOIN

Your aliases made the three-table join shorter and the output clearer. But look at the ON clauses you are still writing. Every time you type ON a.patient_id = p.patient_id, you are repeating the column name twice because both tables call it the same thing.

MySQL offers two shortcuts for exactly this situation. The USING clause works when both tables share the same column name. NATURAL JOIN goes further and auto-detects all shared column names for you.

In this step, get ready to:
  • Replace the ON clause with USING for matching column names.
  • Try NATURAL JOIN to auto-detect join columns.
  • Combine USING with aliases in a multi-table join.
Replace ON with USING

In Step 2, you joined doctors to departments using ON doctors.department_id = departments.department_id. Both tables have a column called department_id. The USING clause lets you name that shared column once instead of writing it on both sides of the equals sign.

  • In your join_queries.sql file in MySQL Workbench, add a new section header and the following USING query below your Step 4 queries:
-- ============================================
-- STEP 5: USING and NATURAL JOIN
-- ============================================

-- USING clause (when column names match exactly)
SELECT d.first_name, d.last_name, dep.department_name
FROM doctors d
INNER JOIN departments dep USING (department_id);

What does this query do?

This is the same doctors-to-departments join from Step 2, but with two improvements.

  • Table aliases d and dep shorten the table references (from Step 4).
  • USING (department_id) replaces ON d.department_id = dep.department_id. MySQL knows to match the column by name in both tables.

Before you run this, what do you think the output will look like compared to Step 2's result?

  • Highlight just the USING query (not the comment header) and click the lightning bolt icon to execute it.

The Result Grid shows 8 rows of doctor names paired with department names. This is identical to Step 2's output. USING produces the same result as ON when the column names match exactly.

Seeing an error about unknown column?

Make sure the column name inside USING (department_id) is spelled exactly as it appears in both tables. Check your ERD if you are unsure.

Also confirm you have USE hospital; at the top of your file so queries run against the correct database.

Help me debug my USING clause query

Try NATURAL JOIN

USING still requires you to name the shared column. NATURAL JOIN takes it one step further by automatically detecting ALL columns with the same name in both tables and joining on them.

  • Add the following NATURAL JOIN query below your USING query:
-- NATURAL JOIN (auto-detects shared columns)
SELECT first_name, last_name, department_name
FROM doctors
NATURAL JOIN departments;

What does NATURAL JOIN do here?

MySQL scans both tables for columns with the same name. Here, department_id is the only shared column between doctors and departments, so MySQL joins on that automatically.

Notice you do not need table aliases here because there is no ambiguous column in the SELECT list. The department_name column only exists in departments.

  • Highlight the NATURAL JOIN query and execute it.

You get the same 8 rows again. Three different syntaxes, identical results.

Why is NATURAL JOIN risky?

NATURAL JOIN works here because department_id is the only shared column. But if someone later adds a column to departments that shares a name with a column in doctors (like last_update), the join would silently include that column in its matching logic and return wrong results.

USING is the safer shortcut for production code because you explicitly name which column to join on.

🙋‍♀️ Getting fewer rows than expected?

If NATURAL JOIN returns fewer rows than 8, it may be matching on an unexpected shared column. Compare the column names in both tables using your ERD to confirm only department_id is shared.

Help me understand why NATURAL JOIN is returning unexpected results

Combine USING with aliases in a three-table join

You have seen USING on a simple two-table join. Now apply it to the three-table appointment query from Step 4 to see how much cleaner it gets.

  • Add the following three-table USING query below your NATURAL JOIN query:
-- USING with a three-table join
SELECT p.first_name AS patient_first, p.last_name AS patient_last,
       d.first_name AS doctor_first, d.last_name AS doctor_last,
       a.appointment_date, a.diagnosis
FROM appointments a
INNER JOIN patients p USING (patient_id)
INNER JOIN doctors d USING (doctor_id);

What changed from the Step 4 version?

Compare the join lines. In Step 4, you wrote:

  • INNER JOIN patients p ON a.patient_id = p.patient_id

Now it is just:

  • INNER JOIN patients p USING (patient_id)

You keep the aliases for column clarity in the SELECT, but the join conditions are now half the length.

  • Highlight the three-table USING query and execute it.

You see 12 rows with clear column headers: patient_first, patient_last, doctor_first, doctor_last, appointment_date, and diagnosis. Identical to Step 4, but the query itself is shorter and more readable.

When should I use ON vs USING vs NATURAL JOIN?

  • ON: Always works. Use when column names differ between tables or you need complex conditions (like joining on two columns at once).
  • USING: Use when both tables have the exact same column name for the join key. Shorter, cleaner, and safe for production.
  • NATURAL JOIN: Auto-detects shared columns. Convenient for quick exploration, but avoid in production code because schema changes can silently break it.
  • Save your join_queries.sql file.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file with all three Step 5 queries.

ⓧ I'd like to double check the full code

Your join_queries.sql should now contain these sections in order. Scroll through your file and confirm each is present.

  • The Step 2 section with the verbose doctors-departments join.
  • The Step 3 section with the verbose three-table join.
  • The Step 4 section with alias queries.
  • The Step 5 section with the USING query, the NATURAL JOIN query, and the three-table USING query.

USING query returning different rows than expected?

Make sure you are using INNER JOIN (not LEFT JOIN or RIGHT JOIN). Also confirm the column name inside USING() matches exactly. Check patient_id and doctor_id in your ERD.

Help me fix my three-table USING query

You now have three ways to write the same join. ON for full control, USING for matching column names, and NATURAL JOIN for quick exploration. Next up, you will combine everything you have learned into a multi-table report that answers a real hospital question.

Secret mission

Hospital Revenue Report

You've mastered join shortcuts on two and three tables. Now combine all three techniques in a single four-table query that answers a real hospital operations question: which departments generate the most prescriptions?

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • The hospital database on your local MySQL 8.0 server (6 tables with sample data).

Keep everything running

No action needed. Choose this if you plan to keep practicing SQL with the hospital database.

  • The hospital database takes minimal disk space and costs nothing to keep.
  • You can use it to practice subqueries, window functions, CTEs, and other advanced SQL concepts.

Pause - I'll come back to this later

Stop the MySQL server process to free up memory. Your database files stay on disk so you can restart the server later.

  • On macOS, stop MySQL by opening System Preferences (or System Settings), selecting MySQL, and clicking Stop MySQL Server.
  • On Windows, open the Services app, find MySQL80, right-click it, and select Stop.
  • To resume later, restart the MySQL service and reopen MySQL Workbench.

Delete - I don't want to use this again

Remove the hospital database entirely. This frees disk space and cleans your MySQL server.

  • In MySQL Workbench, open a query tab and run this command to drop the database:
DROP DATABASE hospital;
  • Confirm it worked by checking the Navigator panel on the left. The hospital schema should no longer appear.
  • Close any open query tabs in MySQL Workbench that reference the hospital database.

Nice Work!

Nice Work!

You just built a complete SQL join query workbook against a hospital database, progressing from verbose syntax all the way to clean, readable shortcuts.

You've learned how to:

  • Read an ERD in MySQL Workbench and write multi-table joins using full verbose syntax, experiencing firsthand why repetitive table names and duplicate column headers become unreadable.
  • Use table aliases and column aliases to shorten queries and produce clear, unambiguous output headers.
  • Apply the USING clause to eliminate redundant ON conditions, and understand why NATURAL JOIN is convenient but risky in production code.
  • Combined all shortcut techniques (aliases, USING, GROUP BY, and aggregation) in a four-table hospital department report (Secret Mission).

Ready to quiz yourself?