Reputation:
I can produce the same result with the where clause inside the join or the where clause outside the join, like:
select *
from a
join b on b.id = a.id and b.type = 1
or
select *
from a
join b on b.id = a.id
where
b.type = 1
Whats better? Why?
ps: feel free to edit my title, i dont know how to proper name the question.
Upvotes: 3
Views: 131
Reputation: 62564
For INNER JOIN
this does not make any difference from neither perfromance nor returned results set perspectives. But for OUTER (LEFT/RIGHT/FULL) JOIN
result set would be different since WHERE clause filtering rows returned by the JOIN and in case of OUTER (LEFT/RIGHT/FULL) JOIN
"nullable" entries could be returned so results set would be wider. (Obviously for large data sets condition in OUTER (LEFT/RIGHT/FULL) JOIN
could operate faster)
Upvotes: 3
Reputation: 66757
Using the father Sybase ASE 15.0
and with no statistics on both tables:
First query
STEP 1 The type of query is SELECT. FROM TABLE b Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. FROM TABLE a Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages.
Second query
STEP 1 The type of query is SELECT. FROM TABLE b Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. FROM TABLE a Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages.
Both queries have the same execution plan so they will take the same time :)
Upvotes: 0
Reputation: 2755
You can use the Query Execution Plan (on SQL Management Studio) to verify that this query and many others execute in the same way.
Execution plans are the greatest tool to learn to write best queries.
Upvotes: 1
Reputation: 700910
It doesn't matter when the result is the same. The database will create the same execution plan for both queries.
If you use an outer join there is a difference in the result. This will limit the records that are joined:
select *
from a
left join b on b.id = a.id and b.type = 1
This will however limit the records that are returned, effectively turning the left join into an inner join, but most likely with worse perfromance:
select *
from a
left join b on b.id = a.id
where b.type = 1
Upvotes: 2
Reputation: 23179
We can't really say which one is better in general. The performance depends on your data, your indexes and the way the query optimizer processes the query. To really tell which is better, you should run an Explain Plan on both. This will give you concrete information on which is better for this specific case.
Upvotes: 1
Reputation: 11908
The SQL Server Query Governor will compile it to exactly the same query plan.
I prefer the second form because it is more readable. You more clearly separate join conditions from filter conditions.
Upvotes: 2