看看下面三个关联查询的 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;
+----+-------+----+--------+
| id | score | id | name |
+----+-------+----+--------+
| 1 | 60 | 1 | 张三 |
| 2 | 75 | 2 | 李四 |
+----+-------+----+--------+
2 rows in set (0.09 sec)
mysql> select * from score join student on score.id=student.id;
+----+-------+----+--------+
| id | score | id | name |
+----+-------+----+--------+
| 1 | 60 | 1 | 张三 |
| 2 | 75 | 2 | 李四 |
+----+-------+----+--------+
2 rows in set (0.00 sec)
mysql> select * from score join student using(id);
+----+-------+--------+
| id | score | name |
+----+-------+--------+
| 1 | 60 | 张三 |
| 2 | 75 | 李四 |
+----+-------+--------+
2 rows in set (0.00 sec)
附,下面是SQL发展的简要历史:
1986年,ANSI X3.135-1986,ISO/IEC 9075:1986,SQL-86
1989年,ANSI X3.135-1989,ISO/IEC 9075:1989,SQL-89
1992年,ANSI X3.135-1992, ISO/IEC 9075:1992,SQL-92(SQL2)
1999年,ISO/IEC 9075:1999, SQL:1999(SQL3)
2003年,ISO/IEC 9075:2003, SQL:2003(SQL4)
200N年,ISO/IEC 9075:200N, SQL:200N(SQLN)