今天看同事代码里使用了 select * from a,b where a.id=b.id, 而我平时都是使用 select * from a inner join b where a.id=b.id, 于是查了下, 发现:
1) 单纯的 select * from a,b 是笛卡尔乘积
2)select * from a,b where a.id=b.id 相当于 inner join
验证
1) 创建两张表
- create table userinfo(
- uid int(10) not null default 0,
- report_id int(10) not null default 0,
- primary key(uid)
- ) engine=innodb default charset=utf8;
- create table report(
- report_id int(10) not null default 0,
- description varchar(255) default '',
- primary key(report_id)
- ) engine=innodb default charset=utf8;
2) 插入测试数据
- insert into userinfo values(1,1),(2,1),(3,2),(4,6);
- insert into report values(1,'第一条'),(2,'第二条'),(3,'第三条');
- MySQL> select * from userinfo;
- +-----+-----------+
- | uid | report_id |
- +-----+-----------+
- | 1 | 1 |
- | 2 | 1 |
- | 3 | 2 |
- | 4 | 6 |
- +-----+-----------+
- 4 rows in set (0.00 sec)
- MySQL> select * from report;
- +-----------+-------------+
- | report_id | description |
- +-----------+-------------+
| 1 | 第一条 |
| 2 | 第二条 |
| 3 | 第三条 |
- +-----------+-------------+
- 3 rows in set (0.00 sec)
3) 验证
单独的 select * from a,b
select * from userinfo,report
结果
- MySQL> select * from userinfo,report;
- +-----+-----------+-----------+-------------+
- | uid | report_id | report_id | description |
- +-----+-----------+-----------+-------------+
| 1 | 1 | 1 | 第一条 |
| 1 | 1 | 2 | 第二条 |
| 1 | 1 | 3 | 第三条 |
| 2 | 1 | 1 | 第一条 |
| 2 | 1 | 2 | 第二条 |
| 2 | 1 | 3 | 第三条 |
| 3 | 2 | 1 | 第一条 |
| 3 | 2 | 2 | 第二条 |
| 3 | 2 | 3 | 第三条 |
| 4 | 6 | 1 | 第一条 |
| 4 | 6 | 2 | 第二条 |
| 4 | 6 | 3 | 第三条 |
- +-----+-----------+-----------+-------------+
- 12 rows in set (0.00 sec)
可见 select * from a,b 是笛卡儿积
再来验证 select * from a,b where a.id=b.id
- MySQL> select * from userinfo,report where userinfo.report_id=report.report_id;
- +-----+-----------+-----------+-------------+
- | uid | report_id | report_id | description |
- +-----+-----------+-----------+-------------+
| 1 | 1 | 1 | 第一条 |
| 2 | 1 | 1 | 第一条 |
| 3 | 2 | 2 | 第二条 |
- +-----+-----------+-----------+-------------+
- 3 rows in set (0.00 sec)
- inner join
- MySQL> select * from userinfo inner join report where userinfo.report_id=report.report_id;
- +-----+-----------+-----------+-------------+
- | uid | report_id | report_id | description |
- +-----+-----------+-----------+-------------+
| 1 | 1 | 1 | 第一条 |
| 2 | 1 | 1 | 第一条 |
| 3 | 2 | 2 | 第二条 |
- +-----+-----------+-----------+-------------+
- 3 rows in set (0.00 sec)
- MySQL> select * from userinfo inner join report on userinfo.report_id=report.report_id;
- +-----+-----------+-----------+-------------+
- | uid | report_id | report_id | description |
- +-----+-----------+-----------+-------------+
| 1 | 1 | 1 | 第一条 |
| 2 | 1 | 1 | 第一条 |
| 3 | 2 | 2 | 第二条 |
- +-----+-----------+-----------+-------------+
- 3 rows in set (0.00 sec)
可见是 select * from a,b where a.id=b.id 只是把笛卡尔积做了一层过滤, 结果与 inner join 相同
补充: inner join 是先生成一个临时表, 然后使用 on 条件筛选
注: 以上结论只在 MySQL 5.7 验证过, 其他数据库不一定成立
来源: http://www.bubuko.com/infodetail-3159820.html