如何單獨顯示表 1 中的數據，並結合表 2 中的匹配行

我有兩張桌子，我們稱它們為t1和t2。它們是關聯的 - 中的列與c1中t2的主鍵列i1匹配t1。

對於我的結果，我希望每一行都t1沒有任何t2數據出現；然後，在以下輸出行中，我想查看該t1行和任何匹配t2行的組合數據。在 MS SQL 中這可能嗎？我該怎麼做？

Table t1:   Table t2:
+----+----+  +----+----+----+
| i1 | x  |  | i2 | c1 | c2 |
+----+----+  +----+----+----+
| 1  | a  |  | 1  | 1  |    |
| 2  | b  |  | 2  | 2  | g  |
| 3  | c  |  | 3  | 3  | h  |
| 4  | d  |  | 4  | 3  | i  |
+----+----+  +----+----+----+

Result
+----+----+----+---+----+----+
| i  | i1 | i2 | x  |c1 | c2 |
+----+----+----+---+----+----+
| 1  |  1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2  |  1 | 1  | a | 1  |NULL|
| 3  |  2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4  |  2 | 2  | b | 2  | g  |
| 5  |  3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6  |  3 | 3  | c | 3  | h  |
| 7  |  3 | 4  | c | 3  | i  |
| 8  |  4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+

注意：i輸出中的列只是一個行號。

您的問題有點令人困惑（特別是在連接方面），這看起來像您正在尋找的東西嗎？

CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));

INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO

CREATE VIEW TestView 
AS
WITH cteInitialSet AS (
   SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
   UNION ALL
   SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2 
   FROM dbo.T1
       JOIN dbo.T2 ON T1.i1 = T2.c1
       )

SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
   i1 ,
   i2 ,
   x ,
   c1 ,
   c2
FROM cteInitialSet
GO

SELECT * FROM dbo.TestView;

引用自：https://dba.stackexchange.com/questions/186955