加入与子查询

我是一个老派的MySQL用户，一直喜欢JOIN而不是子查询。但现在每个人都使用子查询，我讨厌它；我不知道为什么。

我缺乏理论知识来判断是否有任何差异。子查询是否与JOIN一样好，因此没有什么可担心的吗？

574184 次浏览
小开

子查询是解决形式为“从A获取事实，以来自B的事实为条件”的问题的逻辑上正确的方式。在这种情况下，将B粘贴在子查询中比进行连接更有逻辑意义。从实际意义上说，它也更安全，因为您不必因为与B的多个匹配而担心从A获取重复的事实。

然而，实际上，答案通常归结为性能。一些优化器在给定连接和子查询时吸柠檬，而另一些则吸柠檬，这是特定于优化器、特定于DBMS版本和特定于查询的。

从历史上看，显式连接通常会赢，因此公认的智慧是连接更好，但优化者一直在变得更好，所以我更喜欢先以逻辑连贯的方式编写查询，然后在性能约束允许的情况下进行重组。

小开

子查询通常用于将单行作为原子值返回，尽管它们可用于将值与使用IN关键字的多行进行比较。它们被允许在SQL语句中的几乎任何有意义的点，包括目标列表、WHERE子句等。一个简单的子查询可以用作搜索条件。例如，在一对表之间：

SELECT titleFROM booksWHERE author_id = (SELECT idFROM authorsWHERE last_name = 'Bar' AND first_name = 'Foo');

请注意，对子查询的结果使用正常值运算符只需要返回一个字段。如果您有兴趣检查一组其他值中是否存在单个值，请使用IN：

SELECT titleFROM booksWHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

这显然不同于LEFT-JOIN，您只想连接表A和表B中的内容，即使连接条件在表B中找不到任何匹配的记录，等等。

如果您只是担心速度，您必须检查您的数据库并编写一个好的查询，看看性能是否有任何显着差异。

小开

在大多数情况下，JOIN比子查询更快，子查询更快的情况非常罕见。

在JOIN中，RDBMS可以创建一个更适合您的查询的执行计划，并且可以预测应该加载哪些数据来处理并节省时间，这与子查询不同，它将运行所有查询并加载所有数据来进行处理。

子查询的好处是它们比JOIN更具可读性：这就是为什么大多数新SQL更喜欢它们；这是简单的方法；但是在性能方面，JOINS在大多数情况下更好，即使它们也不难阅读。

小开

使用EXPLAIN查看您的数据库如何对您的数据执行查询。这个答案中有一个巨大的“它取决于”……

PostgreSQL可以将一个子查询重写为一个连接，或者当它认为一个子查询的连接比另一个快时重写一个子查询的连接。这一切都取决于数据、索引、相关性、数据量、查询等。

小开

首先，要比较这两个首先，您应该将查询和子查询区分开来：

  1. 一类总是具有用连接编写的相应等效查询的子查询
  2. 不能使用连接重写的一类子查询

为了第一堂课的查询一个好的RDBMS将看到连接和子查询是等价的，并将产生相同的查询计划。

现在连mysql都这么做了。

尽管如此，有时它不会，但这并不意味着连接总是赢——我在mysql中使用子查询时提高了性能。（例如，如果有一些东西阻止mysql规划器正确估计成本，如果规划器没有看到连接变体和子查询变体相同，那么子查询可以通过强制执行特定路径来优于连接）。

结论是，如果您想确定哪一个性能更好，您应该测试连接和子查询变体的查询。

对于第二类比较没有意义，因为这些查询不能使用连接重写，在这些情况下，子查询是执行所需任务的自然方式，您不应该歧视它们。

小开

从旧的Mambo CMS在非常大的数据库上运行：

SELECT id, aliasFROMmos_categoriesWHEREid IN (SELECTDISTINCT catidFROM mos_content);

0秒

SELECTDISTINCT mos_content.catid,mos_categories.aliasFROMmos_content, mos_categoriesWHEREmos_content.catid = mos_categories.id;

~3秒

一个解释表明，他们检查的行数完全相同，但一个需要3秒，一个几乎是即时的。故事的寓意？如果性能很重要（什么时候不是？），尝试多种方法，看看哪一种最快。

而且…

SELECTDISTINCT mos_categories.id,mos_categories.aliasFROMmos_content, mos_categoriesWHEREmos_content.catid = mos_categories.id;

0秒

同样，同样的结果，同样的行数。我的猜测是DISTINCTmos_content.catid比DISTINCTmos_categories.id需要更长的时间来计算。

小开

SQL服务器的MSDN文档说

许多包含子查询的Transact-SQL语句也可以表述为连接。其他问题只能和子查询一起提出。在Transact-SQL中，包含子查询的语句和不包含子查询的语义等效版本之间通常没有性能差异。但是，在某些必须检查是否存在的情况下，连接会产生更好的性能。否则，必须为外部查询的每个结果处理嵌套查询，以确保消除重复。在这种情况下，连接方法会产生更好的结果。

