Join和非Join的区别

Question

 

 

 

请各位谈谈二者的本质区别
说明
Northwind数据库 Orders,Customers,Employees三张表
第一段非Join的查询

 1 SELECT 
 2     O.OrderID 订单编号,
 3     O.OrderDate 订单日期,
 4     (E.FirstName+E.LastName) as 员工姓名,
 5     C.ContactName as 客户姓名
 6  FROM
 7     Orders as O,
 8     Employees as E,
 9     Customers as C
10  WHERE 
11     O.EmployeeID = E.EmployeeID 
12     AND O.CustomerID = C.CustomerID
13     AND O.OrderDate 
14     BETWEEN '1996-07-01' and '1996-07-15'
15  GO
16 
17 
18  

 

第二段是Join的用法

 1 SELECT 
 2     O.OrderID 订单编号,
 3     O.OrderDate 订单日期,
 4     (E.FirstName+E.LastName) as 员工姓名,
 5     C.ContactName as 客户姓名
 6 FROM
 7     Orders O JOIN Employees E
 8     ON
 9     O.EmployeeID = E.EmployeeID
10     JOIN
11     Customers C
12     ON
13     O.CustomerID = C.CustomerID
14 WHERE
15     O.OrderDate    BETWEEN '1996-07-01' and '1996-07-15'

 

显然，两种查询的结果是一致的。它们之间的本质区别在哪呢。哪个更好

Answer 1

第一种耗的内存大很多，性能也更低：因为在from 阶段：会生成一张中间临时表供 where 条件进行筛选；第一种情况，是四个表的笛卡尔集；第二种情况是四个表的笛卡尔集并使用 ON 条件进行过滤后的临时表供 where 条件进行筛选。第二种情况一般情况下要比第一种情况少很多行记录，占更少内存，性能更高。

Answer 2

波形图你还有源码吗？