Select多表查询

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';