Are these SQL Statements equivalent?

Question

Possible Duplicate:
Explicit vs implicit SQL joins

Stmt1: SELECT ... FROM ((a JOIN b ON <cond1>) JOIN c ON <cond2>)

Stmt2: SELECT ... FROM a, b, c WHERE <cond1> AND <cond2>

I'm not sure whether the second statement can give a smaller resultset. If there are several rows in B matching to one row in A, do we get all these matches with the second statement?

did you try running them to see if the results are the same? — Taryn, Jul 11 '12 at 17:09
yes i did, and with my example it was working. but i find this irritating anyway. maybe there are still not equivalent. — user1000742, Jul 11 '12 at 17:13
This question was about SQL in general - we use MySQL and PostgreSQL at university. — user1000742, Jul 11 '12 at 17:26
Check this question http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Jesse, Jul 11 '12 at 17:26

Razvan · Accepted Answer · 2012-07-11T17:56:56.707

2

As a final result, yes. Regarding the execution: the query optimizer might end up creating the same query execution plan for both queries.

This will be the case if, according to its approximate statistics (approximate equi-depth histograms for instance - which are not all the time up-to-date, by the way), the optimizer will determine that the first join is more selective than the second one and, consequently, it will execute this one first.

Stmt1 allows you to specify the order of the joins and, considering that you know exactly what the tables contain, this might be a better solution.

edited Jul 11 '12 at 17:56

answered Jul 11 '12 at 17:12

Razvan

9,925
6
38
51

Thanks. I guess the query optimizer will transform the second one into joins anyway (because there is no other possibility in relational algebra to connect the relations) – user1000742 Jul 11 '12 at 17:17
The second one already contains three inner joins. The second one has a bigger complexity. – Razvan Jul 11 '12 at 17:21
The only way to tell is to perform a SHOW PLAN. Depending on the statistics, it is possible that in many cases the two queries will yield the same plan, hence be executed in the same way. For example, if the optimizer decides for the second query that joining a to b first is better than joining b to c, based on the selectivity of the indexes and the number of rows in the tables, then the two queries will be executed in the same way. – Paul Chernoch Jul 11 '12 at 17:39
I downvoted this because SQL is a declarative language. The execution plan in database specific. In practice, most databases would optimize the two statements to the same plan. – Gordon Linoff Jul 11 '12 at 17:42
@GordonLinoff I edited my answer and this is how I was thinking when I gave the first answer. – Razvan Jul 11 '12 at 17:59

score 1 · Answer 2 · answered Jul 11 '12 at 18:11

Semantically the queries are going to be identical. However, trying to rely on the plans to prove this is not a good idea.

It would also be possible to drop in arbitrary <cond1>and <cond2> such that the query is valid in the second form but not legal in the first one.

In that sense the second on is more general, but as long as the first one is good, then the second one is equivalent.

Are these SQL Statements equivalent?

2 Answers2