Can I use CASE statement in a JOIN condition?

inner join with case statement sql server
case statement in sql multiple joins
sql case join different tables
case statement in join performance
if else condition in sql join
sql join on case statement
case statement in join clause oracle
sql conditional join based on parameter

The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together I would write something similar to this:

FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id = p.hobt_id 
               WHEN a.type IN (2)
                   THEN a.container_id = p.partition_id

But the upper code gives a syntax error. I guess that's because of the CASE statement. Can anyone help to explain a little?

Add error message:

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

Can I use CASE statement in a JOIN condition?, We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an  You should move CASE statement into the SELECT body: SELECT DISTINCT VWL.Type, VWL.ID AS CompanyID,VWL.Name case VWL.Type when 'COMPANY' then CE.Address when 'PERSON' then PE.Address end FROM dbo.LeadList_New AS VWL LEFT OUTER JOIN CRMCompanyEmailAddress as CE on VWL.ID=CE.CRMCompanyID LEFT OUTER JOIN PersonEmailAddress as PE on VWL.ID=PE.PersonID

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

I suggest you to go through this link Conditional Joins in SQL Server and T-SQL Case Statement in a JOIN ON Clause


    SELECT  *
FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON a.container_id =
               WHEN a.type IN (1, 3)
                   THEN  p.hobt_id 
               WHEN a.type IN (2)
                   THEN p.partition_id

Edit: As per comments.

You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.

Conditional Column Join in SQL - DEV Community ‍ ‍ , IS there any way I can use the below. Select X.* From TABLE X. INNER JOIN. (​case when (condition) then TABLE A else TABLE B end) AS Y. I would like to join T1 and T2 in this condition: 1. when T1.sub_service is not null, then join with T2.type 2. when T1.sub_service is null, then use T1.service to join with T2.type SELECT T1.service, T1.sub_service, T2.type FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T2.type LIKE CASE WHEN T1.sub_service IS NULL THEN

Try this:

...JOIN sys.allocation_units a ON 
  (a.type=2 AND a.container_id = p.partition_id)
  OR (a.type IN (1, 3) AND a.container_id = p.hobt_id)

Case Statement in Join condition – SQLServerCentral, Not sure if you have tried using Left Join instead of inner join. You can put an expression in the report cell to check for the valid value from either  Case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By and Group By clause. It can be used in Insert statement as well.

If or case statement to determine a join – SQLServerCentral, The 'conditional join', can be executed but at great cost. columns in a second table, choosing the JOIN column based on a condition. tables (likely as LEFT OUTER JOINs) and use CASE statements to pull the specific data  The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

This seems nice

FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom =  DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable

How to Avoid Conditional JOINs in T-SQL, I'm using Oracle. I've got Or do I need to use a UNION. Joining on a CASE statement is never ideal, because it does not allow SQL to utilize  Coincidentally, you can put a case statement on both sides of the = sign like so: where case (boolean) then (expression) else (expression) end = case (boolean) then (expression) else (expression) end. As long as both sides of the operator have valid syntax., This SQL tutorial for data analysis covers using if/then logic with the SQL CASE statement. CASE can include multiple conditions and be used with aggregate  >>>Them to compound your ignorance, you think that a CASE expression is a control flow statement! <<< I am not sure. that is why i am asking whether we can use CASE statement in the SELECT query to dynamically change the condition in the WHERE clause based on some values fetched from other query. I am sure you can suggest a better approach for

Join on case statement? : SQL, condition that you need to consider. With each possibility JOIN'ed separately, you can now use a CASE expression in your SELECT clause to  Microsoft Access Select Case syntax. The Select Case statement is used to control the flow of statements depending on the value of the control expression (variable) and the comparative conditions specified by the Case keyword. The script for Select Case in MS Access is: Select Case some_expression Case condition_1 result_1 Case condition_2


  • What software did you use to make this beautiful DB diagram?
  • @LearnByReading did you ever find out which software was used?
  • @User632716 no unfortunately not!
  • @User632716 Although I really think it was MySQL Workbench. But I never received a response
  • @LearnByReading Nice, thanks
  • @HABO thanks that worked for me...but the problem is when i do this the conditions make a fall through...please tell me how do i break it?
  • @SagarTandel - Sorry, I don't understand "make a fall through" and "how do I break it". Could you clarify your comment? (Recently surfaced from a dive off Saba. Might be the Nitrox.)
  • It checks for all of the conditions which i don't want. I want it to quit the case once it matches a condition.
  • @SagarTandel - From MSDN: "The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.". If you want all of the joined rows that don't match any of the explicitly stated conditions, just change the tail end from = 1 to = 0, but I don't think you'll like the result.
  • JOIN sys.allocation_units a ON CASE WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1 WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1 ELSE 0 END = 1 could you write it in Entity framework the above of your solution
  • what conditional join means? Each join (excluding cross) is a conditional. How this case is different from any other? Your sample has inner join with condition, as well as OPs query has join with condition.
  • @zerkms: I agree, it sounds confusing. I believe, a conditional join in this context means a join whose condition depends on another condition.
  • @Andriy M: any reason to think of another term for trivial condition with OR?