-1

I have seen two school of thoughts, and have read that that Joining in the where clause is faster.

Normal Join

SELECT
    TaskID,
    ProjectID
FROM 
    Task
INNER JOIN 
    Project ON Task.ProjectID = Project.ProjectID

JOIN in the WHERE clause:

SELECT
    TaskID,
    ProjectID
FROM 
    Task, Project
WHERE 
    Task.ProjectID = Project.ProjectID

I have personally always used INNER JOIN but have come across the WHERE method. Is one method faster than the other, and if so can you explain why that is?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kalimantan
  • 702
  • 1
  • 9
  • 28
  • If that is so can you please redirect me to that quesiton? – Kalimantan Sep 21 '18 at 03:58
  • 1
    Do what is most readable, makes the most logical sense and BENCHMARK! – Mitch Wheat Sep 21 '18 at 03:59
  • do your own testing? "Recently come across the where method" in the history of SQL the "where method" occurred first, but this is no longer considered best practice. Stay with the newer method. – Paul Maxwell Sep 21 '18 at 04:06
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Sep 21 '18 at 21:14
  • Your comment ignores most of the comment it responds to. Also, I cannot see how you can think "but not the one above" since it begins "Is there any difference (performance, best-practice, etc...) between putting a condition in the JOIN clause vs. the WHERE clause?" – philipxy Sep 21 '18 at 21:24
  • My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 – philipxy Sep 21 '18 at 21:26

1 Answers1

2

Neither; the database handles them identically internally. Please use modern ansi INNER/OUTER/CROSS JOIN syntax; the other way was heavily used in the 70s and 80s but was deprecated in favour of the more consistent and self explanatory modern standard in the early 90s

See also Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

Caius Jard
  • 72,509
  • 5
  • 49
  • 80