關於在SQL語句中ON和WHERE中條件使用的差異

create table customers
(
customerid char(5),
city varchar(10)
);
go
create table orders
(
orderid int,
customerid char(5)
);
go
insert into customers values('FISSA','Madrid');
insert into customers values('FRNDO','Madrid');
insert into customers values('KRLOS','Madrid');
insert into customers values('MRPHS','Zion');

insert into orders values(1,'FRNDO');
insert into orders values(2,'FRNDO');
insert into orders values(3,'KRLOS');
insert into orders values(4,'KRLOS');
insert into orders values(5,'KRLOS');
insert into orders values(6,'MRPHS');
insert into orders values(7,NULL);

select c.customerid,o.customerid,c.city,o.orderid as numorders
from customers as c
left outer join orders as o
on c.customerid=o.customerid  and c.city='Madrid'

select c.customerid,o.customerid,c.city,o.orderid as numorders
from customers as c
left outer join orders as o
on c.customerid=o.customerid  
where c.city='Madrid'

select * from customers
FISSA    Madrid
FRNDO    Madrid
KRLOS    Madrid
MRPHS    Zion

select * from orders
1    FRNDO
2    FRNDO
3    KRLOS
4    KRLOS
5    KRLOS
6    MRPHS
7    NULL

select c.customerid,o.customerid,c.city,o.orderid as numorders
from customers as c
left outer join orders as o
on c.customerid=o.customerid  and c.city='Madrid'
FISSA    NULL    Madrid    NULL
FRNDO    FRNDO    Madrid    1
FRNDO    FRNDO    Madrid    2
KRLOS    KRLOS    Madrid    3
KRLOS    KRLOS    Madrid    4
KRLOS    KRLOS    Madrid    5
MRPHS    NULL    Zion    NULL

select c.customerid,o.customerid,c.city,o.orderid as numorders
from customers as c
left outer join orders as o
on c.customerid=o.customerid  
where c.city='Madrid'
FISSA    NULL    Madrid    NULL
FRNDO    FRNDO    Madrid    1
FRNDO    FRNDO    Madrid    2
KRLOS    KRLOS    Madrid    3
KRLOS    KRLOS    Madrid    4
KRLOS    KRLOS    Madrid    5