CODEWITHSUNDEEP
sqlt-sql
GenDemo
GenDemo

Reputation: 761

Best practice when using calculations and joins

I have a general quesiton about best practice when one has a SQL query, and you want to use joins/sub queries to calculate measures, which are then to be used in business rules. I got taught two spesific ways, but as my career progresses and I met more people, they seem to prefer other methods of doing this.

below, I've written out 5 examples of what I'm referring to. Please note that they are not meant to be working SQL queries, simply examples to show the different methods.

--Example 1:  filtering in-line
select top 1
 from [Temp Data] base
 left join table1 t1    on base.key = t1.key
                        and t1.category = 1
                        and t1.status not in ('Voided', 'Deleted')

--Example 2: using where
select top 1
 from [Temp Data] base
 left join table1 t1    on base.key = t1.key
 where t1.category = 1
    and t1.status not in ('Voided', 'Deleted')


--Example 3: sub-query
select top 1
 from [Temp Data] base
 left join (select col1
                from table1 t1  
                where category = 1
                    and status not in ('Voided', 'Deleted')
                ) t1 on base.key = t1.key


--Example 4: if exists
select top 1
        ,case
            when exists (select col1
                            from table1 t1  
                            where category = 1
                                and status not in ('Voided', 'Deleted')
                                and base.key = t1.key
                        ) then 1
          else 0
         end
 from [Temp Data] base


 --Example 5: outer apply
 select top 1
 from [Temp Data] base
 outer apply (select col1
                from table1 t1  
                where category = 1
                    and status not in ('Voided', 'Deleted')
                    and base.key = t1.key
                ) t1

I'd like to hear people's opinions on the different methods.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The queries do different things. So, you should choose the form that does what you want.

As an example, the second query turns the left join into an inner join.

The fourth one does no filtering at all, just setting a flag.

The first, third, and fifth are equivalent. I think the first method would be the more "traditional" way for writing the query -- or at least I think more people would write it like that. The subquery in (3) is superfluous (although no harm to performance). And cross apply is not supported by all databases.

Upvotes: 2

Related Questions