Joining 2 tables connecting records that match



mysql> select * from demo_people join demo_property on demo_people.pid = demo_property.pid;

+-----+-----------+--------------+------+------+----------------------+

| pid | name      | phone        | pid  | spid | selling              |

+-----+-----------+--------------+------+------+----------------------+

|   1 | Mr Brown  | 01225 708225 |    1 |    1 | Old House Farm       |

|   3 | Mr Pullen | 01380 724040 |    3 |    2 | The Willows          |

|   3 | Mr Pullen | 01380 724040 |    3 |    3 | Tall Trees           |

|   3 | Mr Pullen | 01380 724040 |    3 |    4 | The Melksham Florist |

|   3 | Mr Pullen | 01380 724040 |    3 |    6 | The Beetle Drive     |

+-----+-----------+--------------+------+------+----------------------+

5 rows in set (0.05 sec)



Joining two tables connecting records that match PLUS an extra for each "orphan" on left



mysql> select * from demo_people left join demo_property on demo_people.pid = demo_property.pid;

+-----+------------+--------------+------+------+----------------------+

| pid | name       | phone        | pid  | spid | selling              |

+-----+------------+--------------+------+------+----------------------+

|   1 | Mr Brown   | 01225 708225 |    1 |    1 | Old House Farm       |

|   2 | Miss Smith | 01225 899360 | NULL | NULL | NULL                 |

|   3 | Mr Pullen  | 01380 724040 |    3 |    2 | The Willows          |

|   3 | Mr Pullen  | 01380 724040 |    3 |    3 | Tall Trees           |

|   3 | Mr Pullen  | 01380 724040 |    3 |    4 | The Melksham Florist |

|   3 | Mr Pullen  | 01380 724040 |    3 |    6 | The Beetle Drive     |

+-----+------------+--------------+------+------+----------------------+

6 rows in set (0.01 sec)



Selecting only orphans from the join - doing it the CORRECT way



mysql> select * from demo_people left join demo_property on demo_people.pid = demo_property.pid where spid is NULL;

+-----+------------+--------------+------+------+---------+

| pid | name       | phone        | pid  | spid | selling |

+-----+------------+--------------+------+------+---------+

|   2 | Miss Smith | 01225 899360 | NULL | NULL | NULL    |

+-----+------------+--------------+------+------+---------+

1 row in set (0.00 sec)



Trying to select the orphans, but failing because NULL is not a value 



mysql> select * from demo_people left join demo_property on demo_people.pid = demo_property.pid where spid = NULL;

Empty set (0.00 sec)



mysql>