![Pure Soul](https://oo2ee.com/"wp-content/uploads"/2021/12/cropped-cropped-logo-1.png){kind=logo}
目录
数据准备
创建两个表，一个存放用户信息，另一个存放用户订购的信息
CREATE TABLE IF NOT EXISTS `Customer`(
`Id` INT UNSIGNED AUTO_INCREMENT,
`Name` VARCHAR(100) NOT NULL,
`OrderId` INT UNSIGNED NOT NULL,
PRIMARY KEY ( `Id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Orders`(
`Id` INT UNSIGNED AUTO_INCREMENT,
`GoodsName` VARCHAR(100) NOT NULL,
`CustomerName` VARCHAR(100) NOT NULL,
PRIMARY KEY ( `Id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
往表中插入数据
insert into Customer (Name, OrderId) VALUES
('A',1),
('B',2),
('B',3),
('C',4),
('C',5),
('D',6),
('E',7);
插入数据之后
insert into Orders (CustomerName,GoodsName) VALUES ('A','Apple'),
('B','Chair'),
('B','Pen'),
('C','Car'),
('C','Phone'),
('D','Computer'),
('E','House'),
('F','Food'),
('G','Glass');
插入数据之后：
笛卡尔积
select * from Customer,Orders;
+----+------+---------+----+-----------+--------------+
| Id | Name | OrderId | Id | GoodsName | CustomerName |
+----+------+---------+----+-----------+--------------+
| 1 | A | 0 | 1 | Apple | A |
| 2 | B | 1 | 1 | Apple | A |
| 3 | B | 2 | 1 | Apple | A |
| 4 | C | 3 | 1 | Apple | A |
| 5 | C | 4 | 1 | Apple | A |
| 6 | D | 5 | 1 | Apple | A |
| 7 | E | 6 | 1 | Apple | A |
| 1 | A | 0 | 2 | Chair | B |
| 2 | B | 1 | 2 | Chair | B |
| 3 | B | 2 | 2 | Chair | B |
| 4 | C | 3 | 2 | Chair | B |
| 5 | C | 4 | 2 | Chair | B |
| 6 | D | 5 | 2 | Chair | B |
| 7 | E | 6 | 2 | Chair | B |
| 1 | A | 0 | 3 | Pen | B |
| 2 | B | 1 | 3 | Pen | B |
| 3 | B | 2 | 3 | Pen | B |
| 4 | C | 3 | 3 | Pen | B |
| 5 | C | 4 | 3 | Pen | B |
| 6 | D | 5 | 3 | Pen | B |
| 7 | E | 6 | 3 | Pen | B |
| 1 | A | 0 | 4 | Car | C |
| 2 | B | 1 | 4 | Car | C |
| 3 | B | 2 | 4 | Car | C |
| 4 | C | 3 | 4 | Car | C |
| 5 | C | 4 | 4 | Car | C |
| 6 | D | 5 | 4 | Car | C |
| 7 | E | 6 | 4 | Car | C |
| 1 | A | 0 | 5 | Phone | C |
| 2 | B | 1 | 5 | Phone | C |
| 3 | B | 2 | 5 | Phone | C |
| 4 | C | 3 | 5 | Phone | C |
| 5 | C | 4 | 5 | Phone | C |
| 6 | D | 5 | 5 | Phone | C |
| 7 | E | 6 | 5 | Phone | C |
| 1 | A | 0 | 6 | Computer | D |
| 2 | B | 1 | 6 | Computer | D |
| 3 | B | 2 | 6 | Computer | D |
| 4 | C | 3 | 6 | Computer | D |
| 5 | C | 4 | 6 | Computer | D |
| 6 | D | 5 | 6 | Computer | D |
| 7 | E | 6 | 6 | Computer | D |
| 1 | A | 0 | 7 | House | E |
| 2 | B | 1 | 7 | House | E |
| 3 | B | 2 | 7 | House | E |
| 4 | C | 3 | 7 | House | E |
| 5 | C | 4 | 7 | House | E |
| 6 | D | 5 | 7 | House | E |
| 7 | E | 6 | 7 | House | E |
| 1 | A | 0 | 8 | Food | F |
| 2 | B | 1 | 8 | Food | F |
| 3 | B | 2 | 8 | Food | F |
| 4 | C | 3 | 8 | Food | F |
| 5 | C | 4 | 8 | Food | F |
| 6 | D | 5 | 8 | Food | F |
| 7 | E | 6 | 8 | Food | F |
| 1 | A | 0 | 9 | Glass | G |
| 2 | B | 1 | 9 | Glass | G |
| 3 | B | 2 | 9 | Glass | G |
| 4 | C | 3 | 9 | Glass | G |
| 5 | C | 4 | 9 | Glass | G |
| 6 | D | 5 | 9 | Glass | G |
| 7 | E | 6 | 9 | Glass | G |
+----+------+---------+----+-----------+--------------+
63 rows in set (0.00 sec)
也就是前面三列来自于Customer，后三列来自于Orders。数据行数为Customer的行数乘以Orders行数(63=7*9)。所有的数据都是两个表的组合。
左连接
select * from Customer left join Orders on 1=1;
其中的on 1=1表示不设置任何条件，但是如果不使用on设置条件就会存在语法错误，并且使用1=1恒等条件会产生笛卡尔积的效果。左连接当中不仅可以使用on还而已在之后使用where，只不过on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
select * from Customer left join Orders on Customer.OrderId=Orders.Id;
结果如下：
如果添加where条件;
select * from Customer left join Orders on Customer.OrderId=Orders.Id where Customer.Name='B';
由此可见连接会把两个表的字段都合并在一起，没有匹配上的会显示NULL。
右连接
select * from Customer right join Orders on Customer.OrderId=Orders.Id;
添加筛选条件之后
select * from Customer right join Orders on Customer.OrderId=Orders.Id where Customer.OrderId is NULL;
