Understanding self-join with different ON clause

Question

Here is my table structure:

// mytable
+----+---------+----------+
| id | related | subject  |
+----+---------+----------+
| 1  | NULL    | subject1 |
| 2  | 1       |          |
+----+---------+----------+

And there are two queries which seem identical to me, but have different results in tests:

SELECT a.id, IFNULL(b.subject, a.subject)
FROM mytable a
LEFT JOIN mytable b ON a.id = b.related

    +----+----------+
    | 1  | subject1 |
    | 2  |          |
    +----+----------+

SELECT a.id, IFNULL(b.subject, a.subject)
FROM mytable a
LEFT JOIN mytable b ON b.id = a.related

    +----+----------+
    | 1  | subject1 |
    | 2  | subject1 |
    +----+----------+

Look, it is self-join. So why the result of ON a.id = b.related and ON b.id = a.related is different?

Run both of these as `Select *` and look at the data. It will make sense then. — JNevill, Apr 20 '18 at 15:44
Hi. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. PS Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join on goes with your left join on. — philipxy, Apr 21 '18 at 05:43
Because it's a left join here order will matter if you instead use INNER JOIN both would give same result — Vinay, Apr 21 '18 at 12:17

score 3 · Answer 1 · answered Apr 20 '18 at 15:45

Running your queries with SELECT * to uncover some of the mystery:

Your first query:

SELECT *
FROM mytable a
LEFT JOIN mytable b ON a.id = b.related;

Produces the following:

+----+---------+----------+--------+----------+----------+
| id | related | subject  |  id1   | related1 | subject1 |
+----+---------+----------+--------+----------+----------+
|  2 | 1       | <null>   | <null> | <null>   | <null>   |
|  1 | <null>  | subject1 | 2      | 1        | <null>   |
+----+---------+----------+--------+----------+----------+

Your second query:

SELECT *
FROM mytable a
LEFT JOIN mytable b ON b.id = a.related;

Produces this:

+----+---------+----------+--------+----------+----------+
| id | related | subject  |  id1   | related1 | subject1 |
+----+---------+----------+--------+----------+----------+
|  2 | 1       | <null>   | 1      | <null>   | subject1 |
|  1 | <null>  | subject1 | <null> | <null>   | <null>   |
+----+---------+----------+--------+----------+----------+

Your first query is joining id 2 to related 2. There is no related 2 and since id 2 has no subject, you get no subject out of your ifnull().

Your second query is joining related 1 to id 1 for a.id 2. This pulls a subject from b.id 1 and you get a subject back for id 2 as a result.

You really have to mentally map out how a LEFT JOIN works here and how it is affected by your ON clause. You have two very different queries here as a result.

spencer7593 · Accepted Answer · 2018-04-20T16:02:51.803

Both queries are getting all rows from a.

Both queries are doing an outer join to b.

What's different is the condition that is used for finding a "match" from b.

(The queries might seem to be identical, but the truth is that they are significantly different.)

As a demonstration, run a query like this:

SELECT a.id             AS `a_id`
     , a.related        AS `a_related`
     , a.subject        AS `a_subject`
     , b.id             AS `b_id`
     , b.related        AS `b_related`
     , b.subject        AS `b_subject`
  FROM mytable a
  LEFT
  JOIN mytable b
    ON b.related = a.id

And then change the ON clause

    ON b.id = a.related

You might also want to repeat both of those queries removing the LEFT keyword (to make it an inner join instead of an outer join.)

One way to look at an outer join... when a matching row from b is not found, a dummy row from b is invented. That dummy row consists entirely of NULL values, and the dummy row is joined to a, as if it were a matching row. (This isn't necessarily what the database engine actually does, but thinking about it this way gives us an insight to the results that the outer join returns.)

Take a close look at the results of the queries, and you will be able to see why the results by the queries are different.

The fact that a and b refer to the same table is a special case. We would see the same results if those were two different tables. It really doesn't matter... to the query, those are two different sources which just happen to refer to the same table. Don't let the fact that a and b refer to the same table cause any confusion.

Understanding self-join with different ON clause

2 Answers2