CODEWITHSUNDEEP
sqlsql-servert-sql
user989818
user989818

Reputation:

performance related to join and where

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

Answers (6)

sll
sll

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

aF.
aF.

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

Adilson de Almeida Jr
Adilson de Almeida Jr

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

Guffa
Guffa

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

Chris
Chris

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

Filip De Vos
Filip De Vos

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

Related Questions