肯定有区别,基表不一样 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 行受影响) */
楼上几位 都没看清楼主的题目呀 楼主是这个意思, 用*的话是有区别但也只是列序的区别,分开指定结果如下 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) */
结果一样的! 他笑估计他也没看清 虽然 a left join b 但 在使用right join a,b表位置也换了,负负得正 b right join a 结果还是以A表作为基表
左联接 返回包括左表中的所有记录和右表中联结字段相等的记录
右联接 返回包括右表中的所有记录和左表中联结字段相等的记录http://topic.csdn.net/u/20100126/14/5bba5ec8-0aaf-4f94-b832-fb8fd6039f8a.html 下#5有示例的楼主可以去研究研究
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 行受影响)
*/
用*的话是有区别但也只是列序的区别,分开指定结果如下
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)
*/
他笑估计他也没看清
虽然 a left join b
但 在使用right join a,b表位置也换了,负负得正 b right join a
结果还是以A表作为基表