返回介绍
文江博客 开发文档 SQL 常见面试题总结 文章详情

连接表

发布于 2024-08-17 15:49:00 字数 12608 浏览 0 评论 0 收藏 0

JOIN 是“连接”的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。 连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间 。

使用 JOIN 连接两个表的基本语法如下：

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行。您可以使用多个运算符来连接表，例如 =、>、<、<>、<=、>=、!=、 between 、 like 或者 not ，但是最常见的是使用 =。

当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名。当然，如果书写的字段名在两个表中是唯一的，也可以不使用以上格式，只写字段名即可。

另外，如果两张表的关联字段名相同，也可以使用 USING 子句来代替 ON ，举个例子：

# join....on
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
ON c.cust_id = o.cust_id
ORDER BY c.cust_name

# 如果两张表的关联字段名相同，也可以使用 USING 子句：JOIN....USING()
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name

ON 和 WHERE 的区别 ：

  • 连接表时，SQL 会根据连接条件生成一张新的临时表。 ON 就是连接条件，它决定临时表的生成。
  • WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

所以总结来说就是： SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选 。

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：

连接类型说明
INNER JOIN 内连接（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。
LEFT JOIN / LEFT OUTER JOIN 左（外) 连接返回左表中的所有行，即使右表中没有满足条件的行也是如此。
RIGHT JOIN / RIGHT OUTER JOIN 右（外) 连接返回右表中的所有行，即使左表中没有满足条件的行也是如此。
FULL JOIN / FULL OUTER JOIN 全（外) 连接只要其中有一个表存在满足条件的记录，就返回行。
SELF JOIN将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。
CROSS JOIN交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

如果不加任何修饰词，只写 JOIN ，那么默认为 INNER JOIN

对于 INNER JOIN 来说，还有一种隐式的写法，称为 “ 隐式内连接 ”，也就是没有 INNER JOIN 关键字，使用 WHERE 语句实现内连接的功能

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name;

返回顾客名称和相关订单号

Customers 表有字段顾客名称 cust_name 、顾客 id cust_id

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

Orders 订单信息表，含有字段 order_num 订单号、 cust_id 顾客 id

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

【问题】编写 SQL 语句，返回 Customers 表中的顾客名称（ cust_name ）和 Orders 表中的相关订单号（ order_num ），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等连接语法，另外一个使用 INNER JOIN。

答案：

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name,o.order_num

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name,o.order_num;

返回顾客名称和相关订单号以及每个订单的总价

Customers 表有字段，顾客名称： cust_name 、顾客 id： cust_id

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

Orders 订单信息表，含有字段，订单号： order_num 、顾客 id： cust_id

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

OrderItems 表有字段，商品订单号： order_num 、商品数量： quantity 、商品价格： item_price

order_numquantityitem_price
a1100010
a220010
a31015
a42550
a51525
a777

【问题】除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（ cust_name ）和 Orders 表中的相关订单号（ order_num ），添加第三列 OrderTotal ，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

# 简单的等连接语法
SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name, o.order_num

注意，可能有小伙伴会这样写：

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name
ORDER BY c.cust_name,o.order_num

这是错误的！只对 cust_name 进行聚类确实符合题意，但是不符合 GROUP BY 的语法。

select 语句中，如果没有 GROUP BY 语句，那么 cust_name 、 order_num 会返回若干个值，而 sum(quantity * item_price) 只返回一个值，通过 group by cust_name 可以让 cust_name 和 sum(quantity * item_price) 一一对应起来，或者说 聚类 ，所以同样的，也要对 order_num 进行聚类。

一句话，select 中的字段要么都聚类，要么都不聚类

确定哪些订单购买了 prod_id 为 BR01 的产品（二）

表 OrderItems 代表订单商品信息表， prod_id 为产品 id； Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date

OrderItems 表：

prod_idorder_num
BR01a0001
BR01a0002
BR02a0003
BR02a0013

Orders 表：

order_numcust_idorder_date
a0001cust102022-01-01 00:00:00
a0002cust12022-01-01 00:01:00
a0003cust12022-01-02 00:00:00
a0013cust22022-01-01 00:20:00

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（ cust_id ）和订单日期（ order_date ），按订购日期对结果进行升序排序。

提示：这一次使用连接和简单的等连接语法。

# 写法 1：子查询
SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01')
ORDER BY order_date

# 写法 2：连接表 inner join
SELECT cust_id, order_date
FROM Orders o INNER JOIN
  (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01') tb ON o.order_num = tb.order_num
ORDER BY order_date

# 写法 3：写法 2 的简化版
SELECT cust_id, order_date
FROM Orders
INNER JOIN OrderItems USING(order_num)
WHERE OrderItems.prod_id = 'BR01'
ORDER BY order_date

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

有表 OrderItems 代表订单商品信息表， prod_id 为产品 id； Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date ； Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id

OrderItems 表：

prod_idorder_num
BR01a0001
BR01a0002
BR02a0003
BR02a0013

Orders 表：

order_numcust_idorder_date
a0001cust102022-01-01 00:00:00
a0002cust12022-01-01 00:01:00
a0003cust12022-01-02 00:00:00
a0013cust22022-01-01 00:20:00

Customers 表代表顾客信息， cust_id 为顾客 id， cust_email 为顾客 email

cust_idcust_email
cust10cust10@cust.com
cust1cust1@cust.com
cust2cust2@cust.com

【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（ Customers 表中的 cust_email ），结果无需排序。

提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num ，中间的从 Customers 表返回 cust_id ，但是必须使用 INNER JOIN 语法。

SELECT cust_email
FROM Customers
INNER JOIN Orders using(cust_id)
INNER JOIN OrderItems using(order_num)
WHERE OrderItems.prod_id = 'BR01'

确定最佳顾客的另一种方式（二）

OrderItems 表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱， OrderItems 表有订单号 order_num 和 item_price 商品售出价格、 quantity 商品数量

order_numitem_pricequantity
a110105
a211100
a21200
a421121
a5510
a2119
a775

Orders 表含有字段 order_num 订单号、 cust_id 顾客 id

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

顾客表 Customers 有字段 cust_id 客户 id、 cust_name 客户姓名

cust_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

【问题】编写 SQL 语句，返回订单总价不小于 1000 的客户名称和总额（ OrderItems 表中的 order_num ）。

提示：需要计算总和（ item_price 乘以 quantity ）。按总额对结果进行排序，请使用 INNER JOIN 语法。

SELECT cust_name, SUM(item_price * quantity) AS total_price
FROM Customers
INNER JOIN Orders USING(cust_id)
INNER JOIN OrderItems USING(order_num)
GROUP BY cust_name
HAVING total_price >= 1000
ORDER BY total_price

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。
列表为空，暂无数据
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文