sqlserver之on与where条件

挺特别的,on后边的条件以前我只写一个关联的id 之类的,后来发现还可以加条件,比如左表 用户表,右表用户订单表,订单有很多,可以加上时间范围列出某天订单.

很多人会说加where 就可以了,如果还要列出当日没下单的用户呢,where明显就不够了,其实在on后边再加个时间条件就行了,  left join 大家都知道,左侧表数据是不管有没有关联上是会保留的

但加了where 条件除外.

产品表

ProNo ProName Price Stocks
00001 电视 3000.00 800
00002 空调 2000.00 500
00003 床 1000.00 300
00004 餐桌 1500.00 200
00005 音响 5000.00 600
00006 沙发 6000.00 100
00007 窗帘 500.00 100

产品入库表

InputDate    ProNo    Quantity
2006-01-01 00:00:00.000    00001    10
2006-01-01 00:00:00.000    00002    5
2006-01-02 00:00:00.000    00001    5
2006-01-02 00:00:00.000    00003    10
2006-01-03 00:00:00.000    00001    10
2006-02-01 00:00:00.000    00003    20
2006-02-02 00:00:00.000    00001    10
2006-02-03 00:00:00.000    00003    20
2006-02-03 00:00:00.000    00004    30
2020-12-24 16:48:02.043    00001    10
2020-12-24 16:48:04.887    00001    10
2020-12-24 16:48:12.933    00002    10
2020-12-24 16:48:14.527    00002    10
2020-12-24 16:48:20.747    00003    10
2020-12-24 16:48:22.287    00003    10
2020-12-24 16:48:29.710    00004    10
2020-12-24 16:48:31.043    00004    10
2020-12-24 16:48:48.683    00004    10
2020-12-24 16:48:57.200    00005    10
2020-12-24 16:48:58.353    00005    10
2020-12-24 16:48:59.060    00005    10
2020-12-24 16:48:59.863    00005    10
2020-12-24 16:49:06.040    00006    10
2020-12-24 16:49:06.500    00006    10

select  *  from Product left join ProIn on Product.ProNo=ProIn.ProNo  and ProIn.InputDate>'20201223'

   select  *  from Product left join ProIn on Product.ProNo=ProIn.ProNo  where ProIn.InputDate>'20201223'

 null没了 

看一下 sql  实行顺序

查一下MSDN就清楚了整个SQL的执行顺序.

http://msdn.microsoft.com/en-us/library/ms189499(v=SQL.100).aspx

Processing Order of the SELECT statement
The following steps show the processing order for a SELECT statement.

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP BY

6.WITH CUBE or WITH ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER BY

11.TOP

 

也就是说, 先进行on的过滤, 而后才进行join, 这样就避免了两个大表产生全部数据的笛卡尔积的庞大数据.