CODEWITHSUNDEEP
mysql
user198729
user198729

Reputation: 63676

Why these two statements have different results?

mysql> select a.id,a.parent from qa a left join qa b on a.parent=b.parent where b.id=7;
+----+--------+
| id | parent |
+----+--------+
|  7 |      1 |
|  8 |      1 |
|  9 |      1 |
+----+--------+
3 rows in set (0.00 sec)

mysql> select a.id,a.parent from qa a left join qa b on a.parent=b.parent and b.id=7;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |   NULL |
|  3 |   NULL |
|  4 |   NULL |
|  5 |   NULL |
|  6 |   NULL |
|  7 |      1 |
|  8 |      1 |
|  9 |      1 |
+----+--------+

I read them exactly the same!

Upvotes: 2

Views: 79

Answers (3)

kibitzer
kibitzer

Reputation: 4589

First has a condition (where) on the select statement, second applies b.id condition on the join.

From the docs:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table.

Upvotes: 0

davek
davek

Reputation: 22925

you first query is limiting the right hand side of your result by automatically eliminating the NULLs that a LEFT JOIN can/will return. The statementds would return the same results if you were using an INNER JOIN.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166476

The first statement will create the join, and then filter the result using WHERE b.id=7.

So only rows from a where a.parent=b.parent and that join result has b.id=7.

The second statement will create the left join with the b.id=7, thus including all rows from qa a, and then only values from qa b where b.id=7.

So all rows from a where a.parent=b.parent, but show values from b only where b.id=7

Upvotes: 3

Related Questions