MySQL LEFT JOIN: selecting records with no corresponding entries in second table

Syntax:

SELECT u.uid, u.name, m.id, p.value FROM mpulreg m, users u LEFT JOIN profile_values p ON p.uid = u.uid WHERE u.uid = m.regnum AND p.uid = u.uid AND p.fid='3' AND m.status != 'D' ORDER BY u.name

This says, select the fields from the tables and select profile_values.value where profile_values.uid = users.uid, even if it's empty. So, you'll get an empty value for the profile_values.value if it doesn't exist.

Detailed explanation

You have two or more tables and would like to get entries from all the tables in one SELECT statement. However, one of the tables doesn't have any entries that correspond to another table with entries in it.

For example, let's say you have a username table, an event registration table and an user real name table. The users table contains a few users; the event contains information about who has signed up for an event; and the real name table shows the username's real names:

+------------------+  +----------------+  +---------------------+
| users            |  | events         |  | names               |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| uid | name       |  | id | eid | uid |  | id | uid | realname |    
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 1   | kristofer  |  | 1  | 1   | 3   |  | 1  | 1   | Kris McQ |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 2   | kitt       |  | 2  | 1   | 1   |  | 2  | 2   | Kate McQ |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 3   | elizabeth  |  | 3  | 1   | 2   |  
+-----+------------+  +----+-----+-----+  

In this case, 'kitt' is going to event 1 (with eid = 1), as are 'kristofer' and 'elizabeth'. Similarly, kristofer's real name is 'Kris McQ', and kitt's real name is 'Kate McQ'. We don't know elizabeth's real name.

Now, we want to list all the people and see if they are coming to the event, what their real names are:

To do so, first SELECT on the users and events, and we see that everyone is going.

SELECT u.*, e.* FROM users u, events e WHERE u.id = e.uid;

If we just put in the real names table, we'll get the wrong information:

SELECT u.*, e.*, r.* FROM users u, events e, realnames r WHERE u.id = e.uid;

Instead we want to use a LEFT JOIN to join two tables, then use that result set on the selection of data from the third table:

SELECT u.*, e.*, r.* FROM users u, events e LEFT JOIN realnames r ON r.uid = e.uid WHERE u.id = e.uid;