SQL JOIN 共七种

没有搞懂它前,似懂非懂的,东拼西凑,也能写出程式.
但搞懂它,更知道自己在写什么.
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