【MySQL刷题】查找工资比经理高的员工

三种方式

首先想到的是子查询

子查询

SELECT name AS 'Employee'
  FROM Employee AS worker
    WHERE salary > (
      SELECT salary
        FROM Employee AS manager
          WHERE worker.managerId = manager.id
    );

这性能。。。大概考虑了一下性能低的原因

• 子查询像一个for循环，会为每一个外层的worker单独挨个查询比较一次，而内连接通过ON将两个表的结果集组合成一个中间结果集，然后一次性比较出结果

自连接

SELECT worker.`name` AS 'Employee'
  FROM Employee AS 'worker', Employee AS 'manager'
    WHERE worker.managerId = manager.id
      AND worker.salary > manager.salary;

关键点在于先将两表关联起来，或者说组合起来，此时产生笛卡尔积

先关联，再根据列名合并，再过滤

SELECT worker.`name` AS 'Employee'
  FROM Employee AS worker, Employee AS manager

结果集如下：4x4 = 16行结果

再加过滤条件

看得出这个题目自连接的效率不如内连接，毕竟要重复扫描一个表，也比内连接的逻辑复杂，要考虑表自身的关联关系

内连接

跟自连接完全一样，先组合，再合并同列名，再过滤

SELECT worker.name AS 'Employee'
  FROM Employee AS worker
    JOIN Employee AS manager
      ON worker.managerId = manager.id
        AND worker.salary > manager.salary

 

注意用别名要符合SQL规范，列的别名可以加单引号，表的就不要加了