Misusing LEFT JOIN

[Sarah Sproehnle]

SQL is definitely fun, unless it's not. ;) I've seen many cases where developers misuse a LEFT (or RIGHT) JOIN. Either they don't understand what an outer join is, or they needed an outer join at some point, but as the code evolved it wasn't necessary any more.

Let's say you have a Customer table and a Phone and Address table that reference the Customer table. To query customers and their info:
SELECT fname, lname, number, street
FROM cust JOIN phone
USING (custid)
JOIN address
USING (custid);
+-------+-----------+-----------------+----------------+
| fname | lname | number | street |
+-------+-----------+-----------------+----------------+
| Sarah | Sproehnle | [phone] | 42 Sakila Lane |
| Chris | Schneider | [phone] | 88 MySQL Way |
+-------+-----------+-----------------+----------------+

However, if you may have a customer without an address or phone number, they wouldn't be listed. So use a LEFT JOIN:
SELECT fname, lname, number, street
FROM cust LEFT JOIN phone
USING (custid)
LEFT JOIN address
USING(custid);
+-------+-----------+-----------------+----------------+
| fname | lname | number | street |
+-------+-----------+-----------------+----------------+
| Sarah | Sproehnle | [phone] | 42 Sakila Lane |
| Chris | Schneider | [phone] | 88 MySQL Way |
| MySQL | User | NULL | NULL |
+-------+-----------+-----------------+----------------+

Now for the mistake I often see. Someone will do a LEFT JOIN between 2 tables, but then an inner join that makes the left join pointless:
SELECT fname, lname, number, street
FROM cust LEFT JOIN phone
USING (custid)
JOIN address
USING (custid);
+-------+-----------+-----------------+----------------+
| fname | lname | number | street |
+-------+-----------+-----------------+----------------+
| Sarah | Sproehnle | [phone] | 42 Sakila Lane |
| Chris | Schneider | [phone] | 88 MySQL Way |
+-------+-----------+-----------------+----------------+