LEFT JOIN/JOIN/OUTER APPLY/CROSS APPLY的比较

建表

CREATE TABLE [dbo].[Customers](
    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [customerid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

向表一插入数据：

insert into dbo.Customers values('FISSA','Madrid');
insert into dbo.Customers values('FRNDO','Madrid');
insert into dbo.Customers values('KRLOS','Madrid');
insert into dbo.Customers values('MRPHS','Zion');
查询所插入的数据：

select * from dbo.Customers
结果如图：

再建表二（[dbo].[Orders] 字段说明：orderid – 订单id ， customerid – 消费者id）：

CREATE TABLE [dbo].[Orders](
    [orderid] [int] NOT NULL,
    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
    [orderid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

向表二插入数据：

insert into dbo.Orders values(1,'FRNDO');
insert into dbo.Orders values(2,'FRNDO');
insert into dbo.Orders values(3,'KRLOS');
insert into dbo.Orders values(4,'KRLOS');
insert into dbo.Orders values(5,'KRLOS');
insert into dbo.Orders values(6,'MRPHS');
insert into dbo.Orders values(7,null);

查询插入的数据：
select * from dbo.orders

LEFT JOIN/JOIN/OUTER APPLAY/CROSS APPLAY的比较

select * from dbo.Customers cus 
select * from dbo.orders
--left join
select * from dbo.Customers cus 
left join dbo.orders ord on cus.customerid=ord.customerid
--join 
select * from dbo.Customers cus 
join dbo.orders ord on cus.customerid=ord.customerid
--outer apply
select * from dbo.Customers as cus
outer apply
(select * from dbo.Orders as ord
where cus.customerid=ord.customerid
) as T
--cross apply
select * from dbo.Customers as cus
cross apply
(select  * from dbo.Orders as ord
where cus.customerid=ord.customerid
) as T