数据库 ·

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 是相同的。
--查询在Orders存在的Persons,即有订购记录的用户
SELECT
	P .LastName,
	P .FirstName,
	o.OrderNo
FROM
	Persons P
INNER JOIN Orders o ON P .Id_P = o.Id_P
ORDER BY
	P .LastName

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

2、LEFT JOIN(左连接)

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

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

基础

SELECT
	P .LastName,
	P .FirstName,
	o.OrderNo
FROM
	Persons P
LEFT JOIN Orders o ON P .Id_P = o.Id_P
ORDER BY
	P .LastName
  • LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。
  • 当右表未匹配时会以null展示

进阶

SELECT
	p2.LastName,
	p2.FirstName,
	o2.OrderNo
FROM
	Persons p2
LEFT JOIN (
	SELECT
		P .LastName,
		P .FirstName,
		o.OrderNo
	FROM
		Persons P
	INNER JOIN Orders o ON P .Id_P = o.Id_P
) o2
WHERE
	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 区别

参与评论