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

最新推荐文章于 2025-02-19 19:44:01 发布

吾何信仰何

最新推荐文章于 2025-02-19 19:44:01 发布

阅读量699

点赞数

分类专栏： .NET CORE 数据库 JAVA 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/ChengR666/article/details/121624213

版权

数据库同时被 3 个专栏收录

50 篇文章

订阅专栏

JAVA

39 篇文章

订阅专栏

.NET CORE

29 篇文章

订阅专栏

本文详细介绍了如何在SQL中创建Customers和Orders表，进行数据插入，并对比展示了LEFT JOIN、JOIN、OUTER APPLY和CROSS APPLY的不同用法。通过实例演示了如何通过这些连接操作查询两个表中的相关数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表

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