Monday 31 October 2016

sql - INNER JOIN ON vs WHERE clause



For simplicity, assume all relevant fields are NOT NULL.



You can do:



SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
AND (some other conditions)


Or else:



SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1 INNER JOIN table2
ON table1.foreignkey = table2.primarykey
WHERE
(some other conditions)


Do these two work on the same way in MySQL?


Answer



INNER JOIN is ANSI syntax which you should use.



It is generally considered more readable, especially when you join lots of tables.



It can also be easily replaced with an OUTER JOIN whenever a need arises.



The WHERE syntax is more relational model oriented.



A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.



It's easier to see this with the WHERE syntax.



As for your example, in MySQL (and in SQL generally) these two queries are synonyms.



Also note that MySQL also has a STRAIGHT_JOIN clause.



Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.



You cannot control this in MySQL using WHERE syntax.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...