内连接

• 隐式内连接
  查询所有员工信息和对应的部门信息

  SELECT 字段列表 FROM 表名1 , 表名2 WHERE 条件;

• 显式内连接：

  SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 on 条件;

• 例

  -- 隐式内连接
  -- 查询所有员工信息和对应的部门信息
  SELECT * FROM emp , dept WHERE emp.dept_id = dept.id;
  -- 查询员工表的名称、性别，部门表的名称
  SELECT
      emp.name,
      emp.sex,
      dept.name
  FROM
      emp,
      dept
  WHERE
      emp.dept_id = dept.id;
  -- 查询员工表的名称、性别，部门表的名称(起别名)
  SELECT
      t1.name,
      t1.sex,
      t2.name
  FROM
      emp t1,
      dept t2
  WHERE
      emp.dept_id = dept.id;
  
  -- 显式内连接
  -- 查询所有员工信息和对应的部门信息
  SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
  SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id; -- JOIN可省略

外连接查询

• 左外连接

  • 语法

    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

  • 查询的是左表所有数据及其交集部分

• 右外连接

  • 语法

    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

  • 查询的是右表所有数据及其交集部分
• 左右外连接可以相互转换，一般使用左外连接

• 例

  -- 左连接
  -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
  SELECT t1.* , t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id;
  -- 右连接
  SELECT t1.* , t2.name FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id=t2.id;
  -- 跟左连接一样
  SELECT t2.* , t1.name FROM dept t2 RIGHT JOIN emp t1 ON t2.dept_id=t1.id;

子查询

• 子查询的结果是单行单列的

  • 子查询可以作为条件使用运算符去判断

  • 例

    -- 查询员工工资小于平均工资的人
    SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

• 子查询的结果是多行单列的

  • 子查询可以作为条件，使用运算符IN进行判断

  • 例

    -- 查询财务部和市场部所有的员工信息
    SELECT * FROM emp 
    WHERE
        dept_id IN ( SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' );

• 子查询的结果是多行多列的

  • 子查询可以作为一张虚拟表参与查询

  • 例

    -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
    -- 使用子查询
    SELECT
        *
    FROM
        dept t1,
        ( SELECT * FROM emp WHERE emp.join_date > '2011-11-11' ) t2 
    WHERE
        t1.id = t2.id;
    -- 使用内连接
    SELECT
        * 
    FROM
        emp t1,
        dept t2 
    WHERE
        t1.dept_id = t2.id 
        AND t1.join_date > '2011-11-11';