Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Conditional Joins in SQL Server

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(DefaultRates.Rate, DeptRates.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:

Print | posted on Tuesday, April 03, 2007 12:15 PM | Filed Under [ T-SQL Report Writing Joins/Relations ]

Feedback

Gravatar

# 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
4/4/2007 4:33 PM | Jason Kohlhoff
Gravatar

# 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 ....
4/4/2007 4:43 PM | Jeff
Gravatar

# 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.
4/5/2007 10:36 AM | Jason
Gravatar

# 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
4/18/2007 6:26 AM | David MArtin
Gravatar

# 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.
4/18/2007 8:28 AM | Jeff
Gravatar

# re: Conditional Joins in SQL Server

not so clear
4/20/2007 2:40 AM | abc
Gravatar

# re: Conditional Joins in SQL Server

This helped a lot. Thanks!!!
6/14/2007 7:54 AM | afca
Gravatar

# Conditional Joins in SQL Server

Good help..
7/13/2007 2:20 AM | Ajai Sharma
Gravatar

# re: Conditional Joins in SQL Server

good information
7/25/2007 2:18 AM | nitin
Gravatar

# 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
8/14/2007 11:48 AM | Mickey
Gravatar

# 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.
9/26/2007 6:14 PM | Ian Skinner
Gravatar

# 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
10/28/2007 9:23 PM | Rick F
Gravatar

# 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 ?
10/29/2007 10:25 AM | Jeff
Gravatar

# re: Conditional Joins in SQL Server

Good Help
12/24/2007 6:01 AM | Sweetu
Gravatar

# re: Conditional Joins in SQL Server

Hi

restrat system
8/25/2008 4:41 PM | Ramsaran
Gravatar

# re: Conditional Joins in SQL Server

Great, Thanks ;)
9/15/2008 12:15 PM | shay
Gravatar

# re: Conditional Joins in SQL Server

nice topic
1/17/2009 12:42 AM | rajesh
Gravatar

# 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?
2/3/2009 1:24 PM | kanu
Gravatar

# re: Conditional Joins in SQL Server

@kanu,
Take a look at using an outer join instead of an inner join.
2/10/2009 2:00 PM | Bob
Gravatar

# re: Conditional Joins in SQL Server

错误的语法
2/16/2009 5:45 AM | CYF
Gravatar

# re: Conditional Joins in SQL Server

Conditional Joins in SQL Server
Good Help



thanks
4/23/2009 1:26 AM | Bhanu Prakash
Gravatar

# re: Conditional Joins in SQL Server

Good Info....................... Tks
5/15/2009 2:58 AM | KG
Gravatar

# re: Conditional Joins in SQL Server

hallo i wish you verry succes operator
5/22/2009 9:10 PM | Edencity Chat
Gravatar

# 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



10/27/2009 4:19 PM | What about Performance
Gravatar

# 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 ....
10/27/2009 5:23 PM | Jeff Smith
Gravatar

# 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).





10/30/2009 10:58 AM | Noms
Gravatar

# 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.
10/30/2009 5:42 PM | Jeff Smith
Gravatar

# 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?
11/11/2009 9:35 AM | Matthew
Gravatar

# 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
12/5/2009 6:57 PM | RogerDodge
Gravatar

# re: Conditional Joins in SQL Server

BAD
1/14/2010 4:08 AM | fcsdfwsf
Gravatar

# 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
1/14/2010 9:17 PM | abhi
Gravatar

# re: Conditional Joins in SQL Server

Thanks Jeff, just what I was after.
1/23/2010 10:42 PM | will s
Gravatar

# 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?
3/17/2010 11:03 AM | Raed
Gravatar

# 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)
4/16/2010 1:06 PM | Marc
Gravatar

# re: Conditional Joins in SQL Server

thank you!!! This is what i was looking for.
4/19/2010 7:32 PM | ilikecoding
Gravatar

# 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?
5/5/2010 2:50 PM | MyItGuy
Gravatar

# 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??
6/28/2010 5:47 AM | slowfish
Gravatar

# 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?
7/14/2010 5:45 AM | BigFish
Gravatar

# 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?
9/22/2010 5:12 PM | Karl
Gravatar

# re: Conditional Joins in SQL Server

Good information
9/23/2010 11:09 AM | Wilfredo
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET