昨天看了一下 join 的用法,
给大家测试一下。
写出你认为的结果,执行查询,看跟你想的是否一样。
---------------------------------------
--SQL 2005
---------------------------------------
--> 测试数据: @ta
declare @ta table (id int,a_col int)
insert into @ta
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 3,null union all
select 4,4 union all
select null,5
--> 测试数据: @tb
declare @tb table (id int,b_col int)
insert into @tb
select 1,1 union all
select 2,2 union all
select null,10-----------------------------------------------查询1
SELECT * FROM @ta A
JOIN @tb B
ON A.id=1
---查询2
SELECT * FROM @ta A
LEFT JOIN @tb B
ON A.id=1
--SQL 2005
---------------------------------------
--> 测试数据: @ta
declare @ta table (id int,a_col int)
insert into @ta
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 3,null union all
select 4,4 union all
select null,5
--> 测试数据: @tb
declare @tb table (id int,b_col int)
insert into @tb
select 1,1 union all
select 2,2 union all
select null,10-----------------------------------------------查询1
SELECT * FROM @ta A
JOIN @tb B
ON A.id=1
---查询2
SELECT * FROM @ta A
LEFT JOIN @tb B
ON A.id=1
/*id a_col id b_col
----------- ----------- ----------- -----------
1 1 1 1
1 1 2 2
1 1 NULL 10(3 行受影响)id a_col id b_col
----------- ----------- ----------- -----------
1 1 1 1
1 1 2 2
1 1 NULL 10
2 2 NULL NULL
3 3 NULL NULL
3 NULL NULL NULL
4 4 NULL NULL
NULL 5 NULL NULL(8 行受影响)*/
SELECT * FROM (select * from @ta where id=1) A
cross JOIN @tb B
--ON A.id=1