Conditional Joins in SQL Server

Tue Apr 3, 2007 by Jeff Smith in t-sql, report-writing, joins-relations

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort, so that the join processed will vary for each row depending on the data. For example, some rows join to table A, others to table B. You might think to do this with either a CASE expression or with some OR boolean logic in your JOIN expression. Often, you might encounter syntax errors, performance problems, or results not being returned the way you expect when trying this. There's a much better way to approach the problem.

Instead of trying to alter a single INNER JOIN's relation from row to row, instead use multiple LEFT OUTER JOINS in your SELECT, one for each table or condition that you need to consider. With each possibility JOIN'ed separately, you can now use a CASE expression in your SELECT clause to return data from the join that you need based on conditions or the join results.

For example, if you need to join Employees to either Stores or Offices depending on where they work, many SQL beginners think that a CASE will somehow let them get the results they need:

select
E.EmployeeName
from
Employee E
inner join
case when ... ???? .. then Offices else Stores end on ... ??? ....

Even if you did manage to come up with a creative new syntax for doing "CASE JOINS" in SQL, it would not compile or execute in SQL since it is invalid. It's also the wrong approach.

Instead, you simply LEFT OUTER JOIN to both tables, and in your SELECT clause, return data from the one that matches:

select
E.EmployeeName, coalesce(s.store,o.office) as Location
from
Employees E
left outer join
Stores S on ...
left outer join
Offices O on ...

In the above, we use coalesce() to return whichever value did not came back as null; if for some reason we got matching rows in both tables, it is easy to write a CASE to handle that situation as well. The key here is that we kept it simple and clear, and we get the correct results using valid SQL.

We did, however, change the fact that two left outer joins doesn't filter the employees table the way an INNER JOIN would. If you want to return only employees that have a location (i.e., you want to inner join to either of these two tables) you would add that criteria to your WHERE clause:

select
E.EmployeeName, coalesce(s.store,o.office) as Location
from
Employees E
left outer join
Stores S on ...
left outer join
Offices O on ...
where
O.Office is not null OR S.Store is not null

Similarly, sometimes you might need to join to a single table on different expressions depending on a condition. For example, if you have a table of Billing Rates, set up like this:

EmpType Dept      Rate
------   --------- ----
A       DEFAULT   $50
A      4         $55
B        1         $25
B        2         $45
B        3         $55
B        DEFAULT   $40
C        DEFAULT   $70
C        1         $60

In the above table, notice that billing rates can be set by a combination of employee type and department, but if a rate doesn't exist for that combo, we use the "default" rate for that Employee Type. (Note that this is not really a good database design, but I've seen it often enough unfortunately. )

You might think to write something like this:

select
E.EmployeeName, B.Rate as BillingRate
from
Employee E
inner join
BillRates B on ((B.EmpType = E.EmpType and B.Department = E.Department) OR
(B.EmpType = E.EmpType and B.Department = 'DEFAULT'))

In this case, that is valid SQL and it will compile and execute. However, it is not only inefficient, but it doesn't work. For employee rows that match a specific EmpType/Dept billing rate, it will still also return another row for the default rate since the second part of the OR expression still holds. This means that you will get duplicated data with different rates in your result set. You could try to aggregate those results to return 1 row per Employee, and figure out how to pull just the billing rate that you need with a MAX(CASE ...) expression or something similar, but now you are really over-complicating things. There must be an easier way!

Well, there is, and it follows the same basic technique I described earlier. Instead, let's do two separate LEFT OUTER JOINs to the Billing Rates table, giving each copy of the BillingRates table a nice alias to indicate what it is returning:

select
E.EmployeeName, coalesce(DeptRates.Rate, DefaultRates.Rate) as BillingRate
from
Employee E
left outer join
BillRates DefaultRates on E.EmpType = DefaultRates.EmpType and
DefaultRates.Dept='DEFAULT'
left outer join
BillRates DeptRates on E.EmpType = DeptRates.EmpType and
E.Dept = DeptRates.Dept

