对于 join, 如果不写 left ,inner ,right ,默认是什么联接呢?
来源:互联网 发布:淘宝自制摄影棚 编辑:程序博客网 时间:2024/06/06 03:27
默认是 inner join
#2. select * from a inner join b on a.id = b.id
#3. select * from a, b where a.id = b.id
上面的三个是相等的.
#4.剩下的join类型有:
left join
right join
cross join
full join
#5.2005新加
cross apply
outer apply
SQL Server 2005 新增 cross apply 和 outer apply 联接语句,增加这两个东东有啥作用呢?
我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数(返回表结果集的函数)的, 更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请,请看下面的例子:
-- 1. cross join 联接两个表select * from TABLE_1 as T1 cross join TABLE_2 as T2
-- 2. cross join 联接表和表值函数,表值函数的参数是个“常量”select * from TABLE_1 T1 cross join FN_TableValue(100)
-- 3. cross join 联接表和表值函数,表值函数的参数是“表T1中的字段”select * from TABLE_1 T1 cross join FN_TableValue(T1.column_a)Msg 4104, Level 16, State 1, Line 1The multi-part identifier "T1.column_a" could not be bound.
最后的这个查询的语法有错误。在 cross join 时,表值函数的参数不能是表 T1 的字段, 为啥不能这样做呢?我猜可能微软当时没有加这个功能:),后来有客户抱怨后, 于是微软就增加了 cross apply 和 outer apply 来完善,请看 cross apply, outer apply 的例子:
-- 4. cross applyselect * from TABLE_1 T1 cross apply FN_TableValue(T1.column_a)-- 5. outer applyselect * from TABLE_1 T1 outer apply FN_TableValue(T1.column_a)
cross apply 和 outer apply 对于 T1 中的每一行都和派生表(表值函数根据T1当前行数据生成的动态结果集) 做了一个交叉联接。cross apply 和 outer apply 的区别在于:如果根据 T1 的某行数据生成的派生表为空,cross apply 后的结果集 就不包含 T1 中的这行数据,而 outer apply 仍会包含这行数据,并且派生表的所有字段值都为 NULL。
下面的例子摘自微软 SQL Server 2005 联机帮助,它很清楚的展现了 cross apply 和 outer apply 的不同之处:
-- cross applyselect * from Departments as D cross apply fn_getsubtree(D.deptmgrid) as ST
deptid deptname deptmgrid empid empname mgrid lvl----------- ----------- ----------- ----------- ----------- ----------- ------1 HR 2 2 Andrew 1 01 HR 2 5 Steven 2 11 HR 2 6 Michael 2 12 Marketing 7 7 Robert 3 02 Marketing 7 11 David 7 12 Marketing 7 12 Ron 7 12 Marketing 7 13 Dan 7 12 Marketing 7 14 James 11 23 Finance 8 8 Laura 3 04 R&D 9 9 Ann 3 05 Training 4 4 Margaret 1 05 Training 4 10 Ina 4 1(12 row(s) affected)
-- outer applyselect * from Departments as D outer apply fn_getsubtree(D.deptmgrid) as ST
deptid deptname deptmgrid empid empname mgrid lvl----------- ----------- ----------- ----------- ----------- ----------- ------1 HR 2 2 Andrew 1 01 HR 2 5 Steven 2 11 HR 2 6 Michael 2 12 Marketing 7 7 Robert 3 02 Marketing 7 11 David 7 12 Marketing 7 12 Ron 7 12 Marketing 7 13 Dan 7 12 Marketing 7 14 James 11 23 Finance 8 8 Laura 3 04 R&D 9 9 Ann 3 05 Training 4 4 Margaret 1 05 Training 4 10 Ina 4 16 Gardening NULL NULL NULL NULL NULL(13 row(s) affected)
注意 outer apply 结果集中多出的最后一行。 当 Departments 的最后一行在进行交叉联接时:deptmgrid 为 NULL,fn_getsubtree(D.deptmgrid) 生成的派生表中没有数据,但 outer apply 仍会包含这一行数据,这就是它和 cross join 的不同之处。
下面是完整的测试代码,你可以在 SQL Server 2005 联机帮助上找到:
-- create Employees table and insert valuescreate table Employees( empid int not null, mgrid int NULL, empname varchar(25) not null, salary money not null)go-- create Departments table and insert valuescreate table Departments( deptid int not null primary key, deptname varchar(25) not null)go-- fill datasinsert into employees values(1 , NULL, 'Nancy' , 000.00)insert into employees values(2 , 1 , 'Andrew' , 00.00)insert into employees values(3 , 1 , 'Janet' , 00.00)insert into employees values(4 , 1 , 'Margaret', 00.00)insert into employees values(5 , 2 , 'Steven' , 00.00)insert into employees values(6 , 2 , 'Michael' , 00.00)insert into employees values(7 , 3 , 'Robert' , 00.00)insert into employees values(8 , 3 , 'Laura' , 00.00)insert into employees values(9 , 3 , 'Ann' , 00.00)insert into employees values(10, 4 , 'Ina' , 00.00)insert into employees values(11, 7 , 'David' , 00.00)insert into employees values(12, 7 , 'Ron' , 00.00)insert into employees values(13, 7 , 'Dan' , 00.00)insert into employees values(14, 11 , 'James' , 00.00)insert into departments values(1, 'HR', 2)insert into departments values(2, 'Marketing', 7)insert into departments values(3, 'Finance', 8)insert into departments values(4, 'R&D', 9)insert into departments values(5, 'Training', 4)insert into departments values(6, 'Gardening', NULL)go-- table-value functioncreate function dbo.fn_getsubtree(@empid AS INT) returns @TREE table( empid int not null, empname varchar(25) not null, mgrid int null, lvl int not null)asbegin with Employees_Subtree(empid, empname, mgrid, lvl) as ( -- Anchor Member (AM) select empid, empname, mgrid, 0 from employees where empid = @empid union all -- Recursive Member (RM) select e.empid, e.empname, e.mgrid, es.lvl+1 from employees as e join employees_subtree as es on e.mgrid = es.empid ) insert into @TREE select * from Employees_Subtree returnendgo-- cross apply queryselect * from Departments as D cross apply fn_getsubtree(D.deptmgrid) as ST-- outer apply queryselect * from Departments as D outer apply fn_getsubtree(D.deptmgrid) as ST
- 对于 join, 如果不写 left ,inner ,right ,默认是什么联接呢?
- 对于 join, 如果不写 left ,inner ,right ,默认是什么联接呢?
- Oracle inner join、left join、right join的联接查询
- MySQL| MySQL联接(INNER JOIN,LEFT JOIN,RIGHT JOIN)
- left join(左联接)right join(右联接) inner join(等值连接) 区别
- join联接查询--SQL中 inner join、 left join 、right join、 outer join之间的区别
- inner/left/right join
- mysql的left join和right join和inner join等表联接详解
- inner join left right full +
- left join /right join/inner join区别
- mysql left join right join inner join
- left join/right join/inner join
- left join,right join,inner join
- inner join/left join/right join
- 数据库:left join,right join,inner join
- Left join , Right Join, Inner Join 用法
- left join & right join $inner join ......
- INNER JOIN, LEFT JOIN & RIGHT JOIN
- 策略引擎-jboss Drools Planner
- IAR编译警告:the order of volatile accesses is undefined
- ArrayList、LinkedList和HashSet、TreeSet以及HashMap、TreeMap是如何实现存储的?
- linux网络配置命令
- C++读写文件
- 对于 join, 如果不写 left ,inner ,right ,默认是什么联接呢?
- 外观模式:有序?无序?
- Asterisk命令MeetMe详解
- Win7制作Wi-Fi热点
- android屏幕截图
- (Oracle函数备份)Oracle函数列表
- oracle登录报12560:TNS:协议适配器出错的问题
- 现在买房人几个月后又会去砸售楼处
- javascript获取日期