1. Mix n match - LEFT & RIGHT joins
We've seen how to find rows which have exact matches using INNER JOINs. Now let's look at some other common joins - LEFT and RIGHT joins.
2. The rationale for LEFT and RIGHT joins
Why do we need LEFT and RIGHT joins?
Here are some examples of where rows in one table may not have an exact match in another.
3. The rationale for LEFT and RIGHT joins
Let's look at the last example in more detail.
4. The rationale for LEFT and RIGHT joins (II)
We have a table of patient admissions and another showing patient discharges. The admissions table has 5 rows, and the discharges table has 3.
An INNER JOIN on those 2 tables shows the patients who had been admitted AND discharged - patients 1,3 and 4.
But that doesn't tell us about current patients.
In order to get the complete picture in one result set, we need a LEFT JOIN.
We need all the rows from the admissions table, so that becomes the table on the left-hand side of the join.
We also want to return all matches and identify non-matches from the discharges table.
Any non-matches will return a NULL value in the discharge column.
Now we have a complete result set showing patients who have been both admitted and discharged, and those who have been admitted but not yet discharged.
5. LEFT JOIN SYNTAX
Here's how we would perform a LEFT join with these tables.
We can also see a pictorial representation of the join below.
The 'Admitted' table is on the LEFT of the join, 'Discharged' is on the right, and they are joined by the Patient_ID.
The symbol on the connecting line indicates that ALL rows will be returned from the Admitted table.
6. LEFT JOIN results
Here we see the final results of the query. We can clearly see the matching records. Patients 2 and 5, who have not yet been discharged, are identified by NULLs in the Discharged column.
7. RIGHT JOIN
RIGHT JOINS are similar to LEFT joins. The difference is that all rows from the right hand table are returned, plus any matches from the left hand table.
Any non-matched rows in the left hand table will return a NULL value.
8. RIGHT JOIN results
AS before, the query returns all rows from the Admitted table, the matches from the Discharged table, and NULLS for any non-matching rows.
9. Summary
Let's recap what we covered:
The difference between INNER joins and LEFT or RIGHT joins is that INNER JOINS only ever return matching rows from both tables.
LEFT or RIGHT joins return ALL the rows from the main query table, plus any matches from the joining table.
If a row in the main table doesn't have a match in the joining table, then a NULL value will appear in any columns you select from the joining table.
LEFT and RIGHT joins can be interchangeable - we can re-write a LEFT to a RIGHT and a RIGHT to a LEFT.
LEFT joins are more common, but we should be aware of RIGHT joins as they are often useful when multiple tables need to be joined together.
10. JOINING Overview...
Finally, here's a simple visual summary of INNER, LEFT and RIGHT joins, showing which rows get returned from either side of the join, for each join type.
11. Let's Practice!
Let's go and make some more joins!