-3

I have this SQL query. It runs on the same table and creates a dataset with three columns. My concern is whether I should Use 'WHERE' clause or 'AND'. I have this feeling that 'AND' will be more efficient and faster than 'WHERE'. Please advise. 1st one is with 'WHERE' and 2nd one is without it.

  1. SELECT DISTINCT t1.rpsrespondent AS id_num,t1.rpscontent AS PID, t2.RpsContent AS SeqNo
    FROM table t1 INNER JOIN table t2 ON t1.rpsrespondent=t2.rpsrespondent INNER JOIN table t3 ON t3.rpsrespondent=t1.rpsrespondent WHERE t1.RpsQuestion='PID' AND t2.RpsQuestion = 'VISITID' AND t3.rpsquestion ='INT99' AND t3.rpscontent in('36','37')

  2. SELECT DISTINCT t1.rpsrespondent AS id_num,t1.rpscontent AS PID, t2.RpsContent AS SeqNo
    FROM table t1 INNER JOIN table t2 ON t1.rpsrespondent=t2.rpsrespondent AND t1.RpsQuestion='PID' AND t2.RpsQuestion = 'VISITID' INNER JOIN table t3 ON t3.rpsrespondent=t1.rpsrespondent AND t3.rpsquestion ='INT99' AND t3.rpscontent in('36','37')

user3381574
  • 35
  • 4
  • 1
    Use the modern JOIN ON syntax, not the antiquated, implementation -dependent and somewhat flaky SQL-86 syntax. (Yes, that old syntax is **really** old, and obsolete for over 20 years now.) – Pieter Geerkens Mar 16 '15 at 21:57
  • Sure, will do that. Thanks! – user3381574 Mar 16 '15 at 22:14
  • There will be no difference. Check the query plans (CTRL-L) - I bet they're identical. There is a _functional_ difference if there is an outer join involved but this is not the case. – Nick.Mc Mar 16 '15 at 22:42
  • I just checked the plans and they are identical. Thanks Nick for your input. – user3381574 Mar 17 '15 at 13:08

1 Answers1

1

With an INNER JOIN, it makes no difference if predicates are specified in the JOIN or WHERE clauses. The queries are semantically identical so the SQL Server optimizer should generate the same (optimal) executional plan. You will get the same performance as a result.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71