条件JOIN语句SQL Server [英] Conditional JOIN Statement SQL Server

问题描述

是否可以执行以下操作:

Is it possible to do the following:

IF [a] = 1234 THEN JOIN ON TableA 
ELSE JOIN ON TableB

如果是，正确的语法是什么?

If so, what is the correct syntax?

推荐答案

我认为您需要的是将 Initial 表加入 Option_A 和 Option_B (使用LEFT JOIN)，将产生如下内容:

I think what you are asking for will work by joining the Initial table to both Option_A and Option_B using LEFT JOIN, which will produce something like this:

Initial LEFT JOIN Option_A LEFT JOIN NULL
OR
Initial LEFT JOIN NULL LEFT JOIN Option_B

示例代码:

SELECT i.*, COALESCE(a.id, b.id) as Option_Id, COALESCE(a.name, b.name) as Option_Name
FROM Initial_Table i
LEFT JOIN Option_A_Table a ON a.initial_id = i.id AND i.special_value = 1234
LEFT JOIN Option_B_Table b ON b.initial_id = i.id AND i.special_value <> 1234

完成此操作后，您将忽略" NULLS集.此处的另一个技巧是在SELECT行中，您需要在其中决定如何处理NULL字段.如果Option_A和Option_B表相似，则可以使用COALESCE函数返回第一个NON NULL值(根据示例).

Once you have done this, you 'ignore' the set of NULLS. The additional trick here is in the SELECT line, where you need to decide what to do with the NULL fields. If the Option_A and Option_B tables are similar, then you can use the COALESCE function to return the first NON NULL value (as per the example).

另一种选择是，您只需要列出Option_A字段和Option_B字段，然后让使用ResultSet的内容来确定要使用的字段即可.

The other option is that you will simply have to list the Option_A fields and the Option_B fields, and let whatever is using the ResultSet to handle determining which fields to use.

这篇关于条件JOIN语句SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！