2
SELECT *
FROM TableOne
INNER JOIN TableTwo ON TableOne.ForeignKeyID = TableTwo.PrimaryKeyID
WHERE TableTwo.SomeColumnOne = 12345;

Or

SELECT *
FROM TableOne
INNER JOIN TableTwo ON TableOne.ForeignKeyID = TableTwo.PrimaryKeyID
AND TableTwo.SomeColumnOne = 12345;

I prefer the first method since JOIN tell us how the two tables should be bound together whereas WHERE tell us how to filter the result set. But is there any performance difference between the two ? Or any other reason why we should prefer one over the other ?

Thanks in advance!

Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
Shubham
  • 780
  • 3
  • 13
  • 32
  • possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Sadikhasan Jul 10 '14 at 07:34
  • @zxc: the difference in the queries is the first one uses a WHERE clause, whilst the second one adds the extra condition into the ON clause with an AND. I don't know about MySQL in particular, but would expect most databases to treat them the same for INNER JOINS in terms of functionality and performance. – mc110 Jul 10 '14 at 07:42
  • @Sadikhasan It is not a duplicate. The question that you have shared is about whether or not we should use `INNER JOIN`. But the question that I'm asking is how the query conditions (which are not acting on PK Columns) should be specified when THERE IS an `INNER JOIN` between the tables. – Shubham Jul 10 '14 at 08:07

1 Answers1

5
SELECT *
FROM TableOne
INNER JOIN TableTwo ON TableOne.ForeignKeyID = TableTwo.PrimaryKeyID
WHERE TableTwo.SomeColumnOne = 12345;

here the WHERE clause is applicable on the result of INNER JOIN

whereas

SELECT *
FROM TableOne
INNER JOIN TableTwo ON TableOne.ForeignKeyID = TableTwo.PrimaryKeyID
AND TableTwo.SomeColumnOne = 12345;

in second case the INNER JOIN will be performed with filtered result of TableTwo after the AND condition.

Sid M
  • 4,354
  • 4
  • 30
  • 50
Archana
  • 376
  • 2
  • 12
  • 3
    While theoretically correct, any decent optimizer will convert these queries to be the same. – fancyPants Jul 10 '14 at 07:46
  • @Archana So if we assume that the query optimizer will not convert the queries to the same one, it is better to use the second query since this leads to a `JOIN` on smaller result, right ? – Shubham Jul 10 '14 at 08:12
  • 1
    @Archana: can you show any evidence of the second version being better, as I concur with the earlier comment that most/all databases will execute these in exactly the same way, unless their optimiser was written from scratch over a weekend. – mc110 Jul 10 '14 at 08:55
  • @mc110 I think she meant that second will be better if we assume that the optimizer doesn't convert them to the same query. – Shubham Jul 10 '14 at 10:20