数据库知识点02

该文章所用数据库仅供参考，语法适用所有数据库。

连接表

在数据库中，数据分布在多个逻辑表中。要获得完整的有意义的数据集，需要将这些表连接来查询表中的数据。

SQL Server支持多种连接，包括 [内连接]，[左连接]，[右连接]，[全外连接]和[交叉连接]。

每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行。

A. SQL Server内连接

内连接是SQL Server中最常用的连接之一。 内部联接子句用于查询来自两个或多个相关表的数据。

select * from production.products p

inner join production.brands b

on p.brand_id = b.brand_id

 

使用两个 INNER JOIN 子句来查询三个表中的数据：

SELECT

product_name,

category_name,

brand_name,

list_price

FROM

production.products p

INNER JOIN production.categories c ON c.category_id = p.category_id

INNER JOIN production.brands b ON b.brand_id = p.brand_id

ORDER BY

product_name DESC;

B. SQL Server左连接

LEFT JOIN 子句用于查询来自多个表的数据。

它返回左表中的所有行和右表中的匹配行。

如果在右表中找不到匹配的行，则使用 NULL 代替显示。

以下图说明了两个结果集的左连接结果：

 

---查询未销售给任何客户的产品

select * from production.products t1

left join sales.order_items t2

on t1.product_id = t2.product_id

order by t1.product_id

可以使用 WHERE 子句来过滤结果集。

以下查询返回未出现在任何销售订单中的产品：

select * from production.products t1

left join sales.order_items t2

on t1.product_id = t2.product_id

where t2.item_id is null

order by t1.product_id

C. ON与WHERE子句

以下查询查找属于订单ID为 100 的产品：

select * from production.products t1

left join sales.order_items t2

on t1.product_id = t2.product_id

where t2.order_id = 100

order by t1.product_id

如果将条件 order_id = 100 移动到 ON 子句：

select * from production.products t1

left join sales.order_items t2

on t1.product_id = t2.product_id and t2.order_id = 100

order by t2.order_id

请注意，对于 INNER JOIN 子句，如果将 ON 子句中的条件放在 WHERE 子句中，则它在功能上是等效的

D. SQL Server右连接

RIGHT JOIN 子句组合来自两个或多个表的数据。 RIGHT JOIN 开始从右表中选择数据并与左表中的行匹配。 RIGHT JOIN 返回一个结果集，该结果集包含右表中的所有行，无论是否具有左表中的匹配行。如果右表中的行没有来自右表的任何匹配行，则结果集中右表的列将使用 NULL 值。

 

示例：

select * from sales.order_items t2

right join production.products t1

on t1.product_id = t2.product_id and t2.order_id = 100

order by t2.order_id

E. SQL Server 交叉连接

CROSS JOIN 连接两个或多个不相关的表。

select * from sales.customers c

cross join sales.orders o

where c.customer_id = o.customer_id;

交叉连接返回的是两个表的笛卡尔积。

与INNER JOIN或LEFT JOIN不同，交叉连接不会在连接的表之间建立关系。

如果第一个表有n行，第二个表有m行，则交叉连接将会产生n x m 行。

F. SQL Server自连接

自联接用于将表连接到自身(同一个表)。 它对于查询分层数据或比较同一个表中的行很有用。

自联接使用内连接或左连接子句。 由于使用自联接的查询引用同一个表，因此表别名用于为查询中的表分配不同的名称。

请注意，如果在不使用表别名的情况下在查询中多次引用同一个表，则会出现错误。

示例：

--查询David的经理叫什么

select manager_id

from [sales].staffs

where last_name = 'David'

--1

select * from sales.staffs

where staff_id = 1

select manager_id

from [sales].staffs

where last_name = 'Houston'

--7

select last_name from sales.staffs

where staff_id = 7;

select m.manager_id, 经理id, m.last_name 经理名称,

e.last_name 员工名称

from sales.staffs e

inner join sales.staffs m

on e.manager_id = m.manager_id

and e.last_name = 'Houston'

select m.staff_id, 经理id, m.last_name 经理名称,

e.last_name 员工名称

from sales.staffs e

inner join sales.staffs m

on e.manager_id = m.staff_id

and e.last_name = 'Houston'

select e.last_name,e.staff_id,m.last_name,m.staff_id

from sales.staffs e

inner join sales.staffs m

on e.manager_id = m.staff_id

and e.last_name = 'Houston'

G. SQL Server全外链接

 

FULL OUTER JOIN当左表或右表中存在匹配项时，该命令将返回所有行。

下面创建一些示例表来演示全外连接。

首先，创建一个名为 pm 的新模式，它代表项目管理。

CREATE SCHEMA pm; 

GO

接下来，在 pm 模式中创建名为 projects 和 members 的新表：

CREATE TABLE pm.projects(    

     id INT PRIMARY KEY IDENTITY,    

     title VARCHAR(255) NOT NULL );

CREATE TABLE pm.members(    

     id INT PRIMARY KEY IDENTITY,    

     name VARCHAR(120) NOT NULL,    

     project_id INT,    

     FOREIGN KEY (project_id)         

          REFERENCES pm.projects(id) );

假设每个成员只能参与一个项目，每个项目都有零个或多个成员。 如果项目处于构思阶段，则不会分配任何成员。然后，向 projects 和 member 表中插入一些行记录：

INSERT INTO     

     pm.projects(title) 

VALUES    

     ('New CRM for Project Sales'),    

     ('ERP Implementation'),    

     ('Develop Mobile Sales Platform');

INSERT INTO    

     pm.members(name, project_id) 

VALUES    

     ('John Doe', 1),    

     ('Lily Bush', 1),    

     ('Jane Doe', 2),    

     ('Jack Daniel', null);

之后，查询 projects 和 member 表中的数据：

SELECT * FROM pm.projects; 

SELECT * FROM pm.members;

最后，使用 FULL OUTER JOIN 查询 projects 和 member 表中的数据：

select * from pm.projects p

full outer join pm.members m

on p.id = m.project_id