What is the optimal way to select the parent and child rows on a table with a self-relationship? Sample data from the genres table:

id   | genre_name     | genre    | parent_id
---------------------------------------------
2    | Blues          | MAIN     | NULL
6    | Folk           | MAIN     | NULL
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10


Why doesn't the statements #1 and #2 (with the reflexive join) NOT return the parent?

-- Statement #1 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id OR (pd.id= NULL AND pg.id = 10)
    WHERE pg.id = 10;
-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #2 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id 
    WHERE pg.id = 10 AND pg.parent_genre_id IS NULL;

-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10


-- Statement #3 returns both parent and child rows.
(SELECT
    id,
    genre_name,
    genre_type,
    parent_genre_id
    FROM `genres`
    WHERE id = 10)
UNION -- Get all sub-genres (children)
(SELECT
    cg.id,
    cg.genre_name,
    cg.genre_type,
    cg.parent_genre_id
    FROM `genres` AS pg
     JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10);

-- OUTPUT:
id   | genre_name     | genre    | parent_id
---------------------------------------------
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10


The #3 statement seems like a bad use of UNION to me.

Warning, bad pun intended:

Now the term self-relationship sounds like it implies a certain thing people do with their hands below their belt; but in any case I would like to know why the first two selects do not cum up with the intended results. Rather disappointing after the query reached climax. Uh, I mean returns the results.



After the answer given below, I ended up using the UNION (no JOIN of course):

(SELECT 
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
    FROM `genres`
    WHERE `id` = 10)
UNION -- Get all sub-genres (children)
    (SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
        FROM .`genres`
        WHERE `parent_genre_id` = 10);
See the comment on the question about #1 and #2.

I don't believe MySQL supports recursive CTEs, so a union is likely to be the best way to do this.

The self joins are only going to work where the parent key is not null, because NULL will never evaluate as equal to anything (unless MySQL has weird NULL semantics).  In order to capture the parent the union is a reasonable approach.  Left joins for this are barking up the wrong tree because you can only specify them as a join predicate for the child and you're trying to include data from a parent row that actually has no join against itself.

Another approach would be to make parent_genre_id equal to id on the pop (id = 10) row, so the self join would also pick up the parent.  You can tell the parent because the id and parent_genre_id keys are the same value.