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)
本文作者: Liaodeity
本文链接: https://www.jianbaizhan.com/article/99
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处!