首页 文章

加入与子查询

提问于
浏览 次
670

我是一个老派的MySQL用户，并且总是首选 JOIN 超过子查询 . 但是现在每个人都使用子查询，我讨厌它;我不知道为什么 .

如果存在任何差异，我缺乏理论知识来判断自己 . 子查询和 JOIN 一样好，因此没有什么可担心的吗？

sql mysql subquery join

17 回答

  • 12

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

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

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

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

    结果：13.3秒

    select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

    结果：1.65秒

    回复于
  • 0

    • 一般规则是 joins 在大多数情况下更快（99％） .

    • 数据表越多， subqueries 越慢 .

    • 数据表越少， subqueries 的速度与 joins 相同 .

    • subqueries 更简单，更易于理解，更易于阅读 .

    • 大多数Web和应用程序框架及其"ORM"和"Active record"使用 subqueries 生成查询，因为 subqueries 更容易分担责任，维护代码等 .

    • 对于较小的网站或应用程序 subqueries 都可以，但对于较大的网站和应用程序，您通常必须将生成的查询重写为 join 查询，特别是如果查询在查询中使用了多个 subqueries .

    有些人说“有些RDBMS可以将 subquery 重写为 join 或 join 到 subquery ，当它认为一个比另一个更快时 . ”，但这个陈述适用于简单的情况，当然不适用于 subqueries 实际上导致的复杂查询绩效问题 .

    回复于
  • 11

    Taken from the MySQL manual （13.2.10.11 Rewriting Subqueries as Joins）：

    LEFT [OUTER] JOIN可能比同等的子查询更快，因为服务器可能能够更好地优化它 - 这个事实并非仅针对MySQL Server .

    因此子查询可能比LEFT [OUTER] JOINS慢，但在我看来，它们的强度可读性略高 .

    回复于
  • 11

    MSDN Documentation for SQL Server says

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

    所以，如果你需要类似的东西

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

    尝试使用连接 . 在其他情况下，它没有任何区别 .

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

    代码杂乱是一个大问题，业界几十年来一直致力于避免它 .

    回复于
  • 3

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

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

    回复于
  • 19

    首先，要比较两者，首先应将查询与子查询区分开来：

    • 一类子查询，它总是具有用连接写的相应等效查询

    • 一类无法使用连接重写的子查询

    For the first class 查询一个好的RDBMS将连接和子查询视为等效，并将生成相同的查询计划 .

    这些天甚至mysql都这样做 .

    尽管如此，有时却没有，但这并不意味着连接总是会赢 - 我在mysql中使用子查询提高了性能 . （例如，如果存在阻止mysql规划器正确估计成本的事情，并且规划器没有看到连接变量和子查询变量相同，则子查询可以通过强制某个路径来胜过连接 .

    结论是，如果要确定哪个更好，那么您应该测试连接和子查询变体的查询 .

    For the second class 这种比较毫无意义，因为这些查询无法使用连接进行重写，在这些情况下，子查询是执行所需任务的自然方式，您不应该区别对待它们 .

    回复于
  • 15

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

    SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

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

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

    这显然不同于LEFT-JOIN，你只想加入表A和B的东西，即使连接条件没有在表B中找到任何匹配的记录，等等 .

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

    回复于
  • 118

    目前，许多dbs可以优化子查询和连接 . 因此，您只需使用说明检查您的查询，并查看哪一个更快 . 如果性能没有太大差异，我更喜欢使用子查询，因为它们简单易懂 .

    回复于
  • 40

    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_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER 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.5 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

    EXPLAIN显示：

    +----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 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_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER 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.5 
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL 
ORDER 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为两个查询返回相同的行数，这两个查询的运行速度相同 .

    回复于
  • 2

    我认为引用答案中未充分强调的是 duplicates 问题以及可能由特定（使用）案例引起的问题结果 .

    （虽然马塞洛·坎托斯确实提到过）

    我将引用斯坦福大学关于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分数

    Using a subquery:

    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 |
+--------------------+

    Using a join:

    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 得分的三次出现中的一次 . 正确的情况是考虑 3.9 得分的 TWO (2) 次出现，因为我们实际上有 TWO (2) 学生的分数符合我们的查询标准 .

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

    回复于
  • 115

    仅当第二个连接表的数据明显多于主表时才会出现差异 . 我有过如下经历......

    我们有一个十万条目的用户表和他们的会员数据（友谊）约三十万条目 . 这是一个加入声明，以便接收朋友和他们的数据，但有很大的延迟 . 但是在成员资格表中只有少量数据的情况下工作正常 . 一旦我们将其更改为使用子查询，它就可以正常工作 .

    但同时，连接查询正在使用条目少于主表的其他表 .

    所以我认为连接和子查询语句工作正常，它取决于数据和情况 .

    回复于
  • 11

    子查询能够快速计算聚合函数 . 例如 . 找到这本书的最低价格，并获得以这个价格出售的所有书籍 . 1）使用子查询：

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

    2）使用JOIN

    SELECT MIN(price)
     FROM Orders;
