MySQL 为什么 INNER JOIN 不等于 (!=) 永远挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13863751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why INNER JOIN not equal (!=) hang forever
提问by I'll-Be-Back
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 table2and 1 million records in table1
我在 2 秒内得到结果。大约有 600 万条记录table2和 100 万条记录table1
table2.numberand table1.numberare indexed.
table2.number并被table1.number索引。
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?
它需要永远并且仍然挂着..如何解决？
回答by álvaro González
Let's say your first INNER JOINreturns 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×1012row result set.
假设您的第一个INNER JOIN返回 1,000,000 行中的 75% table1。第二个查询不会像您想的那样返回 250,000 其他行。相反，它尝试创建笛卡尔积并删除 750,000 个匹配行。因此它试图返回 6,000,000×1,000,000-750,000 行。这是一个膨胀的 6×10 12行结果集。
You probably want this:
你可能想要这个：
SELECT * FROM table1
LEFT JOIN table2 ON table2.number = table1.number
WHERE table2.number IS NULL
This returns rows in table1not present in table2.
这将返回 中table1不存在的行table2。
You might also be interested in FULL OUTER JOIN:
您可能还对以下内容感兴趣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.
这将返回两个表中在另一个表上没有匹配项的行。
回答by Ian Overton
The reason this isn't working is cause your basiclly joining every row of table1 with every row with table 2. You need something to still join it with. The best way to do this is to do a left join (Meaning it'll join table1 no matter what, but not table2) and then check to make sure there isn't an entry for table2 with the is null. You'll then need to do the same thing for table2.
这不起作用的原因是导致您基本上将 table1 的每一行与 table 2 的每一行连接起来。您仍然需要一些东西来连接它。执行此操作的最佳方法是进行左连接（意味着无论如何它都会连接 table1，但不会连接 table2），然后检查以确保 table2 没有为 null 的条目。然后，您需要对 table2 执行相同的操作。
SELECT * FROM `table1`
LEFT JOIN table2 ON table2.number = table1.number
WHERE table2.number is NULL
UNION
SELECT * FROM `table2`
LEFT JOIN table1 ON table2.number = table1.number
WHERE table1.number is NULL
回答by geetha
Instead of this you can use this method:
SELECT * FROM table1LEFT JOIN table2 ON table2.number = table1.number
WHERE table2.number is NULL OR table1.number is NULL
您可以使用以下方法代替此方法：SELECT * FROM table1LEFT JOIN table2 ON table2.number = table1.number WHERE table2.number is NULL OR table1.number is NULL
