PostgreSQL服务端编程子查询
我是一个老派的MYSQL用户，并且总是更喜欢JOIN不是子查询。 但是现在每个人都使用子查询，我讨厌它; 我不知道为什么。
如果存在任何差异，我缺乏理论知识来判断自己。 子查询和JOIN一样好，因此没有什么可担心的吗？
#1楼
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 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楼
仅当第二个连接表的数据明显多于主表时才会出现差异。 我有过如下经历......
我们有一个十万条目的用户表和他们的会员数据（友谊）约三十万条目。 这是一个加入声明，以便接收朋友和他们的数据，但有很大的延迟。 但是在成员资格表中只有少量数据的情况下工作正常。 一旦我们将其更改为使用子查询，它就可以正常工作。
但同时，连接查询正在使用条目少于主表的其他表。
所以我认为连接和子查询语句工作正常，它取决于数据和情况。
#3楼
子查询是解决形式问题的逻辑上正确的方法，“从A获取事实，以B中的事实为条件”。 在这种情况下，在子查询中粘贴B比进行连接更合乎逻辑。 从实际意义上说，它也更安全，因为你不必因为多次匹配B而从A中获取重复的事实时要谨慎。
然而，实际上，答案通常归结为性能。 一些优化器在给出连接和子查询时会吮吸柠檬，而另一些优化者则以另一种方式吮吸柠檬，这是特定于优化器，特定于DBMS的版本和查询特定的。
从历史上看，显式连接通常会获胜，因此连接的既定智慧更好，但优化器一直在变得越来越好，所以我更喜欢先以逻辑连贯的方式编写查询，然后在性能限制要求时进行重组。
#4楼
子查询通常用于将单个行作为原子值返回，但它们可用于将值与多个行与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中找到任何匹配的记录，等等。
如果您只是担心速度，则必须检查数据库并编写一个好的查询，看看性能是否有任何显着差异。
#5楼
在大多数情况下， JOIN比子查询更快，并且子查询的速度非常快。
在JOIN RDBMS可以创建一个更适合您的查询的执行计划，并且可以预测应该加载哪些数据以进行处理并节省时间，这与子查询不同，在子查询中它将运行所有查询并加载所有数据以执行处理。
子查询的好处是它们比JOIN更具可读性：这就是大多数新SQL用户更喜欢它们的原因; 这是简单的方法; 但是在性能方面，JOINS在大多数情况下都更好，即使它们也不难读。
#6楼
使用EXPLAIN查看数据库如何对数据执行查询。 在这个答案中有一个巨大的“取决于”......
当PostgreSQL认为一个子查询比另一个更快时，它可以将子查询重写为连接或子查询的连接。 这一切都取决于数据，索引，相关性，数据量，查询等。
#7楼
首先，要比较两者，首先应将查询与子查询区分开来：
- 一类子查询，始终具有使用连接编写的相应等效查询
- 一类无法使用连接重写的子查询
对于第一类查询，良好的RDBMS将联接和子查询视为等效，并将生成相同的查询计划。
这些天甚至mysql都这样做。
尽管如此，有时却没有，但这并不意味着连接总是会赢 - 我在mysql中使用子查询提高了性能。 （例如，如果存在阻止mysql规划器正确估计成本的事情，并且规划器没有看到连接变量和子查询变量相同，那么子查询可以通过强制某个路径来胜过连接）。
结论是，如果要确定哪个更好，那么您应该测试连接和子查询变体的查询。
对于第二个类 ，比较没有意义，因为这些查询不能使用连接重写，在这些情况下，子查询是执行所需任务的自然方式，您不应该区别它们。
#8楼
子查询具有即时计算聚合函数的能力。 例如，查找该书的最低价格，并获得以此价格出售的所有书籍。 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;
#9楼
摘自MySQL手册 （ 13.2.10.11重写子查询作为连接 ）：
LEFT [OUTER] JOIN可以比等效的子查询更快，因为服务器可能能够更好地优化它 - 这一事实并非仅针对MySQL Server。
所以子查询可能比LEFT [OUTER] JOIN慢，但在我看来，它们的强度略高于可读性。
#10楼
我认为引用答案中未充分强调的是特定（使用）案例可能产生的重复和问题结果的问题。
（虽然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）次出现。
在某些情况下，除了任何性能问题之外，在某些情况下，子查询是最安全的方式。
#11楼
根据我的观察，如两个案例，如果一个表有少于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秒
#12楼
在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服务器优化为静态内容，并且只能被评估一次。
Where语句中的子查询
SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)
如果幸运的话，DB会在内部将其优化为JOIN 。 如果没有，您的查询将在巨大的数据集上变得非常非常慢，因为它将为foo每一行执行子查询，而不仅仅是select-type中的结果。
Join语句中的子查询
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 ，并且最好不位于select字段或where语句中，那么它可能会大大提高性能。
#13楼
我只是考虑同样的问题，但我在FROM部分使用子查询。 我需要从大表连接和查询，“slave”表有2800万条记录，但结果只有128个这么小的结果大数据！ 我正在使用MAX（）函数。
首先我使用LEFT JOIN，因为我认为这是正确的方法，mysql可以优化等。第二次只是为了测试，我重写为JOIN的子选择。
LEFT JOIN运行时：1.12s SUB-SELECT运行时：0.06s
subselect比连接快18倍！ 就在chokito adv。 该子选择看起来很糟糕，但结果......
#14楼
- 一般规则是在大多数情况下连接速度更快（99％）。
- 数据表越多， 子查询就越慢。
- 数据表越少， 子查询的速度与连接速度相同。
- 子查询更简单，更易于理解，更易于阅读。
- 大多数Web和应用程序框架及其“ORM”和“活动记录”都会使用子查询生成查询，因为子查询更容易分担责任，维护代码等。
- 对于较小的网站或应用程序子查询是可以的，但对于较大的网站和应用程序，您通常必须重写生成的查询以加入查询，特别是如果查询在查询中使用了许多子查询。
有些人说“有些RDBMS可以将子查询重写为连接，或者当它认为一个子查询比另一个更快时可以连接到子查询 。”但是这个语句适用于简单的情况，当然不适用于带有子查询的复杂查询，这实际上导致了表现上的问题。
#15楼
如果您想使用join加速查询：
对于“内部连接/连接”，不要使用where条件而是在“ON”条件下使用它。 例如：
select id,name from table1 a
join table2 b on a.name=b.name
where id='123'
Try,
select id,name from table1 a
join table2 b on a.name=b.name and a.id='123'
对于“左/右连接”，不要在“开”状态下使用，因为如果使用左/右连接，它将获得任何一个表的所有行。因此，不要在“开”中使用它。 所以，尝试使用“Where”条件
#16楼
在旧的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更长的时间来计算。
#17楼
包含子查询的许多Transact-SQL语句也可以表示为连接。 其他问题只能通过子查询提出。 在Transact-SQL中，包含子查询的语句与不包含子查询的语义等效版本之间通常没有性能差异。 但是，在某些必须检查存在的情况下，连接会产生更好的性能。 否则，必须为外部查询的每个结果处理嵌套查询，以确保消除重复项。 在这种情况下，联接方法会产生更好的结果。
所以，如果你需要类似的东西
select * from t1 where exists select * from t2 where t2.parent=t1.id
尝试使用连接。 在其他情况下，它没有任何区别。
我说：为子查询创建函数消除了cluttter的问题，并允许您为子查询实现额外的逻辑。 所以我建议尽可能为子查询创建函数。
代码杂乱是一个大问题，业界几十年来一直在努力避免它。
#18楼
目前，许多dbs可以优化子查询和连接。 因此，您只需使用解释检查您的查询，并查看哪一个更快。 如果性能没有太大差异，我更喜欢使用子查询，因为它们简单易懂。
- 点赞
- 收藏
- 关注作者
评论（0）