34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE a(aid INT IDENTITY(1,1),
col1 VARCHAR(10))
CREATE TABLE b(bid INT IDENTITY(1,1),
col2 VARCHAR(10))
INSERT a SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
INSERT b SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'
SELECT a.*,b.* FROM a LEFT JOIN b ON a.col1=b.col2
SELECT a.*,b.* FROM b RIGHT JOIN a ON a.col1=b.col2
/*
aid col1 bid col2
----------- ---------- ----------- ----------
1 a 1 a
2 b 2 b
3 c NULL NULL
(3 row(s) affected)
aid col1 bid col2
----------- ---------- ----------- ----------
1 a 1 a
2 b 2 b
3 c NULL NULL
(3 row(s) affected)
*/
CREATE TABLE L(ID INT,NAME VARCHAR(6))
CREATE TABLE R(ID INT,NAME VARCHAR(6))
INSERT INTO L(ID,NAME)VALUES(1,'A')
INSERT INTO L(ID,NAME)VALUES(2,'B')
INSERT INTO L(ID,NAME)VALUES(3,'C')
INSERT INTO L(ID,NAME)VALUES(3,'M')
INSERT INTO R(ID,NAME)VALUES(3,'D')
INSERT INTO R(ID,NAME)VALUES(4,'E')
INSERT INTO R(ID,NAME)VALUES(5,'F')
SELECT L.*,R.* FROM L LEFT JOIN R ON L.ID=R.ID
SELECT L.*,R.* FROM L RIGHT JOIN R ON L.ID=R.ID
SELECT L.*,R.* FROM L full outer JOIN R ON L.ID=R.ID
/*
ID NAME ID NAME
----------- ------ ----------- ------
1 A NULL NULL
2 B NULL NULL
3 C 3 D
3 M 3 D
(4 行受影响)
ID NAME ID NAME
----------- ------ ----------- ------
3 C 3 D
3 M 3 D
NULL NULL 4 E
NULL NULL 5 F
(4 行受影响)
ID NAME ID NAME
----------- ------ ----------- ------
1 A NULL NULL
2 B NULL NULL
3 C 3 D
3 M 3 D
NULL NULL 4 E
NULL NULL 5 F
(6 行受影响)
*/