-----------------
2.99

SELECT titles, price
FROM Books b
INNER JOIN  Orders o
ON b.ID = o.ID
WHERE o.price = 2.99;
    回复于
  • 334

    在大多数情况下， JOIN s比子查询更快，并且子查询的速度非常快 .

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

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

    回复于
  • 19

    我只是考虑同样的问题，但我在FROM部分使用子查询 . 我需要从大表中连接和查询，“slave”表有2800万条记录，但结果只有128条那么小的结果大数据！我正在使用MAX（）函数 .

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

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

    subselect比连接快18倍！就在chokito adv . 该子选择看起来很糟糕，但结果......

    回复于
  • 3

    在2010年，我本来会加入这个问题的作者，并会强烈投票支持 JOIN . 但是有了更多的经验（特别是在MySQL中），我可以说：是的子查询可以更好 . 我在这里读过多个答案 . 有人说，子查询更快，但缺乏一个很好的解释 . 我希望我能提供这个（非常）迟到的答案：

    首先，让我说最重要的： There are different forms of sub-queries

    第二个重要声明： Size matters

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

    选择字段中的子查询

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

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

    Where-statement中的子查询

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

    如果幸运的话，DB会在内部将其优化为 JOIN . 如果没有，您的查询将在大型数据集上变得非常非常慢，因为它将执行 foo 中每一行的子查询，而不仅仅是select-type中的结果 .

    Join-statement中的子查询

    SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

    这是有趣的 . 我们将 JOIN 与子查询结合起来 . 在这里，我们获得了子查询的真正优势 . 想象一下在 wilco 中有数百万行的数据集，但只有少数不同的 me . 我们现在有一个较小的临时表来加入，而不是加入一个巨大的表 . 这可以导致更快的查询，具体取决于数据库大小 . 您可以使用 CREATE TEMPORARY TABLE ... 和 INSERT INTO ... SELECT ... 获得相同的效果，这可以在非常复杂的查询上提供更好的可读性（但可以将数据集锁定在可重复的读隔离级别） .

    嵌套子查询

    SELECT moo, bar
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      GROUP BY moo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  GROUP BY bar
  ORDER BY bar

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

    结论

    子查询不能替代 JOIN ，您不应该像这样使用它们（尽管可能） . 在我看来，正确使用子查询是用作 CREATE TEMPORARY TABLE ... 的快速替代 . 一个好的子查询以某种方式减少数据集，您无法在 JOIN 的 ON 语句中完成 . 如果子查询具有关键字 GROUP BY 或 DISTINCT 之一并且最好不位于选择字段或where语句中，则可能会大大提高性能 .

    回复于
  • 22

    子查询是解决形式问题的逻辑上正确的方法，“从A中获取事实，以B中的事实为条件” . 在这种情况下，在子查询中粘贴B比在连接中粘贴更具逻辑性 . 从实际意义上说，它也更安全，因为你不必因为多次匹配B而从A中获取重复的事实时要小心谨慎 .

    然而，实际上，答案通常归结为性能 . 一些优化器在给出连接与子查询时会吮吸柠檬，而另一些优化者则以另一种方式吮吸柠檬，这是特定于优化器，特定于DBMS的版本和查询特定的 .

    从历史上看，显式联接通常会获胜，因此加入的既定智慧更好，但优化者一直在变得更好，所以我更喜欢先以逻辑上连贯的方式编写查询，然后在性能限制保证这一点时进行重组 .

    回复于
  • 722

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

    SELECT id, alias
FROM
  mos_categories
WHERE
  id IN (
    SELECT
      DISTINCT catid
    FROM mos_content
  );

    0秒

    SELECT
  DISTINCT mos_content.catid,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

    ~3秒

    EXPLAIN显示它们检查完全相同的行数，但是一个需要3秒，一个接近瞬间 . 故事的道德启示？如果性能很重要（何时不是？），请尝试多种方式，看看哪一个最快 .

    和...

    SELECT
  DISTINCT mos_categories.id,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

    0秒

    同样，相同的结果，检查的行数相同 . 我的猜测是，DISTINCT mos_content.catid比DISTINCT mos_categories.id需要更长的时间来计算出来 .

    回复于

相关问题