Say you have a table of information, and another table that has more information, there’s a clever way of getting data from, one, the other, or both. It’s nothing new really at all, more of a reference thing for me… Say you have two tables:
common_table:
| id | name | age |
|---|---|---|
| 1 | Joe Bloggs | 29 |
| 2 | John Doe | 23 |
uncommon_table:
| id | customer_id | postcode |
|---|---|---|
| 6 | 1 | PO57 1AA |
Joining these is a standard operation, and if we wanted all records, that may or may not have a postcode, we’d:
SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id
And that would give us:
| c.id | c.name | c.age | u.id | u.customer_id | u.postcode |
|---|---|---|---|---|---|
| 1 | Joe Bloggs | 29 | 6 | 1 | PO57 1AA |
| 2 | John Doe | 23 | NULL | NULL | NULL |
Note carefully the fact that the uncommon_table values in the second row are NULL. We can use this to distinguish which records have entries in the uncommon_table, and which don’t. Then we can form queries based on this, for example if you wanted a report of each user with their postcode, ignoring those without postcodes:
SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NOT NULL
And converseley, find those people who haven’t got a postcode entry:
SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NULL
Knowing this can be handy, and sometimes quite powerful.
