Difference between using "ON .. AND" and "WHERE" in LEFT JOIN

Question

Are these two queries effectively same or any difference between them from execution perspective? If they are same would there be any performance difference between these two?

1.

SELECT * 
FROM TABLE_A tbl_a 
LEFT JOIN TABLE_B tbl_b ON (tbl_a.srno_a = tbl_b.srno_b) 
AND tbl_a.srno_a = 1997

and

2.

SELECT * 
FROM TABLE_A tbl_a 
LEFT JOIN TABLE_B tbl_b ON (tbl_a.srno_a = tbl_b.srno_b) 
WHERE tbl_a.srno_a = 1997

Answer 1

For a LEFT [OUTER] JOIN there is a fundamental difference based on multiplicity. This is because the LEFT/RIGHT/FULL outer joins 'preserve' records that exist in the left/right source sets, respectively, even if they are not matched:

That is: a left join does not remove any rows that appear in the left set, regardless of the join condition.

-- the result of the LEFT join contains every LEFT row AT LEAST ONCE
-- then we throw away the post-join rows that we don't want
SELECT * FROM l
LEFT JOIN r ON r.id = l.id
WHERE l.x = q

-- the above is logically equivalent to the following
-- where the left set is restricted before the join
SELECT * FROM (SELECT * FROM l WHERE l.x = q) l2
LEFT JOIN r ON r.id = l2.id

-- however, it differs from just moving the condition as
-- the result of the LEFT join contains every LEFT row AT LEAST ONCE
-- (this includes the rows for which 'l.x = q' is NOT true!)
-- but we don't filter the post-join rows
SELECT * FROM l
LEFT JOIN r ON r.id = l.id
            AND l.x = q

When using an OUTER JOIN it is not possible to always move the condition. For a LEFT JOIN, a restrictive join condition that depends only on the left relation it is probably .. wrong.

If it were an INNER JOIN then the condition could be moved without affecting the result. This is because the INNER JOIN restricts both joined sets and thus there is no 'AT LEAST ONCE' clause.

Only a valid query can performance be reasoned about - and the Query Execution Plan will contain the relevant execution information. The very act using the 'correct' join can affect how the database executes the query.

Answer 2

Here you can see the difference:

if you use WHERE the result will be smaller. look at the sample.

sample

my tables

MariaDB [yourSchema]> select * from table1;
+--------+------------+
| id     | val        |
+--------+------------+
| 000001 | tabe 1 --1 |
| 000002 | tabe 1 --2 |
| 000003 | tabe 1 --3 |
| 000004 | tabe 1 --4 |
| 000005 | tabe 1 --5 |
| 000006 | tabe 1 --6 |
+--------+------------+
6 rows in set (0.00 sec)

MariaDB [yourSchema]> select * from table2;
+--------+------------+
| id     | val        |
+--------+------------+
| 000001 | tabe 2 --1 |
| 000002 | tabe 2 --2 |
| 000004 | tabe 2 --4 |
| 000005 | tabe 2 --5 |
| 000006 | tabe 2 --6 |
+--------+------------+
5 rows in set (0.00 sec)

MariaDB [yourSchema]>

JOIN with AND

MariaDB [yourSchema]> SELECT *
    -> FROM table1 t1
    -> LEFT JOIN table2 t2 ON t1.id = t2. id AND t2.val ='tabe 2 --4';
+--------+------------+--------+------------+
| id     | val        | id     | val        |
+--------+------------+--------+------------+
| 000004 | tabe 1 --4 | 000004 | tabe 2 --4 |
| 000001 | tabe 1 --1 |   NULL | NULL       |
| 000002 | tabe 1 --2 |   NULL | NULL       |
| 000003 | tabe 1 --3 |   NULL | NULL       |
| 000005 | tabe 1 --5 |   NULL | NULL       |
| 000006 | tabe 1 --6 |   NULL | NULL       |
+--------+------------+--------+------------+
6 rows in set (0.00 sec)

MariaDB [yourSchema]>

JOIN with WHERE

MariaDB [yourSchema]> SELECT *
    -> FROM table1 t1
    -> LEFT JOIN table2 t2 ON t1.id = t2. id
    -> WHERE t2.val ='tabe 2 --4';
+--------+------------+--------+------------+
| id     | val        | id     | val        |
+--------+------------+--------+------------+
| 000004 | tabe 1 --4 | 000004 | tabe 2 --4 |
+--------+------------+--------+------------+
1 row in set (0.00 sec)

MariaDB [yourSchema]>