最近面试中,Java的都会考察数据库,写SQL语句,大多数就问关联查询,索引
看看下面三个关联查询的 SQL 语句有何区别?
SELECT * FROM score, student WHERE score.id = student.id ...
SELECT * FROM score JOIN student ON (score.id = student.id) WHERE ...
SELECT * FROM score JOIN student USING (id) WHERE ...
第一种为联合查询或者说并查询,前两种写法,只是写法不同而已,其功能完全相同;
最后一种,会将同名的列,合并起来。
第一种,是传统写法,SQL89标准里,就是这种;后面两种,是在SQL92标准才有的!
mysql> select * from score;
+----+-------+
| id | score |
+----+-------+
| 1 | 60 |
| 2 | 75 |
| 4 | 90 |
+----+-------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
3 rows in set (0.00 sec)
mysql> select * from score, student where score.id=student.id;
+----