Can I use CASE statement in a JOIN condition?

问题:

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:

SELECT  *
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
               END 

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

Thanks!

---

Add error message:

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

回答1:

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

SELECT  * 
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.)

回答2:

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

e.g.

    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 =
            CASE
               WHEN a.type IN (1, 3)
                   THEN  p.hobt_id 
               WHEN a.type IN (2)
                   THEN p.partition_id
               END 

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.

回答3:

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)

回答4:

I think you need two case statements:

SELECT  *
FROM    sys.indexes i
    JOIN sys.partitions p
        ON i.index_id = p.index_id 
    JOIN sys.allocation_units a
        ON 
        -- left side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id
               WHEN a.type IN (2)
                   THEN a.container_id
            END 
        = 
        -- right side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN p.hobt_id
               WHEN a.type IN (2)
                   THEN p.partition_id
            END             

This is because:
- the CASE statement returns a single value at the END
- the ON statement compares two values
- your CASE statement was doing the comparison inside of the CASE statement. I would guess that if you put your CASE statement in your SELECT you would get a boolean \'1\' or \'0\' indicating whether the CASE statement evaluated to True or False

回答5:

This seems nice

https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition

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

回答6:

Here I have compared the difference in two different result sets. Hope this might be helpful.

SELECT main.ColumnName, compare.Value PreviousValue,  main.Value CurrentValue
FROM 
(
    SELECT \'Name\' AS ColumnName, \'John\' as Value UNION ALL
    SELECT \'UserName\' AS ColumnName, \'jh001\' as Value UNION ALL
    SELECT \'Department\' AS ColumnName, \'HR\' as Value UNION ALL
    SELECT \'Phone\' AS ColumnName, NULL as Value UNION ALL
    SELECT \'DOB\' AS ColumnName, \'1993-01-01\' as Value UNION ALL
    SELECT \'CreateDate\' AS ColumnName, \'2017-01-01\' as Value UNION ALL
    SELECT \'IsActive\' AS ColumnName, \'1\' as Value
) main
INNER JOIN
(
    SELECT \'Name\' AS ColumnName, \'Rahul\' as Value UNION ALL
    SELECT \'UserName\' AS ColumnName, \'rh001\' as Value UNION ALL
    SELECT \'Department\' AS ColumnName, \'HR\' as Value UNION ALL
    SELECT \'Phone\' AS ColumnName, \'01722112233\' as Value UNION ALL
    SELECT \'DOB\' AS ColumnName, \'1993-01-01\' as Value UNION ALL
    SELECT \'CreateDate\' AS ColumnName, \'2017-01-01\' as Value UNION ALL
    SELECT \'IsActive\' AS ColumnName, \'1\' as Value
) compare
ON main.ColumnName = compare.ColumnName AND
CASE 
    WHEN main.Value IS NULL AND compare.Value IS NULL THEN 0
    WHEN main.Value IS NULL AND compare.Value IS NOT NULL THEN 1
    WHEN main.Value IS NOT NULL AND compare.Value IS NULL THEN 1
    WHEN main.Value <> compare.Value THEN 1
END = 1 

回答7:

I took your example and edited it:

SELECT  *
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 p.hobt_id 
           WHEN a.type IN (2)
               THEN p.partition_id
           ELSE NULL
           END = a.container_id