1

Sometime during joining couples tables i seen that condition criterias placed are inside ON() clause, and sometime out of it, means after WHERE.

What approach is more optimized and faster in big amount of data ?

What will be faster 1.

SELECT a.column1, b.column2
FROM tablea a
JOIN tableb b
ON a.column3 = b.column3 
WHERE b.column2='Y' AND a.column1='N'

or this one 2.

SELECT a.column1, b.column2
FROM tablea a
JOIN tableb b
ON (a.column3 = b.column3 AND b.column2='Y')
WHERE  a.column1='N'
user1016265
  • 2,307
  • 3
  • 32
  • 49

2 Answers2

6

There are two ways of joining:

1/ SQL-89-style, using comma separated tables and the WHERE clause

Example:

SELECT a.column1, b.column2
FROM tablea a, tableb b
WHERE a.column3 = b.column3

2/ SQL-92-style, using the JOIN ... ON clause

Example: 

SELECT a.column1, b.column2
FROM tablea a
JOIN tableb b
ON a.column3 = b.column3

The 92 style is more modern and is preferred, because the join is actually much more visible when reading the query. You can mix both styles, and it will work, but that is a terrible idea.

About performance, I can not do better than an already existing answer on Stackoverflow. I will quote the gist of it:

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

(emphasis mine)

Community
  • 1
  • 1
Konerak
  • 39,272
  • 12
  • 98
  • 118
  • 1
    The first style, using WHERE is an implied INNER JOIN. If you want to do any other kind of join you need to list it explicitly using the JOIN .. ON syntax. – CLo Feb 08 '12 at 16:46
  • @Konerak: I agree with almost all you said. But how do these answer the question about efficiency? – ypercubeᵀᴹ Feb 08 '12 at 16:48
  • btw: I think conditions after (in) `ON` clause should be only related to joining tables (not filtering, the "static part" of query) whenever possible and "general conditions" (anything like `column = ?`) and "filtering" should be in where clause, what's your opinion? – Vyktor Feb 08 '12 at 16:51
  • @ypercube: I indeed forgot to answer that second part of the question, but another answer on stackoverflow does it perfectly. I have linked to the answer and quoted the applicable paragraph. – Konerak Feb 08 '12 at 16:52
  • Vyktor: I agree. However, if someone needs to join table a with table b `on a.c1 = b.c1 AND a.c2 = 'join_b'`, his datamodel probably needs normalization. If you can't change the model at the time, I would still advise the `AND a.c2 = 'join_b'` to be in the ON clause, so it is clear it belongs with the join and not with the specific values for that query. – Konerak Feb 08 '12 at 16:54
  • 2
    @Vyktor Sometimes that won't work. Consider this: `SELECT * FROM tblA LEFT JOIN tblB ON tblA.id = tblB.id WHERE tblB.active = 'Y'` If there's no matching record in tblB, its fields will be null (duh, because it's a left join). But then if `tblB.active` is null, the `WHERE` will filter that one out. In this case, `tblB.active = 'Y'` needs to be in the join condition. – Wiseguy Feb 08 '12 at 16:57
  • @Wiseguy I read your last post twice and could not understood. It will not matter where we will check tblB.active in your example. in any case it will show us only records who has active as 'Y'... So could you explain why do you think that (in your example) this criteria should be in "join on" clause ? Do you think in this case DB will take lesse rows to analyze ? – user1016265 Feb 09 '12 at 06:39
  • @user1016265: that's why his comment got the upvotes: it was insightful. Note he is using a `LEFT JOIN`: if you put the second clause `active=Y` in the ON clause, every row from table a will be shown, with either one or more matching rows from table b, or NULL in the fields from table b. If you put the second clause `active=Y` in the WHERE clause, MySQL will internally generate the join, and then filter the fields where active is NOT Y. You won't get the NULL fields, you won't get each row from table a. Feel free to create a question on itself if you want more explanations/examples. – Konerak Feb 09 '12 at 09:40
0

ON is a part of ternary table operation <table1> JOIN <table2> ON <condition>. The result of this operation is a imaginary table with which statement works.

WHERE is a clause which filters records.

Suzan Cioc
  • 29,281
  • 63
  • 213
  • 385