内连接之等值连接:最大特点是:条件是等量关系。
案例:查询每个员工的部门名称,要求显示员工名和部门名。
下面是SQL92的(太老,不用了)
mysql> select
e.ename ,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
下面是SQL99的(常用的)
mysql> select
e.ename ,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
下面是带着inner的,inner是可以忽略的,带着inner目的是可读性好一些,直接就看出来是内连接:
mysql> select
e.ename ,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
查询结果都是下面的表:
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
总结:语法:
select
...
from
A
join
B
on
查询条件
where
。。。
SQL99语法结构更清晰一些:标的连接条件和后面的where条件分离了。