Atul Atul - 5 months ago 13
SQL Question

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

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

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]>
Comments