所以如果你需要像 

select * from t1 where exists select * from t2 where t2.parent=t1.id

尝试使用连接代替。在其他情况下，它没有区别。

我说：为子查询创建功能消除了混乱的问题，并允许您为子查询实现额外的逻辑。所以我建议尽可能为子查询创建函数。

代码中的混乱是一个大问题，业界几十年来一直在努力避免它。

小开

如今，许多数据库可以优化子查询和连接。因此，你只需要使用解释来检查你的查询，看看哪个更快。如果性能没有太大差异，我更喜欢使用子查询，因为它们简单易懂。

小开

MySQL版本：5.5.28-0ubuntu0.12.04.2-log

我还认为JOIN总是比MySQL中的子查询更好，但EXPLAIN是做出判断的更好方法。这是一个子查询比JOIN工作得更好的示例。

这是我的3个子查询查询：

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_dateFROM `vote-ranked-listory` vrlINNER JOIN lists l ON l.list_id = vrl.list_idINNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULLAND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULLAND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULLORDER BY vrl.moved_date DESC LIMIT 200;

解释显示：

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+| id | select_type        | table    | type   | possible_keys                                       | key          | key_len | ref                                             | rows | Extra                    |+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+|  1 | PRIMARY            | vrl      | index  | PRIMARY                                             | moved_date   | 8       | NULL                                            |  200 | Using where              ||  1 | PRIMARY            | l        | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              ||  1 | PRIMARY            | vrlih    | eq_ref | PRIMARY                                             | PRIMARY      | 9       | ranker.vrl.list_id,ranker.vrl.ontology_id,const |    1 | Using where              ||  1 | PRIMARY            | lbs      | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              ||  4 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index ||  3 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

与JOIN相同的查询是：

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_dateFROM `vote-ranked-listory` vrlINNER JOIN lists l ON l.list_id = vrl.list_idINNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000AND lt1.list_id IS NULL AND lt2.tag_id IS NULLORDER BY vrl.moved_date DESC LIMIT 200;

输出是：

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+| id | select_type | table | type   | possible_keys                                       | key          | key_len | ref                                         | rows | Extra                                        |+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+|  1 | SIMPLE      | lt3   | ref    | list_tag_key,list_id,tag_id                         | tag_id       | 5       | const                                       | 2386 | Using where; Using temporary; Using filesort ||  1 | SIMPLE      | l     | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.lt3.list_id                          |    1 | Using where                                  ||  1 | SIMPLE      | vrlih | ref    | PRIMARY                                             | PRIMARY      | 4       | ranker.lt3.list_id                          |  103 | Using where                                  ||  1 | SIMPLE      | vrl   | ref    | PRIMARY                                             | PRIMARY      | 8       | ranker.lt3.list_id,ranker.vrlih.ontology_id |   65 | Using where                                  ||  1 | SIMPLE      | lt1   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index; Not exists         ||  1 | SIMPLE      | lbs   | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                          |    1 | Using where                                  ||  1 | SIMPLE      | lt2   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index                     |+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

rows列的比较说明了差异，并且使用JOIN的查询使用Using temporary; Using filesort。

当然，当我运行两个查询时，第一个在0.02秒内完成，第二个即使在1分钟后也没有完成，所以EXPLAIN正确地解释了这些查询。

如果我没有list_tag表上的INNER JOIN，即如果我删除 

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL

从第一个查询开始，相应地：

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

从第二个查询中，EXPLAIN为两个查询返回相同数量的行数，并且这两个查询的运行速度相同。

小开

只有当第二个连接表的数据明显多于主表时，才会看到差异。我有过如下经历…

我们有一个包含10万个条目的用户表和他们的成员数据（友谊）大约30万个条目。这是一个连接语句，以便获取朋友和他们的数据，但是有很大的延迟。但是在成员表中只有少量数据的情况下，它工作得很好。一旦我们将其更改为使用子查询，它就工作得很好。

但与此同时，连接查询正在与其他表一起工作，这些表的条目比主表少。

所以我认为连接和子查询语句工作得很好，这取决于数据和情况。

小开

子查询能够即时计算聚合函数。找到这本书的最低价格，并得到所有以这个价格出售的书。1）使用子查询：

SELECT titles, priceFROM Books, OrdersWHERE price =(SELECT MIN(price)FROM Orders) AND (Books.ID=Orders.ID);

2）使用JOIN

SELECT MIN(price)FROM Orders;-----------------2.99
SELECT titles, priceFROM Books bINNER JOIN  Orders oON b.ID = o.IDWHERE o.price = 2.99;
小开
最佳答案

摘自MySQL手册（13.2.10.11将子查询重写为连接）：

