3

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.

Improve this question
2

6 Answers 6

Reset to default
3

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

A left/right join is an outer join. Do you mean full outer join when you say outer join?
Won't a full outer join be affected the same way as a left/right join?
2

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.

Improve this answer

1 Comment

I got the same query plan but it makes no much sense to me. Ill try to better understand the query plan. Im in doubt about what to use because sometimes (left joins) you cant use the filter clause outside the join (in this case you do a inner join).
2

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
Improve this answer

Comments

1

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

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 :)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.