Now, we will only get back one row per Employee, and it is clear how we are joining to the Billing Rates table in two different ways. We can add criteria to the WHERE clause to ensure that only employees with a valid, matching billing rate is returned, much in the same way as the previous example:

WHERE DefaultRates.Rate is not null OR DeptRates.Rate is not null

So, the next time you think you need an OR condition or a CASE expression in your JOIN clause, consider instead doing multiple LEFT OUTER JOINs. You'll find that your code is easier to write, returns correct results, and is also more efficient.

see also:

Legacy Comments


Jason Kohlhoff 2007-04-04	re: Conditional Joins in SQL Server Jeff, You wrote, "In the above, we use coalesce() to return whichever value did not came back as null; if for some reason we got matching rows in both tables, it is easy to write a CASE to handle that situation as well." Coalesce() already handles the case in which both tables return matching rows. Coalesce() will return the first non-null expression in the argument list. If all expressions are null, then coalesce() will return null. You probably should clarify that so you don't confuse anyone. Most developers I know don't even know that coalesce exists, let alone how to use it. ;-) Jason

Jeff 2007-04-04	re: Conditional Joins in SQL Server Thanks, Jason, I will think about re-wording it. My point was that when data comes back from both joins, you might need to use some logic to decide what to return, so you may need a CASE. For example, if you just use: COALESCE(s.store, o.office) Then if BOTH an office AND a store are returned, you'll never know, you'll just see the store name ... You may not want that. You may prefer to do something like this: CASE WHEN s.store is not null and o.office is not null THEN s.store + ', ' + o.office ELSE WHEN s.store is not null THEN s.store ELSE WHEN o.office is not null THEN o.office ELSE 'N/A' END or something along those lines ....

Jason 2007-04-05	re: Conditional Joins in SQL Server My main concern was the phrase, "return whichever value did not came back as null." To me, it doesn't imply that there is a distinct pecking order in the evaluation of the arguments. It's not an issue if you have just 2 args, but what if you have N arguments? You could do this instead of your CASE statement - COALESCE(s.store + ', ' + o.office, s.store, o.office , 'N/A') If either element is NULL, then the first argument will be null, and it does the same thing as the CASE statement.

David MArtin 2007-04-18	re: Conditional Joins in SQL Server How would you do it if you like to do something like SELECT m.SCatID,c.ItemID,n.ItemTimeStamp,s.ItemName,s.Note,s.Price,u.UserMail,p.Hits, coalesce(a.,x.) from ItemClass c join Classes as m on (m.ClassID = c.ClassID) join ItemList as l on (l.ItemID = c.ItemID) join NewItems as n on (n.ItemID= l.ItemID) join dUsers as u on (u.UserID= l.ItemUserID) join Posts as p on (p.ItemID = l.ItemID) join SuitAllDes as s on(s.DescriptID = l.ItemID) left outer join AutoDes a on (a.DescriptID = c.ItemID) left outer join HomeDes x on (x.DescriptID = c.ItemID) where c.ItemID = 278 im trying to join a table depending on the SCatID the ItemID has mainly my items have 3 Description TAbles, one that suits all, one for Cars and another for Homes. as you wrote it works for one value, what about the whole table. Thanks in advance

Jeff 2007-04-18	re: Conditional Joins in SQL Server David - What youa are trying to do doesn't make logical sense .... a single SELECT statement cannot contain a varying number of columns and datatypes depending on some condition, and it certainly cannot vary for each row in the data! You need to explicitly define what columns you wish to return, and if each column needs to be like this: case when a.ItemID is null then x.ColumnName else a.ColumnName end then that's what you need to do. Alternatively, you can just return all columns from both table x and table a and at your presentation layer decide which set of columns to return in your output for each row. It all depends on ultimately what you are trying to do. Hope this makes sense.

