SQL 服务器在连接位置子句上添加默认值(如果不存在记录)

Question

我有以下SQL服务器表设置

table1.Id
table1.Name
table2.Id
table2.Type

table1和table2之间存在 1 对多关系，例如，table2中可以有许多记录链接到table1中的单个记录。

我现在有以下数据集：

table1  
Id  Name
1   Name1
2   Name2
table2  
Id  Type
1   1
1   2
2   1

我现在运行以下查询：

SELECT *
FROM table1 t
LEFT JOIN table2 l
ON t.Id = l.Id
WHERE l.Type = 2

此查询将返回以下内容：

table1      table2  
Id  Name    Id  Type
1   Name1   1   2

我实际上要实现的是一个将返回以下内容的查询：

table1      table2  
Id  Name    Id  Type
1   Name1   1   2
2   Name2   1   DEFAULTVALUE

我声明默认值的地方。

我知道如果我删除WHERE子句，查询将返回 3 行，但我想排除所有类型不是 2 的记录，如果不存在记录，我想添加一个默认条目。

我想我可能需要为此使用案例，但无法确切地看到如何做到这一点。

我希望我的问题清楚吗？

Answer 1

如果你的条件在left join表中，你可以把它放在left join子句中

SELECT  ISNULL(l.Type, 'Default Value') as Type
FROM table1 t
LEFT JOIN table2 l
ON t.Id = l.Id and l.Type = 2     

Answer 2

你能试试这个吗？ (L.ID 将是 NULL ，而不是预期输出中的 1)：

SELECT T.ID, T.NAME, L.ID, COALESCE(L.TYPE, 'DEFAULTVALUE') AS TYPE
FROM table1 t
LEFT JOIN (SELECT * FROM table2 WHERE Type = 2) l ON t.Id = l.Id

Answer 3

您可以使用CASE语句来设置默认值。可以独立于数据运行以下示例进行测试：

CREATE TABLE #table1 ( id INT, name VARCHAR(10) );
CREATE TABLE #table2 ( id INT, [type] INT );
INSERT  INTO #table1
( id, name )
VALUES  ( 1, 'Name1' ),
( 2, 'Name2' );
INSERT  INTO #table2
( id, type )
VALUES  ( 1, 1 ),
( 1, 2 ),
( 2, 1 );
DECLARE @TYPE INT = 2 ,
@DEFAULT INT = 99;
SELECT  t.id ,
t.name ,
l.id ,
CASE WHEN l.type = @TYPE THEN l.type
ELSE @DEFAULT
END AS [Type]
FROM    #table1 t
LEFT JOIN #table2 l ON t.id = l.id
WHERE   t.id IN ( SELECT    id
FROM      #table2
WHERE     type = @TYPE );
DROP TABLE #table1;
DROP TABLE #table2;