多张数据表或视图的查询叫做连接查询

-- 1.笛卡尔积:

SELECT *

FROM emp,dept;

-- 2.等值链接(SELECT * FROM A,B WHERE A.主键=B.外键;)

SELECT *

FROM emp,dept WHERE dept.deptno = emp.deptno;

-- 3.内连接(SELECT * FROM A INNER JOIN B ON A.主键=B.外键;)

SELECT *

FROM emp INNER JOIN dept ON dept.deptno = emp.deptno;

-- 4.外连接:

-- 4.1左外连接:(SELECT * FROM A LEFT OUTER JOIN B ON 条件;)

SELECT *

FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno;

-- 4.2右外连接:(SELECT * FROM A right OUTER JOIN B ON 条件;)

SELECT *

FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;

-- 5.子查询:

-- 5.1单行单列,工作地点在上海的员工

SELECT *

FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc ='上海');

-- 5.2多行单列,工作地点不在上海的员工

SELECT *

FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>'上海');

-- 6.自连接

SELECT e1.*,e2.* FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno

WHERE e1.ename ='吕子乔'