为什么INNER JOIN不等于（！=）永远挂起 [英] Why INNER JOIN not equal (!=) hang forever

问题描述

当我执行以下查询：

  SELECT * FROM`table1`
 INNER JOIN table2 ON table2。 number = table1.number 
  

我在2秒内得到结果。在 table2 中有大约600万条记录，中有100万条记录table1

table2.number 和 table1.number 已编入索引。

现在我想得到一个不存在的数字列表。像这样：

  SELECT * FROM`table1` 
 INNER JOIN table2 ON table2.number！= table1.number 
  

它永远还在悬挂..如何解决？

INNER JOIN 返回中的1,000,000行的75％table1 。第二个查询不会返回25万其他行，如你所想。相反，它尝试创建一个笛卡尔积，并删除750,000个匹配行。因此，它试图返回6,000,000次; 1,000,000-750,000行。这是一个膨胀的6× 10 ¹² 行结果集。

您可能想要这样：

  SELECT * FROM table1 
 LEFT JOIN table2 ON table2.number = table1.number 
 WHERE table2.number IS NULL 
  

这会返回 table2中不存在的行 table1 code>。

您可能也会对 FULL OUTER JOIN 感兴趣：

SELECT * FROM table1
FULL OUTER JOIN table2 ON table2.number = table1.number
WHERE table1.number IS NULL AND table2 .nu​​mber IS NULL


这将返回两个表中在其他表上没有匹配项的行。

When I execute the following query:

SELECT * FROM `table1` 
 INNER JOIN table2 ON table2.number = table1.number

I get the result within 2 seconds. There are about 6 millions records in table2 and 1 million records in table1

table2.number and table1.number are indexed.

Now I want to get a list of numbers that not exist. Like this:

SELECT * FROM `table1` 
 INNER JOIN table2 ON table2.number != table1.number

It take forever and still hanging.. How to fix?

解决方案

Let's say your first INNER JOIN returns 75% of the 1,000,000 rows in table1. The second query does not return the 250,000 other rows as you think. Instead, it attempts to create a Cartesian product and remove the 750,000 matching rows. Thus it's trying to return 6,000,000×1,000,000-750,000 rows. That's a bulging 6×10¹² row result set.

You probably want this:

SELECT * FROM table1
LEFT JOIN table2 ON table2.number = table1.number
WHERE table2.number IS NULL

This returns rows in table1 not present in table2.

You might also be interested in FULL OUTER JOIN:

SELECT * FROM table1
FULL OUTER JOIN table2 ON table2.number = table1.number
WHERE table1.number IS NULL AND table2.number IS NULL

This returns rows in both tables that don't have a match on the other table.

这篇关于为什么INNER JOIN不等于（！=）永远挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！