What is difference in join with join keyword or join with =

Question

Possible Duplicate:
Explicit vs implicit SQL joins
In MySQL queries, why use join instead of where?

Query with = operator:-

 Select us.FirstName 
       ,uf.FileTitle 
       ,pq.Question
       ,pa.Answer from
 UserMst us,UploadedFile uf,PollQuestion pq,PollAnswer pa,PollResult pr
 where us.UserId = pr.U_id 
       and uf.FileId=pr.A_id 
       and pq.Q_id=pr.Q_id 
       and pa.A_id=pr.ans_id

Query with Join keyword:-

  Select us.FirstName,uf.FileTitle ,pq.Question,pa.Answer  from 
  PollResult pr join UserMst us on us.UserId = pr.U_id  
           join UploadedFile uf on uf.FileId = pr.A_id 
           join PollQuestion pq on pq.Q_id= pr.Q_id
           join PollAnswer pa on pa.A_id =pr.ans_id

Which one is better in execution?

Or [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — bhamby, Apr 04 '12 at 14:14
implicit joins area SQL antipattern see: http://www.amazon.com/gp/product/1934356557/ref=kinw_rke_tl_1 — HLGEM, Apr 04 '12 at 15:42

Matt Fellows · Answer 1 · 2012-04-04T14:20:14.907

In SQL 2008 it will make almost no difference. (Though I think the former method might be deprecated?)

However, in a lot of SQL implementations if each join has a condition, then the subsequent join will join on fewer values, and so be slightly faster.

If you put all your conditions at the end, then this optimisation won't occur until the end. And so you will end up with a massive list of rows which get filtered in one go, which will in theory, be slower.

So for example:

If the Table A has 1000 rows and you join to another table, B, with 1000 rows, but specify int eh join condition that you are only joining to 10 of the rows in B, you have 10 rows. If you then join to table C you will only be joing 10 rows.

If you Join A to B to C then filter, you are filtering 1000 rows (Or maybe 1,000,000 depending on the join)

So for the sake of cross implementation compatibility, and also readability, it might be best to use the latter version.

score 0 · Answer 2 · answered Apr 04 '12 at 14:15

I prefer the join I think it makes the most sense to be in the habit of always putting the join condition in the ON clause (unless it is an outer join and you actually do want it in the where clause) as it makes it clearer to anyone reading your query what conditions the tables are being joined on, and also it helps prevent the WHERE clause from being dozens of lines long. because its clean

What is difference in join with join keyword or join with =

2 Answers2