CODEWITHSUNDEEP
sqljoinwhere-clause
Frank_Sma
Frank_Sma

Reputation: 37

Multiple columns in where clause SQL

I was wondering about the efficiency of a couple of different queries. The task is to pull multiple tables together that must be equal from multiple columns different. I am curious about what is the best way to approach this from an efficiency standpoint.

I have already checked this out, but it doesn't say anything about multiple column where clauses SQL WHERE.. IN clause multiple columns

and this solution shows doesn't comment on the efficiency or best practices of the solution, and doesn't include a solution where the final query is a join from the two tables Two columns in subquery in where clause

select ID, col1, col2, col3 
from table1 a
left join 
    (select ID, col1, col2, col3 from table2) b on a.col1 = b.col1
where a.col2 = b.col2
  and a.col3 = b.col3

or

select ID, col1, col2, col3 
from table1 a
left join 
    (select ID, col1, col2, col3
     from table2) b on a.col1 = b.col1
                    and a.col2 = b.col2
                    and a.col3 = b.col3

Upvotes: 0

Views: 1632

Answers (1)

DRapp
DRapp

Reputation: 48169

You do not need to do a join on a sub-select. You were very close on the second sample query. Because of the join based on 3 columns, I would make sure that the second table also has a single index using all 3 columns for optimal performance. Ex: Index on ( col1, col2, col3 ), and not 3 individual indexes, one for each column.

Also, try not to use aliases like a, b, c, unless it really correlates to the name of your table like "Accounts a", "Business b", "Customers c". Use an alias on your table references such as the abbreviation more closely matches its source.

select 
      t1.ID, 
      t1.col1, 
      t1.col2, 
      t1.col3,
      t2.WhatColumnFromSecondTable,
      t2.AnotherColumnFromTable2,
      t2.AnythingElse
   from 
      table1 t1
         left join table2 t2
            on t1.col1 = t2.col1
           and t1.col2 = t2.col2
           and t1.col3 = t2.col3

Then, if you are only looking for specific things within, you would add a WHERE clause to further filter your data down.

Upvotes: 1

Related Questions