多表查询

1. Join连接

Join是SQL92中引入的，其中INNER JOIN可以省略INNER关键字，内连接和等值连接效果一样。

select
    a.orderid, a.customerid, a.employeeid, a.orderdate
from orders a
inner join producets b
where a.orderid = b.orderid;
select
    a.orderid, a.customerid, a.employeeid, a.orderdate
from orders a, producets b
where a.orderid = b.orderid;
-- 和in查询的区别是in做了去重：
select
    a.orderid, a.customerid, a.employeeid, a.orderdate
from orders a
where orderid in (select orderid from producets b);

2. 内连接

查询每个员工最新的岗位信息：

SELECT
    emp_no, title 
FROM
    titles 
WHERE
    (emp_no ,to_date) in (
        SELECT emp_no， MAX(to_date)
        FROM
    titles 
        GROUP BY emp_no)

3. 外连接

查询在左表中但右表中没有：

select
    a.actor_id, first_name + '-' + last_name
from actor a
    left outer join film_actor b
    on a.actor_id = b.actor_id
where b.actor_id is null;

4. on和where区别

在内连接中，on和where的作用相同；在外连接中，on如果加上其他表的条件仍然表示连接条件而不是where过滤条件:

-- 返回客户是加拿大的，但是1997年内没有产生订单的客户
SELECT
    c.c_name，c.c_phone, c.c_address
    FROM customer c
    LEFT JOIN orders o ON c.c_custkey=o.o_custkey 
    LEFT JOIN nation n ON c.c_nationkey=n_nationkey
    WHERE
    o.o_orderkey IS NULL 
    AND o.o_orderDATE >=1997-01-01 AND o.orderDATE <'1998-01-01'
    AND n.nname ='CANADA';

上面的sql表示是对的，其实是错误的，因为o.o_orderkey IS NULL和o.o_orderDATE >=1997-01-01 AND o.orderDATE <'1998-01-01'条件是互斥的，也就是会导致没有数据被匹配到，正确做法是：

SELECT
    c.c_name，c.c_phone, c.c_address
    FROM customer c
    LEFT JOIN orders o ON c.c_custkey=o.o_custkey 
    AND o.o_orderDATE >=1997-01-01 AND o.orderDATE <'1998-01-01'
    LEFT JOIN nation n ON c.c_nationkey=n_nationkey
    WHERE
    o.o_orderkey IS NULL
    AND n.nname ='CANADA';

5. 子查询

子查询可以接在ANY,IN,SOME,ALL的后面，ANY关键词的意思是"对于在子查询返回的列中的任一数值，如果比较结果为TRUE的话，则返回TRUE。其中SOME和ANY作用相同。

5.1 子查询分类

1. 独立子查询：不依赖外部查询而运行的子查询。
2. 相关子查询：引用了外部查询列的子查询。

-- 相关子查询
SELECT orderid,customerid,employeeid,orderdate,requireddate
FROM orders 
WHERE orderdate IN(
    SELECT 
        MAX(orderdate)
     FROM
    orders GROUP BY employeeid 
);

5.2 Exists子查询

用来返回TRUE、FALSE。特殊的UNKNOWN返回为FALSE，Exists子查询是相关子查询，总是内部子查询需要和外部进行关联查询。Exists后面直接接上子查询不需要指定字段名。

SELECT orderid, customerid, employeeid, orderdate
FROM orders a
WHERE exists (
    SELECT
        MAX(orderdate)
    FROM
        orders b
    GROUP BY (date_format(orderdate, '%Y%M'))
    having max(orderdate) = a.orderdate
);

exists子查询会跟随主查询次数进行执行。

5.3 in子查询

mysql> select 'd' not in ('a', 'b', 'c', null);
+----------------------------------+
| 'd' not in ('a', 'b', 'c', null) |
+----------------------------------+
|                             NULL |
+----------------------------------+
1 row in set (0.00 sec)

对于in后面出现null值，如果在in子查询只会出现1和null结果，对于not in子查询只会出现0和null结果。