2
SELECT EMP.EMPNAME,EMP.EMPID,
EMP.EMPDEPID,DEPT.DEPTID
FROM EMPLOYEE EMP,DEPARTMENT DEPT
WHERE EMP.EMPDEPID=DEPT.DEPTID;

Here in the where clause, is the order of the conditions important? That is, will this get me different results?:

EMP.EMPDEPID = DEPT.DEPTID

or 

DEPT.DEPTID = EMP.EMPDEPID
ANeves
  • 6,219
  • 3
  • 39
  • 63
user3754837
  • 21
  • 1

2 Answers2

1

This order is not important. DB engine optimizer will parse your query and create execution plan. So, you place it this way or another, optimizer will rule it in its way anyway.

As a matter of fact you can test it. Get both execution plans and compare. Here it is explained: How do I obtain a Query Execution Plan?

Also, newer Sql standards recommend using Inner join ... On ... syntax and not list of tables in FROM

Community
  • 1
  • 1
T.S.
  • 18,195
  • 11
  • 58
  • 78
1

Previously, I believed that the join order was specified in the SQL 92 standard.

Which means you would write your query using INNER JOIN syntax like this:

SELECT EMP.EMPNAME,EMP.EMPID,
EMP.EMPDEPID,DEPT.DEPTID
FROM EMPLOYEE EMP 
inner join DEPARTMENT DEPT on EMP.EMPDEPID=DEPT.DEPTID;

This incorrect information was was enforced by LINQ where in the join, the first join element corresponds to the first or previously declared table.

The specification however just indicates that the join must match a <search condition>. 

<join specification> ::=
    <join condition>
  | <named columns join>

<join condition> ::= ON <search condition>

This search condition is the same that is used in the WHERE clause exactly like your SQL 89 style join.

The <search condition> is defined as:

 <search condition> ::=
        <boolean term>
      | <search condition> OR <boolean term>

Therefore, as long as you create a boolean term in your search condition, the query is valid. EMP.EMPDEPID=DEPT.DEPTID and DEPT.DEPTID=EMP.EMPDEPID are both boolean terms and evaluate to the same thing. Thus, no difference between them.

crthompson
  • 15,653
  • 6
  • 58
  • 80
  • Could you please point to the part in the standard which says comparison order is important in a join? – Quassnoi Jun 20 '14 at 17:39
  • @Quassnoi, I've just spent an hour going over [the spec](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) and while I feel certain that I've read it before, I cant find anything to corroborate it. The search conditions are exactly that of a `where` clause and the order doesnt matter. I'll try to edit my post to reflect that. – crthompson Jun 20 '14 at 19:22