沒有搞懂它前,似懂非懂的,東拚西湊,也能寫出程式.
但搞懂它,更知道自己在寫什麼.
key word : sql join

A與B的配對遊戲
inner join 配對到的A和B都站出來
left join A都站出來,有配對到的帶著B出來.接著我們也可以再把有配對到的給踢掉.
right join B都站出來,有配對到的帶著A出來.接著我們也可以再把有配對到的給踢掉.
FULL OUTER JOIN A,B都站出來.接著我們可以把有配對到的給踢掉.
以上是概念,以下是語法實作

DROP TABLE #TMP1,#TMP2 
CREATE TABLE #TMP1(ACNO INT)
CREATE TABLE #TMP2(ACNO INT)

INSERT INTO #TMP1 SELECT 1
INSERT INTO #TMP1 SELECT 2
INSERT INTO #TMP1 SELECT 3

INSERT INTO #TMP2 SELECT 3
INSERT INTO #TMP2 SELECT 4

--1.娶老婆配對.不管有沒有娶到,都把A隊列出來.一個個有沒有娶到.
SELECT * FROM #TMP1 T1 LEFT JOIN #TMP2 T2 ON T1.ACNO =T2.ACNO
--2.娶老婆配對.A隊有娶到的,請離開.
SELECT * FROM #TMP1 T1 LEFT JOIN #TMP2 T2 ON T1.ACNO = T2.ACNO WHERE T2.ACNO IS NULL

--3.娶老婆配對.A隊.B隊都站出來.有娶到的,站在同一排.
SELECT * FROM #TMP1 T1 FULL OUTER JOIN #TMP2 T2 ON T1.ACNO = T2.ACNO
--4.娶老婆配對.A隊.B隊都站出來,有娶到的,都出去.
SELECT * FROM #TMP1 T1 FULL OUTER JOIN #TMP2 T2 ON T1.ACNO = T2.ACNO WHERE T2.ACNO IS NULL OR T1.ACNO IS NULL

--5.娶老婆配對,B隊站出來,有對應的,請離開
SELECT * FROM #TMP1 T1 RIGHT JOIN #TMP2 T2 ON T1.ACNO = T2.ACNO WHERE T1.ACNO IS NULL
--6.娶老婆配對,B隊站出來,有對應到的,請站在他旁邊.
SELECT * FROM #TMP1 T1 RIGHT JOIN #TMP2 T2 ON T1.ACNO = T2.ACNO

--7.開放式關係,大家一起愛起來~不再有人落單.
SELECT * FROM #TMP1,#TMP2