CODEWITHSUNDEEP
sqldb2
user2946211
user2946211

Reputation: 1

Which query would be better query?

I am applying joins on more than two tables and below is part of where clause. Out of following two which is better way of writing it and why??

where
    d.bu_id     =   'ABC'
and
    hd.bu_id    =   'ABC'
and 
    s.bu_id     =   'ABC'

OR

where
    d.bu_id     =   'ABC'
and
    hd.bu_id    =   d.bu_id
and 
    s.bu_id     =   d.bu_id

Upvotes: 0

Views: 75

Answers (2)

StyxUT
StyxUT

Reputation: 26

I think the best solution is to declare a variable to hold the bu_id you are looking for, then join hd, s, and d together on bu_id. This will allow you to change the bu_id in just one location should it ever be necessary. Adding an index on the bu_id fields should ensure good performance. 

DECLARE @bu_id CHAR(3) = 'ABC'

SELECT
    *
FROM
    table_d d
    INNER JOIN table_hd hd on hd.bu_id = d.bu_id
    INNER JOIN table_s s on s.bu_id = d.bu_id
WHERE
    d.bu_id = @bu_id

Upvotes: 1

couchand
couchand

Reputation: 2649

The best solution really depends on your expected change patterns. If all three are the same because they really do refer to the same entity, it seems more likely that they would all change together. In that case you'd want to go with your second option to keep from repeating yourself.

If they just happen to have the same value, it's more likely that one of them will change independently in the future, so you'll save yourself hassle by keeping it in the first form.

If you're wondering about performance, there isn't much difference, as any query optimizer should be able to figure both of them out.

Upvotes: 2

Related Questions