什么是左联，右联一般的书上好象没有讲！！！！！哪位能解释一下

[jspONE]

谢谢！！！！！！！！！！！！！！！！！！

[lynx1111]

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

FULL [OUTER]

Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows usually returned by the INNER JOIN.

Note It is possible to specify outer joins as specified here or by using the old nonstandard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.

LEFT [OUTER]

Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER]

Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.


INNER

指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。

FULL [OUTER]

指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。

说明 按此处指定的方法指定外联接或在 WHERE 子句中使用旧式非标准的 *= 和 =* 运算符都是可行的。不能在同一语句中同时使用这两种方法。

LEFT [OUTER]

指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为 NULL。

RIGHT [OUTER]

指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为 NULL。


declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)

--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b

[jspONE]

.

[berlin68]

多谢，楼上兄弟，讲的比较全面

[sinocat]

我刚刚学会，现在告诉你把

select * from aaa a , bbb b where a.a (+) = b.a 左

select * from aaa a , bbb b where a.a = b.a (+) 右

[软猫克鲁]

告诉你一个简单的记忆方法吧，加号在哪边那个表就是附加的。所选的字段如果没有则为空。也就是结果中必须有没有加号那个表中的字段。

[beckhambobo]

select * from aaa a , bbb b where a.a (+) = b.a 相当于 a right join b on a.a = b.a

select * from aaa a , bbb b where a.a = b.a (+) 相当于 a left join b on a.a = b.a

[gaobh]

楼上的兄弟讲的是sqlserver把
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带'+',则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.
select a.empno from emp a where a.empno not in
(select empno from emp1 where job='SALE');

---- 倘若利用外部联接,改写命令如下:

select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job='SALE';