MySQL 7种 JOIN连表方法
2024-09-05 15:18:55
规定：左边的圆代表表 a，右边的代表 b。
JOIN 关键字可以在两表之间选中任意部分。】
通过以下代码制造一些数据：
delimiter //
drop procedure if exists produce_data//
create procedure produce_data()
begin
declare i int default 0;
drop table if exists a;
drop table if exists b;
create table a(id int not null,name varchar(32));
create table b(id int not null,name varchar(32));
set i = 1;
while i <= 4 do
insert into a(id,name) values(i, concat('name', i));
set i = i + 1;
end while;
set i = 3;
while i <= 6 do
insert into b(id,name) values(i, concat('name', i));
set i = i + 1;
end while;
end//
call produce_data()//
mysql> select * from a//
+----+-------+
| id | name |
+----+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
+----+-------+
mysql> select * from b//
+----+-------+
| id | name |
+----+-------+
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
| 6 | name6 |
+----+-------+
select *
from a left join b on a.id = b.id// +----+-------+------+-------+
| id | name | id | name |
+----+-------+------+-------+
| 1 | name1 | NULL | NULL |
| 2 | name2 | NULL | NULL |
| 3 | name3 | 3 | name3 |
| 4 | name4 | 4 | name4 |
+----+-------+------+-------+
select *
from a right outer join b on a.id = b.id//
+------+-------+----+-------+
| id | name | id | name |
+------+-------+----+-------+
| 3 | name3 | 3 | name3 |
| 4 | name4 | 4 | name4 |
| NULL | NULL | 5 | name5 |
| NULL | NULL | 6 | name6 |
+------+-------+----+-------+
select *
from a inner join b on a.id = b.id// +----+-------+----+-------+
| id | name | id | name |
+----+-------+----+-------+
| 3 | name3 | 3 | name3 |
| 4 | name4 | 4 | name4 |
+----+-------+----+-------+
select *
from a left join b on a.id = b.id
where b.id is null// +----+-------+------+------+
| id | name | id | name |
+----+-------+------+------+
| 1 | name1 | NULL | NULL |
| 2 | name2 | NULL | NULL |
+----+-------+------+------+
select *
from a right join b on a.id = b.id
where a.id is null// +------+------+----+-------+
| id | name | id | name |
+------+------+----+-------+
| NULL | NULL | 5 | name5 |
| NULL | NULL | 6 | name6 |
+------+------+----+-------+
一般，是这样写：
select *
from a full outer join b on a.id = b.id
where a.id is null or b.id is null//
但是，mysql 并没有 FULL 关键字，因此使用 UNION 联接 左连接和 右连接。
select *
from a left join b on a.id = b.id
where b.id is null
union
select *
from a right join b on a.id = b.id
where a.id is null//
+------+--------+------+--------+
| a_id | a_name | b_id | b_name |
+------+--------+------+--------+
| 1 | name1 | NULL | NULL |
| 2 | name2 | NULL | NULL |
| NULL | NULL | 5 | name5 |
| NULL | NULL | 6 | name6 |
+------+--------+------+--------+
类似上面，使用UNION
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from a left join b on a.id = b.id
union
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from a right join b on a.id = b.id//
+------+--------+------+--------+
| a_id | a_name | b_id | b_name |
+------+--------+------+--------+
| 1 | name1 | NULL | NULL |
| 2 | name2 | NULL | NULL |
| 3 | name3 | 3 | name3 |
| 4 | name4 | 4 | name4 |
| NULL | NULL | 5 | name5 |
| NULL | NULL | 6 | name6 |
+------+--------+------+--------+
最新文章
- How to convert any valid date string to a DateTime.
- windows核心编程---第一章 谈谈windows中的错误处理机制
- ServiceManager 小结
- Spring security3
- 《linux下sudo服务的使用》RHEL6
- (转)C#模拟键盘鼠标事件
- mssql sql高效关联子查询的update 批量更新
- H.264 RTPpayload 格式------ H.264 视频 RTP 负载格式
- uVa 714 (二分法)
- T-SQL基础(5) - 表表达式
- hashChange & url change & QRCode & canvas to image
- Linux中断管理
- iview 表单非空验证
- Mysql多实例安装笔记
- SqlBulkCopy简单封装，让批量插入更方便
- JSONObject基本内容（二）
- LoadRunner改脚本
- JAVA设计模式详解（三）----------装饰者模式
- SSH安装篇之——SecureCRT连接(内网和外网)虚拟机中的Linux系统(Ubuntu)
- win10上走网络打印机(不需找驱动包，会自动)