PHP Freaks

LEFT JOIN

We use a left join when we want to return all selected rows from the left table regardless of whether there is a matching value in the right table. (LEFT merely denotes its postion in the query statement. If you have "A LEFT JOIN B" then "A", to the left of the "JOIN", is the left table)

Using the same query as the previous problem, except with a LEFT JOIN instead of an INNER JOIN

Results

+-----------+-----------+-------+
| subjectID | subject   | total |
+-----------+-----------+-------+
|         1 | English   |    12 |
|         2 | German    |    11 |
|         3 | Geography |     6 |
|         4 | Maths     |     7 |
|         5 | Economics |    13 |
|         6 | Biology   |     6 |
|         7 | Chemistry |     6 |
|         8 | Physics   |    10 |
|         9 | History   |    10 |
|        10 | Computing |     0 |    ------- now we get all subjects
+-----------+-----------+-------+

Where there is no matching row in the right table, a NULL value is returned in any columns selected from that table

Results:

+-----------+-----------------+
| subject   | pupil_name      |
+-----------+-----------------+
| Maths     | William Smith   |
| Maths     | Henry Irving    |
| Maths     | David Powell    |
| Maths     | Wayne Jones     |
| Maths     | John Tully      |
| Maths     | Mary Whitehouse |
| Maths     | Peter Appleby   |
| Computing | NULL            |
+-----------+-----------------+

We can use the NULL value when we need to know if there are records in one table with NO matching record in another table. So to find subjects not chosen by any pupil

Results:

+-----------+
| subject   |
+-----------+
| Computing |
+-----------+

WHERE and LEFT JOINS

One last word on LEFT JOINs, and something I still forget myself sometimes.

Suppose we want a list of all pupils and we want to indicate those taking geography (subject id 3). The obvious query is

Unfortunately, this gives just a list of those taking geography and not all pupils as expected.

+------------------+-----------+
| pupil_name       | subjectID |
+------------------+-----------+
| John Patterson   |         3 |
| Allan Blair      |         3 |
| Caroline Freeman |         3 |
| Emma Watson      |         3 |
| Anthony Bell     |         3 |
| Mary Whitehouse  |         3 |
+------------------+-----------+

Since not all rows in the right table of a LEFT JOIN may not be present then it doesn't work if you have a WHERE condition on non-existent records. You need to make right-table conditions part of the join condition.

Now we get the expected results

+------------------+-----------+
| pupil_name       | subjectID |
+------------------+-----------+
| John Watson      |      NULL |
| Peter Adamson    |      NULL |
| William Smith    |      NULL |
| Mary Sheldon     |      NULL |
| Anna Hamilton    |      NULL |
| Gearge Wilson    |      NULL |
| Henry Irving     |      NULL |
| John Patterson   |         3 |
| Allan Blair      |         3 |
| Adam Simms       |      NULL |
| Mary Blake       |      NULL |
| Anne Bailey      |      NULL |
| Caroline Freeman |         3 |
| David Powell     |      NULL |
| Emma Watson      |         3 |
| Michael Grove    |      NULL |
| John Williams    |      NULL |
| Wayne Jones      |      NULL |
| Anthony Bell     |         3 |
| John Tully       |      NULL |
| Mary Whitehouse  |         3 |
| Jane Morrison    |      NULL |
| Margaret Norton  |      NULL |
| Peter Appleby    |      NULL |
+------------------+-----------+

Which just leaves the cartesian join