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