Joins

What does “inner” signify that we want in a join result set?

Only successful matches

In general, why would you need to use a join?

Because you need to cross reference across different tables to get a full picture or conclusion about data

What is a join?

A way to combine rows from two or more tables based on a related column

That related column is almost always the foreign key relationship from your ERD.

What does ON do in a join?

Specifies which columns link the two tables together (what your matches are evaluated on).

What is a table alias in a join?

A nickname assigned to a table in FROM or JOIN.

What does INNER JOIN return?

Only rows where a match exists (if ON condition = true) in both tables. Unmatched rows from either side are excluded.

What does LEFT JOIN return?

All rows from the left table (conviently, left of the JOIN word), plus matching rows from the right table.

NULLs appear in right-side columns where no match exists.

What does RIGHT JOIN return?

All rows from the right (JOIN) table, plus matching rows from the left.

NULLs appear on the left where no match exists.

Pssst. This can just be written as a left join by switching around the FROM table (left of the JOIN word) with the JOIN table.

What does FULL JOIN return?

All rows from both tables regardless of matches. NULLs fill in on whichever side has no match.

What does a SELF JOIN do?

What gets used in conjunction with it to make it useful?

Joins a table to itself

Aliases for column names

Also: the same table is given two different aliases so it can be treated as two separate tables.

Which join is the most commonly used?

INNER JOIN

It covers the majority of real-world query needs.

Which join is rarely used and why?

RIGHT JOIN

Any right join can be rewritten as a left join by swapping the table order. Most developers avoid it to keep queries consistent and readable.

What is the mental puzzle piece order for writing any join query?

SELECT   -- what columns
FROM     -- main table + alias
JOIN     -- second table + alias
ON       -- alias.fk = alias.pk
WHERE    -- optional filter

Fill in the slots left to right. ON is always the child FK matching the parent PK.

How do you assign and use a table alias in a join?

Define it after the table name in FROM or JOIN, then use it to prefix every column reference.

FROM Prescription AS rx
INNER JOIN Physician AS ph ON rx.physician_id = ph.physician_id

How do you find rows that have no match, like physicians who have written no prescriptions?

SELECT ph.physician_name
FROM Physician AS ph
LEFT JOIN Prescription AS rx ON ph.physician_id = rx.physician_id
WHERE rx.prescription_id IS NULL;

How do you chain multiple joins together?

Add another JOIN ... ON block after the first. Each join adds a new table:

FROM Prescription AS rx
INNER JOIN Physician AS ph ON rx.physician_id = ph.physician_id
INNER JOIN Patient AS p ON rx.patient_id = p.patient_id

How do you write a self join, and what prevents a row from matching on itself?

Assign two different aliases to the same table, then exclude self-matches in WHERE

FROM Patient AS a
INNER JOIN Patient AS b ON a.room_id = b.room_id
WHERE a.patient_id <> b.patient_id

<> means "not equal to." Without it, every patient matches themselves.

Translate this into a query: "I want to see every prescription along with the name of the patient it was issued to."

SELECT rx.prescription_id,
       p.patient_name,
       rx.date_issued
FROM Prescription AS rx
INNER JOIN Patient AS p ON rx.patient_id = p.patient_id;

Inner join — you only want rows where both a prescription and a matching patient exist.

Translate this into a query: "I want to see all patients, and if they have any prescriptions show those too. Show every patient even if they have none."

SELECT p.patient_name,
       rx.prescription_id,
       rx.date_issued
FROM Patient AS p
LEFT JOIN Prescription AS rx ON p.patient_id = rx.patient_id;

Left join — every patient must appear regardless of whether prescriptions exist.

You need to audit the database and see every physician and every prescription, including physicians with no prescriptions and prescriptions with no linked physician. Which join do you use and why?

FULL JOIN. You need all rows from both tables regardless of whether a match exists. NULLs will appear wherever the relationship is missing, revealing broken or incomplete data.

SELECT ph.physician_name,
       rx.prescription_id
FROM Physician AS ph
FULL JOIN Prescription AS rx ON ph.physician_id = rx.physician_id;