LEFT[OUTER]JOIN可以比等效的子查询更快，因为服务器可能能够更好地优化它-这一事实并不仅限于MySQL Server。

所以子查询可能比LEFT [OUTER] JOIN慢，但在我看来，它们的强度略高于易读性。

小开

我认为在引用的答案中强调不足的是重复问题和可能来自特定（使用）案例的有问题的结果。

（虽然Marcelo Cantos提到过）

我将引用斯坦福大学关于SQL的Lagunita课程的例子。

学生桌

+------+--------+------+--------+| sID  | sName  | GPA  | sizeHS |+------+--------+------+--------+|  123 | Amy    |  3.9 |   1000 ||  234 | Bob    |  3.6 |   1500 ||  345 | Craig  |  3.5 |    500 ||  456 | Doris  |  3.9 |   1000 ||  567 | Edward |  2.9 |   2000 ||  678 | Fay    |  3.8 |    200 ||  789 | Gary   |  3.4 |    800 ||  987 | Helen  |  3.7 |    800 ||  876 | Irene  |  3.9 |    400 ||  765 | Jay    |  2.9 |   1500 ||  654 | Amy    |  3.9 |   1000 ||  543 | Craig  |  3.4 |   2000 |+------+--------+------+--------+

应用表格

（针对特定大学和专业的申请）

+------+----------+----------------+----------+| sID  | cName    | major          | decision |+------+----------+----------------+----------+|  123 | Stanford | CS             | Y        ||  123 | Stanford | EE             | N        ||  123 | Berkeley | CS             | Y        ||  123 | Cornell  | EE             | Y        ||  234 | Berkeley | biology        | N        ||  345 | MIT      | bioengineering | Y        ||  345 | Cornell  | bioengineering | N        ||  345 | Cornell  | CS             | Y        ||  345 | Cornell  | EE             | N        ||  678 | Stanford | history        | Y        ||  987 | Stanford | CS             | Y        ||  987 | Berkeley | CS             | Y        ||  876 | Stanford | CS             | N        ||  876 | MIT      | biology        | Y        ||  876 | MIT      | marine biology | N        ||  765 | Stanford | history        | Y        ||  765 | Cornell  | history        | N        ||  765 | Cornell  | psychology     | Y        ||  543 | MIT      | CS             | N        |+------+----------+----------------+----------+

让我们试着找到申请CS专业的学生的GPA分数（无论大学如何）

使用子查询：

select GPA from Student where sID in (select sID from Apply where major = 'CS');
+------+| GPA  |+------+|  3.9 ||  3.5 ||  3.7 ||  3.9 ||  3.4 |+------+

此结果集的平均值为：

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');
+--------------------+| avg(GPA)           |+--------------------+| 3.6800000000000006 |+--------------------+

使用连接：

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+------+| GPA  |+------+|  3.9 ||  3.9 ||  3.5 ||  3.7 ||  3.7 ||  3.9 ||  3.4 |+------+

此结果集的平均值：

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+-------------------+| avg(GPA)          |+-------------------+| 3.714285714285714 |+-------------------+

很明显，第二次尝试在我们的用例中产生了误导性的结果，因为它计算了计算平均值的重复项。同样明显的是，在基于连接的语句中使用distinct将没有消除问题，因为它将错误地保留3.9分数的三次出现中的一次。正确的情况是考虑两个（2）次出现3.9分数，因为我们实际上有两个（2）名学生符合我们的查询标准。

似乎在某些情况下，除了任何性能问题之外，子查询是最安全的方式。

小开

根据我的观察，就像两个案例一样，如果一个表的记录少于100,000条，那么连接将快速工作。

但是如果一个表有超过100,000条记录，那么子查询是最好的结果。

我有一个表，上面有500,000条记录，我在下面的查询中创建了它的结果时间就像

SELECT *FROM crv.workorder_details wdinner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

结果：13.3秒

select *from crv.workorder_detailswhere workorder_id in (select workorder_id from crv.workorder)

结果：1.65秒

小开

在2010年，我会加入这个问题的作者，并强烈投票支持JOIN，但随着更多的经验（尤其是在MySQL方面），我可以声明：是的，子查询可以更好。我在这里阅读了多个答案；一些声明的子查询更快，但它缺乏一个好的解释。我希望我能提供一个（非常）晚的答案：

首先，让我说最重要的：有不同形式的子查询

第二个重要声明：大小很重要

如果您使用子查询，您应该意识了解DB-Server如何执行子查询。尤其是如果子查询被评估一次或每一行！另一方面，现代DB-Server能够进行大量优化。在某些情况下，子查询有助于优化查询，但较新版本的DB-Server可能会使优化过时。

选择字段中的子查询

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

