List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 27 1999 11:04am
Subject:Re: LEFT JOIN ON Clause moved to WHERE
View as plain text  
At 21:23, 19990826, Michael Griffith wrote:
>I read in one of the messages on the list posted by Monty that when
>processing  a LEFT JOIN, mysql moves the ON clause to the WHERE clause.
>
>I like to use the LEFT JOIN ... ON syntax for easier reading when I write
>complicated queries, but many times I cannot get a query to work unless I
>move the ON clause to the WHERE clause myself. Sorry I don't have a specific
>example.

Yes, MySQL moves the condition from the ON clause to the WHERE clause.  But
the JOIN that is performed is different, so the condition is being checked
against a different set of rows.


>1) SELECT * FROM a,b WHERE a.id = b.id
>2) SELECT * FROM a LEFT JOIN b ON a.id = b.id

Here is a simple (if somewhat lengthy) example that should show what is
going on.

    /*
     * First, the setup
     */

    mysql> select * from one;
    +----+------+
    | id | val  |
    +----+------+
    |  1 | aa   |
    |  2 | ab   |
    |  6 | ba   |
    +----+------+
    3 rows in set (0.01 sec)

    mysql> select * from two;
    +----+--------+----------------+
    | id | one_id | at             |
    +----+--------+----------------+
    |  1 |      1 | 19990827030617 |
    |  2 |      2 | 19990827030624 |
    +----+--------+----------------+
    2 rows in set (0.01 sec)

    /*
     * Now do a full table join - this is the cartesian product
     * of the two sets (tables).  Notice that there are 5 columns
     * (two from table one, and three from table two), and 6 rows
     * (3 * 2).
     */

    mysql> select * from one, two;
    +----+------+----+--------+----------------+
    | id | val  | id | one_id | at             |
    +----+------+----+--------+----------------+
    |  1 | aa   |  1 |      1 | 19990827030617 |
    |  2 | ab   |  1 |      1 | 19990827030617 |
    |  6 | ba   |  1 |      1 | 19990827030617 |
    |  1 | aa   |  2 |      2 | 19990827030624 |
    |  2 | ab   |  2 |      2 | 19990827030624 |
    |  6 | ba   |  2 |      2 | 19990827030624 |
    +----+------+----+--------+----------------+
    6 rows in set (0.00 sec)

    /*
     * Now reduce the rows by selecting only the ones with equal
     * values in the first and fourth columns.
     */

    mysql> select * from one, two where one.id = two.one_id;
    +----+------+----+--------+----------------+
    | id | val  | id | one_id | at             |
    +----+------+----+--------+----------------+
    |  1 | aa   |  1 |      1 | 19990827030617 |
    |  2 | ab   |  2 |      2 | 19990827030624 |
    +----+------+----+--------+----------------+
    2 rows in set (0.01 sec)

    /*
     * Do the same reduction, only with the ON clause instead of
     * the WHERE clause, and using a LEFT JOIN instead of a normal
     * full join (a.k.a. ",").
     */

    mysql> select * from one left join two on (one.id = two.one_id);
    +----+------+------+--------+----------------+
    | id | val  | id   | one_id | at             |
    +----+------+------+--------+----------------+
    |  1 | aa   |    1 |      1 | 19990827030617 |
    |  2 | ab   |    2 |      2 | 19990827030624 |
    |  6 | ba   | NULL |   NULL |           NULL |
    +----+------+------+--------+----------------+
    3 rows in set (0.01 sec)

Ah, there's an extra row when using the LEFT JOIN + ON.  Let's look
again at what Monty said:

http://www.progressive-comp.com/Lists/?l=mysql&m=91938338016806&w=2

  # LEFT JOIN is in MySQL implemented as follows:
  # 
  # - Move the ON condition to the WHERE clause
  # - Do all join and where optimizations, but with the exceptions that
  #   the left table in 'A LEFT JOIN B' is always read before the right
  #   table.  (This will also make the join optimizer a bit faster)
  # - If there is a row in 'A' that matches the WHERE clause but there
  #   wasn't any row in B that matched the on clause then an extra 'B'
  #   row is generated with all B columns set to NULL.

The last statement holds the key.  MySQL generates extra rows for every
row in A that is "OK" which doesn't have a match in B.

For our example, every row in table one is "OK", because there is no
constraint on what value "id" or "val" should have.  But the last row
in table one (where id = 6) does not have a matching row in table two
(where one_id = 6).  So MySQL generates a row in table two with all
columns set to NULL.  It's as if you had this, then:

    mysql> select * from one, two;
    +----+-----+------+--------+----------------+
    | id | val |   id | one_id | at             |
    +----+-----+------+--------+----------------+
    |  1 | aa  |    1 |      1 | 19990827030617 |
    |  2 | ab  |    1 |      1 | 19990827030617 |
    |  6 | ba  |    1 |      1 | 19990827030617 |
    |  1 | aa  |    2 |      2 | 19990827030624 |
    |  2 | ab  |    2 |      2 | 19990827030624 |
    |  6 | ba  |    2 |      2 | 19990827030624 |
    |  6 | ba  | NULL |   NULL | NULL           |
    +----+-----+------+--------+----------------+
    6 rows in set (0.00 sec)

But that last row, with the NULL's, is magically created by MySQL.
I hope this clears it up a little bit for you.  Monty was replying
to a specific question about how LEFT JOIN is optimized - so his
description of a LEFT JOIN was not intended as an explanation of how
to *think* about LEFT JOIN; instead, he was describing how MySQL does
a left join under the surface.  I don't think of the ON condition
moving to the WHERE clause.  Instead, I think of the ON clause always
matching - either by picking out existing rows in the second table
that match the row in the first table that I'm currently working on,
or by making up a row full of NULL values.

Once you use LEFT JOIN a few times (for it's real purpose, not as
(bogus) syntactical sugar), you'll get a feel for it and be able to
picture it in your head.

Tim
Thread
LEFT JOIN ON Clause moved to WHEREMichael Griffith27 Aug
  • Re: LEFT JOIN ON Clause moved to WHERESasha Pachev27 Aug
  • Re: LEFT JOIN ON Clause moved to WHEREThimble Smith27 Aug