Can I Use a CASE Statement in a JOIN Condition?

Can the CASE statement be used in JOIN conditions?

When trying to join tables based on the value of a column in the sys.allocation_units table, the following code produces a syntax error:

<code class="language-sql">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 </code>

Explanation of error reason:

CASE expression returns a single value, not an equality expression. In this example, the result of the CASE expression needs to be compared to a constant (such as 1) to determine whether the condition is true.

Corrected code:

<code class="language-sql">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</code>

This code compares the result of the CASE expression with 1 and returns the row where the condition is true.

The above is the detailed content of Can I Use a CASE Statement in a JOIN Condition?. For more information, please follow other related articles on the PHP Chinese website!