Menu
Is there any significant difference between the following?
SELECT a.name, b.name FROM a, b WHERE a.id = b.id AND a.id = 1
AND
SELECT a.name, b.name FROM a INNER JOIN b ON a.id = b.id WHERE a.id = 1
Do SO users have a preference of one over the other?
598
Such joins are called non-equi JOINs, and they are also possible in SQL. When you join two tables using other conditional operators, beyond the equal sign, non-equi JOINs come into play. Comparison operators, like <, >, <=, >=, != , and <> and the BETWEEN operator work perfectly for joining tables in SQL.
An 'inner join' is not the same as an 'equi-join' in general terms. 'equi-join' means joining tables using the equality operator or equivalent.
“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns. An inner join is a join of two or more tables that returns only those rows (compared using a comparison operator) that satisfy the join condition.
There is no difference, but the readability of the second is much better when you have a big multi-join query with extra where clauses for filtering.
Separating the join clauses and the filter clauses is a Good Thing :)
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
