CODEWITHSUNDEEP
mysqloptimization
user1016265
user1016265

Reputation: 2397

Mysql query explanation

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'

Upvotes: 1

Views: 84

Answers (2)

Konerak
Konerak

Reputation: 39763

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)

Upvotes: 6

Suzan Cioc
Suzan Cioc

Reputation: 30107

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.

Upvotes: 0

Related Questions