SQL语句JOIN

最新推荐文章于 2022-12-05 23:25:10 发布

C++业余爱好者

最新推荐文章于 2022-12-05 23:25:10 发布

阅读量140

点赞数

分类专栏：数据库文章标签： sql 数据库

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

版权

数据库专栏收录该内容

50 篇文章 2 订阅

订阅专栏

–人员表参照SQL官方教程

CREATE TABLE [dbo].[Persons](
	[Id_P] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[Address] [varchar](50) NULL,
	[City] [varchar](50) NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[Id_P] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

—订单表

CREATE TABLE [dbo].[Orders](
	[Id_O] [varchar](50) NOT NULL,
	[OrderNo] [varchar](50) NULL,
	[Id_P] [varchar](50) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[Id_O] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

–人员数据

Id_P	LastName	FirstName	Address	City
1	张三	 				张三				河北			河北
2	李四					李四				天津	   		天津	
3	王五	                王五	            北京	        北京

—订单数据

Id_O	OrderNo	Id_P
1			吉利			3
2			比亚迪		3
3			长安			1
4			奇瑞			1
5			上汽			65

--WHERER INNER JOIN  JOIN   根据左表跟右表所有数据  列出关联项
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 
--JOIN 同INNER JOIN效果一样 等同于where
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

LEFT JOIN   取出左表所有数据  并列出同右表的关联项
 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

RIGHT JOIN     取出右表所有的数据 并列出与左表的关联项
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。
 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName