/*--网上找一些有趣的题目，做为新生的培训题目。
--select left('claro',2) 2009-01-16 23:56:35.903 整理于西安
--Microsoft SQL Server 2005 - 9.00.3042.00
-- (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005
--Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
有两表a和b，前两字段完全相同：(id int,name varchar(10)...)，都有下面的数据（当然还有其它字段，这里不列出来了）：
id name
----------- ----------
1 a
2 b
3 c
以下的查询语句，你知道它的运行结果吗？：
1.
select * from a left join b on a.id=b.id where a.id=1
2.
select * from a left join b on a.id=b.id and a.id=1
3.
select * from a left join b on a.id=b.id and b.id=1
4.
select * from a left join b on a.id=1
5.
select * from a left join b on a.id=1 where a.id=1
*/
use tempdb
if object_id('a') is not NULL
drop table a
go
create table a (id int identity, [name] varchar(10) )
go
insert into a
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'
go
if object_id('b') is not NULL
drop table b
go
create table b (id int identity, [name] varchar(10) )
go
insert into b
select 'a' union all
select 'b' union all
select 'd'
--1.
select * from a left join b on a.id=b.id where a.id=1
/*
id name id name
1 a 1 a
*/
--2.
select * from a left join b on a.id=b.id and a.id=1
/*
id name id name
1 a 1 a
2 b NULL NULL
3 c NULL NULL
4 d NULL NULL
*/
--3.
select * from a left join b on a.id=b.id and b.id=1
/*
id name id name
1 a 1 a
2 b NULL NULL
3 c NULL NULL
4 d NULL NULL
*/
--4.
select * from a left join b on a.id=1
/*
id name id name
1 a 1 a
1 a 2 b
1 a 3 d
2 b NULL NULL
3 c NULL NULL
4 d NULL NULL
*/
--5.
select * from a left join b on a.id=1 where a.id=1
/*
id name id name
1 a 1 a
1 a 2 b
1 a 3 d
*/
--6.
select * from a cross join b where a.id=1
/*
id name id name
1 a 1 a
1 a 2 b
1 a 3 d
*/