abc 2007-04-20	re: Conditional Joins in SQL Server not so clear

afca 2007-06-14	re: Conditional Joins in SQL Server This helped a lot. Thanks!!!

Ajai Sharma 2007-07-13	Conditional Joins in SQL Server Good help..

nitin 2007-07-25	re: Conditional Joins in SQL Server good information

Mickey 2007-08-14	re: Conditional Joins in SQL Server Hi, I am not an expert in Joins. I can write simple SQL queries. Would appreciate if someone could help me write and understand this query. Here is a simplified scenario: 2 Source Tables: Table Name Fields =========== ======= Failed Subjects Name of Student Subject Date of Test Name of Subject(failed) Master Subjects Name of Student Subject enrolled I need to write a SQL Query that shows - Date of Test Name of Student Subjects Enrolled Name of Failed Subject ========= ============ ============ ================ If the student has passed the test for a subject, it should appear as 'Passed!' in the "Name of Failed Subject Field" Thanks in advance Mickey

Ian Skinner 2007-09-26	re: Conditional Joins in SQL Server Can this be applied to doing a outer join between two tables with a multi-part (4) foreign key but the last part is optional? FROM pur75raw pur, product pro WHERE pur.mfg_firmno = pro.mfg_firmno(+) AND pur.label_seq_no = pro.seq_no(+) AND pur.revision_no = pro.revision_no(+) AND (IF pur.reg_firmno IS NOT NULL THEN pur.reg_firmno = pro.reg_firmno(+)) -- This pseudo code or anything like it I have tried does not work NOTE: The use of sql92 join syntax since this is against an older version of Oracle and it does not understand the modern FROM ... INNER JOIN ... ON ... = ... syntax.

Rick F 2007-10-28	re: Conditional Joins in SQL Server when i try to understand something i will read what is being said through the first time without much analyzing. Then i will read it over again until I can understand what is being said. I am reading all the comments regarding better ways, or different ways or problems with the way it was explained. I really don't know what is right or wrong now... so, maybe so others don't get as confused as I, someone that really knows the correct way to handle a situation like this step in and post it but delete all that is here now. Thank you

Jeff 2007-10-29	re: Conditional Joins in SQL Server >>I really don't know what is right or wrong now... so, maybe so others don't get as confused as I, someone that really knows the correct way to handle a situation like this step in and post it but delete all that is here now. What are you confused about? What situation ?

Sweetu 2007-12-24	re: Conditional Joins in SQL Server Good Help

Ramsaran 2008-08-25	re: Conditional Joins in SQL Server Hi restrat system

shay 2008-09-15	re: Conditional Joins in SQL Server Great, Thanks ;)

rajesh 2009-01-17	re: Conditional Joins in SQL Server nice topic

kanu 2009-02-03	re: Conditional Joins in SQL Server I created 2 tables --one with files that got promos and other with file that got emails. I joined these 2 table on filenum to create a kind of crosstab with # of promos to # of emails with count of filenums (join on filenum). Problem is due to join on filenum, this is not capturing data where count of promo filenum is null but not count of email filenum. Example: promo count of filenum is 0 but count of email filenum is say 1. any suggestions?

Bob 2009-02-10	re: Conditional Joins in SQL Server @kanu, Take a look at using an outer join instead of an inner join.

Bhanu Prakash 2009-04-23	re: Conditional Joins in SQL Server Conditional Joins in SQL Server Good Help thanks

KG 2009-05-15	re: Conditional Joins in SQL Server Good Info....................... Tks

Edencity Chat 2009-05-22	re: Conditional Joins in SQL Server hallo i wish you verry succes operator

What about Performance 2009-10-27	re: Conditional Joins in SQL Server Jeff, This is a very nice technique indeed, but not very good on performance. Performance may not matter that much to some user depending on what they are trying to do but for me database is one of the key elements for getting good performance from your application. Rather than using the CASE statement and LEFT outer joins just use IF ELSE statements instead e.g. DECLARE @TYPE INT = 1 IF @TYPE = 1 BEGIN select E.EmployeeName, s.store as Location from Employees E INNER join Stores S on ... END ELSE BEGIN select E.EmployeeName, o.office as Location from Employees E INNER join Offices O on ... END

