where in与join 查询

前言

问题

Oracle：当前所用版本中，限制in中的参数不能超过 1000个。当超出时会被报错”ORA-01795异常(where in超过1000)的解决”。

MySQL：有人说有限制，有人说没限制。但尽量也是不要太多为好，容易造成全表扫描。

解决方案

• 使用 inner join 代替 in
• 使用 left in … where … is null 代替 not in

实例

1、INNER JOIN（内连接）

• 在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
• INNER JOIN 与 JOIN 是相同的。

1
--查询在Orders存在的Persons，即有订购记录的用户
2
SELECT
3
  P .LastName,
4
  P .FirstName,
5
  o.OrderNo
6
FROM
7
  Persons P
8
INNER JOIN Orders o ON P .Id_P = o.Id_P
9
ORDER BY
10
  P .LastName

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 “Persons” 中的行在 “Orders” 中没有匹配，就不会列出这些行。

2、LEFT JOIN（左连接）

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

基础

1
SELECT
2
  P .LastName,
3
  P .FirstName,
4
  o.OrderNo
5
FROM
6
  Persons P
7
LEFT JOIN Orders o ON P .Id_P = o.Id_P
8
ORDER BY
9
  P .LastName

• LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。
• 当右表未匹配时会以null展示

进阶

1
SELECT
2
  p2.LastName,
3
  p2.FirstName,
4
  o2.OrderNo
5
FROM
6
  Persons p2
7
LEFT JOIN (
8
  SELECT
9
    P .LastName,
10
    P .FirstName,
11
    o.OrderNo
12
  FROM
13
    Persons P
14
  INNER JOIN Orders o ON P .Id_P = o.Id_P
15
) o2
16
WHERE
17
  o2.OrderNo IS NULL;

• 先用 inner join 查询出有订单的用户，将该查询作为右表o2
• Persons 继续做左表，此时为p2
• p2 LEFT JOIN o2 查询左表所有信息，加上条件 o2.OrderNo is null 将有订单的用户过滤掉
• 最终出来的是，没有下订单的用户，即代替了 not in实现。

参考与扩展

SQL INNER JOIN 关键字 SQL LEFT JOIN 关键字 inner join 和where 区别