EXPLAIN EXTENDED

From Stack Overflow (shortened for clarity):

I have two tables: homes and home_photos:

It’s very possible that a home does not have a photo associated with the home.

In that case, the column primary_photo_group_id will have value 0.

Otherwise, primary_photo_group_id equals the group_id of the photo.

How do I return a NULL if the photo if not defined for a home?

This is of course a classic task for an OUTER JOIN:

SELECT  *
FROM    homes h
LEFT JOIN
        photos p
ON      p.home_photo_group_id = h.primary_photo_group_id
        AND …

This construct, however, can be optimized, and I’ll show how.

Let’s create and fill the sample tables:
Read the rest of this entry »