1

Is the logic of these two queries the same? Which one is appropriate? 

SELECT *
FROM TESTTABLE1 TBL1
INNER JOIN TESTTABLE2 TBL2 ON TBL2.ID = '123'
WHERE TBL2.APP_ID = TBL1.ID;

SELECT *
FROM TESTTABLE1 TBL1
INNER JOIN TESTTABLE2 TBL2 ON TBL2.APP_ID = TBL1.ID
WHERE TBL2.ID = '123';

Please give explanations, thank you ;)

Vishan Karthik
  • 11
  • 3

2 Answers2

4

The ON clause is intended to define the criteria for joining two tables and the WHERE clause is intended to apply additional filters to the result set of the join. This clarity of intention is one of the benefits of the ANSI 92 syntax.

The second query conforms to this intention and is easy to read..

On the other hand the first query throws away that clarity. It has the filter in the ON clause and the join criteria in the WHERE clause. It is harder to understand because it doesn't conform to the expected way of things. Reading such a statement trips us up, because now we're trying to figure out why you wrote the query like that instead of the normal way.

So don't do this. Regardless of whether it produces the same results as the second query there is never a good reason to deliberately write confusing code.

APC
  • 144,005
  • 19
  • 170
  • 281
1
SELECT *
FROM TESTTABLE1 TBL1
INNER JOIN TESTTABLE2 TBL2 ON TBL2.ID = '123'
WHERE TBL2.APP_ID = TBL1.ID;

The first will return an id and its lines, if any, for id number '123'. 

SELECT *
FROM TESTTABLE1 TBL1
INNER JOIN TESTTABLE2 TBL2 ON TBL2.APP_ID = TBL1.ID
WHERE TBL2.ID = '123';

The second will return all ids, but only id '123' will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Fahmi
  • 37,315
  • 5
  • 22
  • 31