请注意，对foo中的每个结果行执行子查询。
如果可能，请避免这种情况；它可能会大大降低您对大型数据集的查询速度。但是，如果子查询没有引用foo，则DB-server可以将其优化为静态内容，并且只能评估一次。

WHE-语句中的子查询

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

如果你幸运的话，数据库会在内部将其优化为JOIN。如果不是，你的查询在巨大的数据集上会变得非常非常慢，因为它会对foo中的每一行执行子查询，而不仅仅是像选择类型那样的结果。

Join语句中的子查询

SELECT moo, barFROM fooLEFT JOIN (SELECT MIN(bar), me FROM wilco GROUP BY me) ON moo = me

这很有趣。我们将JOIN和子查询结合起来。这里我们得到了子查询的真正优势。想象一个数据集，wilco中有数百万行，但只有少数不同的me。我们现在有一个较小的临时表来连接，而不是针对一个巨大的表连接。这可以根据数据库大小导致更快的查询。你可以用CREATE TEMPORARY TABLE ...和INSERT INTO ... SELECT ...产生同样的效果，这可能在非常复杂的查询上提供更好的易读性（但可以将数据集锁定在可重复的读取隔离级别）。

嵌套子查询

SELECT VARIANCE(moo)FROM (SELECT moo, CONCAT(roger, wilco) AS barFROM fooHAVING bar LIKE 'SpaceQ%') AS temp_fooGROUP BY moo

您可以在多个级别中嵌套子查询。如果您必须对结果进行分组或更改，这可以对巨大的数据集有所帮助。通常DB-Server会为此创建一个临时表，但有时您不需要对整个表进行一些操作，而只需要对结果集进行一些操作。根据表的大小，这可能会提供更好的性能。

结论

子查询不能替代JOIN，你不应该这样使用它们（尽管可能）。在我的愚见中，子查询的正确使用是用作CREATE TEMPORARY TABLE ...的快速替换。一个好的子查询减少了一个数据集，这是你在JOIN的ON语句中无法完成的。如果子查询具有关键字GROUP BY或DISTINCT之一，并且最好不在选择字段或where语句中，那么它可能会大大提高性能。

小开

我只是在想同样的问题，但我在from部分使用子查询。我需要连接和查询大表，“从”表有2800万记录，但结果只有128所以小结果大数据！我在上面使用MAX（）函数。

首先我使用LEFT JOIN，因为我认为这是正确的方法，mysql可以优化等。第二次只是为了测试，我重写以针对JOIN进行子选择。

LEFT JOIN运行时：1.12sSUB-SELECT运行时：0.06s

子选择比连接快18倍！就在chokito广告中。子选择看起来很糟糕，但结果…

小开
  • 一般规则是加入在大多数情况下（99%）更快。
  • 数据表越多，子查询就越慢。
  • 数据表越少，子查询的速度与加入相当。
  • 子查询更简单，更容易理解，更容易阅读。
  • 大多数Web和应用程序框架及其“ORM”和“活动记录”生成带有子查询的查询，因为使用子查询更容易分解责任、维护代码等。
  • 对于较小的网站或应用程序子查询是可以的，但对于较大的网站和应用程序，您通常必须将生成的查询重写为加入查询，特别是如果查询在查询中使用许多子查询。

有些人说“一些RDBMS可以将子查询重写为加入或加入重写为子查询，当它认为一个比另一个快时”，但这种说法适用于简单的情况，当然不适用于子查询的复杂查询，这实际上会导致性能问题。

小开

如果您想使用连接加速查询：

对于"内连接/连接"，不要使用where条件，而是在“ON”条件下使用它。例如：

     select id,name from table1 ajoin table2 b on a.name=b.namewhere id='123'
Try,
select id,name from table1 ajoin table2 b on a.name=b.name and a.id='123'

对于“左/右连接”，不要在“ON”条件下使用，因为如果您使用左/右连接，它将获取任何一张表的所有行。因此，在“On”中使用它没有用。所以，尝试使用“在哪里”条件

小开

这取决于几个因素，包括您正在运行的特定查询、数据库中的数据量。子查询首先运行内部查询，然后再次从结果集中过滤出实际结果。而在连接中运行并一次性生成结果。

最好的策略是您应该测试连接解决方案和子查询解决方案以获得优化的解决方案。

小开

我不是一个关系型数据库专家，所以对这个问题持怀疑态度。

关于子查询与连接的一般思想是评估较大查询所采用的路径。

为了执行较大的查询，必须首先执行每个单独的子查询，然后将结果集存储为较大查询与之交互的临时表。

这个临时表没有索引，因此，任何比较都需要扫描整个结果集。

相比之下，当您使用连接时，所有索引都在使用中，因此，比较需要遍历索引树（或哈希表），这在速度方面要便宜得多。

现在，我不知道最新版本的最流行的关系引擎是否会执行反向评估，并将必要的元素加载到临时表中，作为一种优化方法。


提交答案