mysql join查询

博客主要总结了MySQL中join语句的7种用法。先创建tb_emp和tb_dept两张表并插入测试数据,介绍了交叉连接、内连接等概念。详细阐述了inner join、left join等用法,还提到因MySQL不支持full outer join,需用union转换实现AB并集。

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


0.准备

join主要根据两表或多表之间列的关系,从这些表中进行数据的查询。

首先创建两张表:tb_emp(员工表)和tb_dept(部门表),并插入相关测试数据。

1.tb_emp表。

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `deptid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tb_emp_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3');
INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '14');

2.tb_dept表。

DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_dept`(deptname) VALUES ('研发');
INSERT INTO `tb_dept`(deptname) VALUES ('测试');
INSERT INTO `tb_dept`(deptname) VALUES ('运维');
INSERT INTO `tb_dept`(deptname) VALUES ('经理');

从上表插入的数据可知outman是没有对应部门的。

  • 交叉连接: 生成笛卡尔积,它不使用任何匹配条件,
  • 内连接: 只连接匹配的行
  • 左连接: 会显示左边表内所有的值,无论在右边的表内是否匹配
  • 右连接: 会显示右边表内所有的值,无论在左边的表内是否匹配
  • 全外连接: 包含左右两个表的全部行 

 

1.inner join

注:A表示左表,B表示右表,下同。

inner join:A、B共有,也就是交集。

满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。

   

2.left join

left jion:A独有+AB共有(交集)

这种场景下得到的是A的所有数据,和满足某一条件的B的数据。

   

3.right join

right join:B独有+AB共有(交集)

   

4.A独有

   

注:参照left join,A独有只是将AB交集部分去掉。

5.B独有

   

注:参照right join,B独有只是将AB交集部分去掉。

6.AB全有(并集)

   

由于mysql中不支持full outer join,所以这里通过union进行转换。AB并集:AB交集+A独有+B独有。

7.A、B独有并集

   

A、B独有并集,相当于A、B全有去掉AB的共有(交集)。

总结

这里主要对MySQL中join语句的7中用法进行了总结,主要注意MySQL不支持full outer join,所以需要对其进行转换变形,最终达到效果

### MySQL JOIN 查询详解 #### 内连接 (INNER JOIN) 内连接返回两个表中满足连接条件的记录。只有当两张表中的某个字段相等时才会显示出来。 ```sql SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; ``` 此查询会返回订单表 `orders` 客户表 `customers` 中具有相同 `customer_id` 的所有记录[^1]。 #### 外连接 (OUTER JOIN) 外连接分为左外连接 (`LEFT OUTER JOIN`) 右外连接 (`RIGHT OUTER JOIN`)。这些连接不仅返回匹配的数据,还会保留左侧或右侧表中不匹配的数据,在无对应关系的地方填充 NULL 值。 ##### 左外连接 (LEFT OUTER JOIN) 左外连接返回左边表所有的记录以及右边表符合条件的记录;如果右边没有对应的记录,则用NULL补充。 ```sql SELECT employees.employee_name, departments.department_name FROM employees LEFT OUTER JOIN departments ON employees.department_id = departments.department_id; ``` 这条命令即使某些员工未分配部门也会列出所有员工的信息,并且对于那些缺少关联部门的情况给出 null 结果[^2]。 ##### 右外连接 (RIGHT OUTER JOIN) 与左外连接相反,右外连接确保右边表格的所有行都被包含进来,而不管左边是否有相应的匹配项。 ```sql SELECT products.product_name, suppliers.supplier_name FROM products RIGHT OUTER JOIN suppliers ON products.supplier_id = suppliers.supplier_id; ``` 这段代码将展示供应商列表及其供应的产品名称,即便有些供应商当前没有任何产品也依然会被列出来. #### 交叉连接 (CROSS JOIN) 交叉连接会产生笛卡尔积的结果集,即第一个表每一行同第二个表每行列组合配对形成新的结果集合。需要注意的是,在实际应用中很少单独使用这种类型的联接方式,因为它通常会导致非常庞大的数据量。 ```sql SELECT a.item_name AS itemA, b.item_name AS itemB FROM items a CROSS JOIN items b; ``` 上述例子展示了如何创建物品之间的所有可能搭配情况,这在特定场景下可能是有用的,但在大多数情况下并不是所期望的行为. #### 子查询 vs. JOIN 尽管子查询可以在某种程度上完成类似于 JOIN 的功能,特别是在涉及复杂的逻辑运算或是依赖聚合函数的情况下表现良好,但对于简单的多表间一对一或多对一的关系来说,JOIN 更加简洁明了并能提供更好的性能优化机会[^4].
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值