连接查询

#连接查询
/**根据表连接的方式分类：
            内连接：
                    等值连接
                    非等值连接
                    自连接
                    
            外连接：
                    左外连接(左连接)
                    右外连接(右连接)
            
            全连接
*/
SELECT * FROM EMP;
SELECT * FROM DEPT;
#当两张表进行连接查询时，没有任何条件的限制，最终查询条件结果条数，是两张表条数的乘积，这就是笛卡尔积现象
#查询每个员工所在的部门名称。
SELECT ENAME,DNAME FROM EMP,DEPT;

#如何避免，连接查询时添加条件
SELECT 
    ENAME,DNAME 
FROM 
    EMP,DEPT
WHERE
    EMP.DEPTNO = DEPT.deptno
ORDER BY
    DNAME;
    
#表起别名，很重要，效率问题    
SELECT 
    e.ENAME,d.DNAME 
FROM 
    EMP e,DEPT d
WHERE
    e.DEPTNO = d.deptno
ORDER BY
    DNAME;
#最终查询条数显示是减少了，但是过程中匹配的次数没有减少。
#笛卡尔积现象表名，表的连接次数越多，效率越低，尽量避免。

#内连接之等值连接
#案例：查询每个员工所在部门名称，显示员工名和部门名？
#SQL92语法
SELECT 
    e.ename,d.dname
FROM
    EMP e,DEPT d
WHERE
    e.DEPTNO = d.DEPTNO
ORDER BY
    DNAME;
#SQL92的缺点，结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面

#SQL99语法：
SELECT
    e.ename,d.dname 
FROM
    EMP e
JOIN
    DEPT d
ON 
    e.DEPTNO = d.DEPTNO
ORDER BY
  DNAME;
    
#INNER 可以省略，加上的话可以看出是内连接.(可读性更好)
SELECT
    e.ename,d.dname 
FROM
    EMP e
INNER JOIN
    DEPT d
ON 
    e.DEPTNO = d.DEPTNO #条件是等量关系，所以被称为等值连接
ORDER BY
  DNAME;
#SQL99:表连接的条件是独立的，连接之后如果还需要进一步筛选，再往后继续添加where
#SQL99语法
SELECT
    ...
FROM
    ...
JOIN
    ...
ON
    a和b 的连接条件
WHERE
  筛选条件
    
#内连接之非等值连接
#案列：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级。
SELECT 
    e.ENAME,e.sal,s.GRADE
FROM
    EMP e
JOIN
    SALGRADE s
ON
    e.SAL BETWEEN s.LOSAL AND s.HISAL #条件不是一个等量关系，称为非等值连接。
ORDER BY
    s.GRADE;

#内连接之自连接
#案列：查询员工的上级领导，要求显示员工名和对应的领导名。
SELECT  DISTINCT
    e.ENAME,m.ENAME HINAME
FROM
    EMP e
JOIN
    EMP m
ON
    e.MGR = m.EMPNO #员工的领导编号，等于领导的员工编号
ORDER BY
    ename;
#以上就是内连接中的：自连接，一张表看做两张表。

#内连接(A和B连接，AB两张表没有主次关系，平等的)
SELECT
    e.ENAME,d.DNAME
FROM
    EMP e
JOIN
    DEPT d
ON
    e.DEPTNO = d.DEPTNO; #内连接特点：就是完全能够匹配上这个条件的数据查询出来。
    
#外连接(右外连接)
SELECT
    e.ENAME,d.DNAME
FROM
    EMP e 
RIGHT OUTER JOIN #OUTER可以省略，带着可读性强
    DEPT d
ON
    e.DEPTNO = d.DEPTNO; 
#RIGHT 表示将JOIN关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
#在外连接当中，两张表连接产生了主次关系。

#外连接(左外连接)
SELECT
    e.ENAME,d.DNAME
FROM
    DEPT d  
LEFT OUTER JOIN #OUTER可以省略，带着可读性强 
  EMP e
ON
    e.DEPTNO = d.DEPTNO; 
#带有right的是右外连接，又叫右连接
#带有left的是左外连接，又叫左连接
#任何一个右连接都有左连接的写法
#任何一个左连接都有一个右连接的写法

#注意：外连接的查询结果条数一定是>=内连接的查询结果条数
#案列：查询每个员工的上级领导，要求显示所有员工的名字和领导们
SELECT
    e.ENAME,d.ENAME
FROM
    EMP e
LEFT JOIN
    EMP d
ON 
    e.MGR = d.EMPNO;
    
#三张表连接，四张表连接
/**语法:
            SELECT
                    ...
            FROM
                    a
            JOIN 
                    b
            ON
                a和b的连接条件
            Join
                o
            ON
                a和c的连接条件
            JOIN
                c
            ON
                a和c的连接条件
            RIGHT JOIN
                d
            ON
                a和d的连接
            
            一条SQL中内连接和外连接可以混合，都可以出现
*/
#案列：找出每个员工的部门名称以及工资等级，
#要求显示员工名、部门名、薪资、和薪资等级
SELECT
    e.ENAME,d.DNAME,e.SAL,s.GRADE
FROM
    EMP e
JOIN
    DEPT d
ON
    e.DEPTNO = d.DEPTNO
JOIN
    SALGRADE s
ON
    e.SAL BETWEEN s.LOSAL AND s.HISAL;
    
#案列：找出每个员工的部门名称以及工资等级，以及上级领导
#要求显示员工名、部门名、薪资、和薪资等级
SELECT
    e.ENAME,m.ENAME as MGRNAME,d.DNAME,e.SAL,s.GRADE
FROM
    EMP e
JOIN
    DEPT d
ON
    e.DEPTNO = d.DEPTNO
JOIN
    SALGRADE s
ON
    e.SAL BETWEEN s.LOSAL AND s.HISAL
LEFT JOiN
    EMP m
ON
    e.MGR = m.EMPNO;