When using JOINS we have the choice of using either the WHERE clause and the OR clause.
Question: is there a performance difference between these two? Should one be used over the other? What is the difference exactly?
Example:
SELECT field
FROM table1
INNER JOIN table2
ON table1.id = table2.id;
or
SELECT field
FROM table1, table2
WHERE table1.id = table2.id;
In an INNER JOIN, you could use the ON and WHERE interchangebly, just a case of preference really. The underlying logic doesn't change and it also doesn't have an impact of the execution plan as well.
Its quite different for the OUTER JOINs.
a. Filter Criteria in the WHERE Clause i.e.
SELECT * FROM A left join B
ON A.id=B.id
WHERE B.id=1
The above query will function like an INNER JOIN and limit the entire result only to the row with B.id=1 (Note that the filter is applied to the table on the right of the LEFT JOIN operator)
b. Filter Criteria in the ON Clause i.e.
SELECT * FROM A left join B
ON A.id=B.id
AND B.id=1
The above query will only limit the rows of table B as they are being mapped to table A in the LEFT JOIN operator. So the result will contain all rows of table A and values in the columns for table B only for the rows that match the condition B.id=1
Credit to @Abraham - here is the SQLFIDDLE for the same
The performance should the same, but I would prefer the explicit join syntax for any new development.
There are some things you can't do the older table1, table2 syntax that you can do with the newer table1 join table2 syntax. Also, for queries involving several tables, the older syntax is incredibly difficult to read.