Jeff Smith 2009-10-27	re: Conditional Joins in SQL Server "What about performance" -- what you have wrote has absolutely nothing to do with the situation or SQL presented in the article. Read it again ....

Noms 2009-10-30	re: Conditional Joins in SQL Server "what you have wrote has absolutely nothing to do with the situation or SQL presented in the article. Read it again .... " I think i understant it very clearly. You are clearly advising people to use multiple outer joins WHEN the joins need to be based on some sort of condition. AND All i am saying is that they are poor on performance. Because you are unnecessarily joining tables that may not need to be joined. Hence you are better off with an IF ELSE statement (assuming you are doing that in a Stored Proc).

Jeff Smith 2009-10-30	re: Conditional Joins in SQL Server Hi Noms -- again, no; you are not writing code that solves the issue stated in the post. EACH ROW independently might need to a join to table A or table B; it is not determined before the query is executed, it is determined on a row-by-row basis. i.e., there is no "@Type" parameter, where did you get that from? You are writing code for a different problem than what the article is describing. I hope this makes sense.

Matthew 2009-11-11	re: Conditional Joins in SQL Server Can you use CASE in the "ON" clause of the join? I only have 1 table to join, so that's not a problem, but which columns are joined is the variable. Can I use CASE in the ON clause?

RogerDodge 2009-12-05	re: Conditional Joins in SQL Server Jeff, This approach has allowed me to combine 3 seperate queries and avoided the headache of managing the combining of the results of the 3 queries. Many thanks for this helpful posting

fcsdfwsf 2010-01-14	re: Conditional Joins in SQL Server BAD

abhi 2010-01-14	re: Conditional Joins in SQL Server Hi guys very interesting topic goin on.. i think.. ok i have two tables say A and B i have used inner join to get some columns from both tables. say: A.COMPSEQ A.MNC A.BILLO B.SHIPTO B.ADDRESSCODE ................ 0 ------ PRIMARY WAREHOUSE 713 APTC NJ... 0 ----- DITO WAREHOUSE 713 \PTC NJ 1 ---- DITO WAREHOUSE EMPTYFIELD FOR COMPSEQ = 0 I HAVE VALUES FOR ADRESSCODE FORCOMPSEQ<>0 I DONT HAVE ANY VALUES IT WILL BE EMPTY SO NOW I WANT MY RESULT SET TO GET THE VALUES OF ADRESCODE FOR COMPSEQ <>0 O BE SET SAME AS FOR COMPSEQ=0 I WANT THE TABLE TO LOOK LIKE THIS 1 ---- PRIMARY WAREHOUSE 713 APTC NJ ANY GUESS.. IF YOU NEED SOME MORE DETAILS I WILL GET BACK TO YOU BUT IF YOU CAN FIGURE OUT THEN PLEASE TELL ME

will s 2010-01-23	re: Conditional Joins in SQL Server Thanks Jeff, just what I was after.

Raed 2010-03-17	re: Conditional Joins in SQL Server i have a situation when results return before left outer join Table A ID DESC AMT 3877 CC-3877 100.000 3877 CC-3877 50.000 3878 CC-3878 100.000 3878 CC-3878 120.000 after left outer join table b and included Person column from table b result are duplicated like this result after left outer join with b ON A.TransID = B.TransID that contains Person and TransID DESC AMT Person 3877 CC-3877 100.000 P1 3877 CC-3877 50.000 P2 3877 CC-3877 100.000 P2 3877 CC-3877 50.000 P1 3878 CC-3878 100.000 C1 3878 CC-3878 120.000 C2 3878 CC-3878 100.000 C2 3878 CC-3878 120.000 C1 what's wrong here and how to handle the extra records?

