原文地址:MySQL中,关联查询的3种写法(USING/ON)
作者:王小安
看看下面三个关联查询的 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)