Marc 2010-04-16	re: Conditional Joins in SQL Server Thanks for the article, combining your left outer join approach to an approach I was using elsewhere I came up with the following which works great. You can do the following, note the bit parameter that acts as the control variable in the where clause. The joins can be on anything, mine just happens to be a function returning a table with one column of data. The key to this conditional join is what I do in the where clause and how that matches up with their relative joins. declare @userID int declare @getForTeam set @userID = 54 set @getForTeam = 1 SELECT J.journalID, J.title, J.dateCreated FROM Journal J --join for the Journals that this client has access to but does not own LEFT OUTER JOIN getAssignedParents('Journal', @userID, 'Client') JtoU ON J.journalID = JtoU.itemID --join for the Journals that this client created/owns himself (implied access) LEFT OUTER JOIN getAssignedChildren('Client', @userID, 'Journal') UtoJ ON J.journalID = UtoJ.itemID WHERE (@getForTeam = 0 AND J.journalID = UtoJ.itemID) OR (@getForTeam <> 0 AND J.journalID = JtoU.itemID)

ilikecoding 2010-04-19	re: Conditional Joins in SQL Server thank you!!! This is what i was looking for.

MyItGuy 2010-05-05	re: Conditional Joins in SQL Server Looking for a little help on a related query problem; I have two tables. The first holds location specific info such as store hours and the second holds the data. Location_Table Data_Table Location (varchar) Location(varchar) Sat_open(int) Date(datetime) Sat_close(int) Hour(int) Sun_open(int) Data(int) Sun_close(int) Mon_open(int) etc... I need to select the data for a period for all locations but only data where the location was open. Open and close hours change for each day of the week and are different for each location. How do I write the join for this? Can antbody help?

slowfish 2010-06-28	re: Conditional Joins in SQL Server Hi You mention about writing a CASE to get round the situation where you have matching rows in both tables after using coalesce(). I'm not quite sure how to do this. In an "orders" table, a column contains the either the id of a person or the id of business - ie. its never null. Another column "type" states either "pers" or "bus". I want to join the persons table if type is pers, join the business table if type is bus. How would I go about this? Is it also then possible to order the returned data by name??

BigFish 2010-07-14	re: Conditional Joins in SQL Server How about using UNION. ie, instead of wrting two left outer join, use UNION? Which one will be more efficient?

Karl 2010-09-22	re: Conditional Joins in SQL Server What about a case where you want to use a field in Table A to determine if you JOIN on the table in database B or C where it IS possible for that ID to join to either B or C? Example: You want to SELECT the User.CompanyID and the corresponding Company.Description. All the data you have is the UserID and the table to join. (Yes I know it is odd to have two databases with the same table names, but that is not something that I can change) For this example, we know that our CompanyID is 1, and the DBName is 'DBOne', so we want to select "CompanyOne". How do you use the fact that you know the DBName is DBOne in your JOIN? Data: DB.dbo.User CompanyID Name DBName 1 Amy DBOne 2 Bob DBOne 1 Chris DBTwo 2 David DBTwo DBOne.dbo.Company CompanyID Description 1 CompanyOne DBTwo.dbo.Company CompanyID Description 1 CompanyTwo Like this, but not this... SELECT User.CompanyID, comp.Description FROM User INNER JOIN CASE WHEN User.DBName = 'DBOne' THEN DBOne.dbo.Company comp ON User.CompanyID = DBOne.dbo.CompanyID ELSE DBTwo.dbo.Company comp ON User.CompanyID = DBTwo.dbo.CompanyID WHERE User.CompanyID = 1 AND User.DBName = DBOne' Yes, I know this query will fail on syntax. Is there a way to pull this data in one query without running a select and storing the value in a variable and then IF-ELSEing your two separate queries based on DB?

Wilfredo 2010-09-23	re: Conditional Joins in SQL Server Good information