我是一个老派的MySQL用户，总是更喜欢JOIN而不是子查询。但是现在每个人都用子查询，我讨厌它;我不知道为什么。
我缺乏理论知识来判断是否有任何不同。子查询是否与JOIN一样好，因此没有什么可担心的?
当前回答
我认为在引用的答案中没有强调的是重复的问题和可能由特定(使用)案例引起的有问题的结果。
(尽管马塞洛·坎托斯提到过)
我将引用斯坦福大学Lagunita SQL课程的例子。
学生表
+------+--------+------+--------+
| 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 |
+------+----------+----------------+----------+
让我们试着找出申请计算机科学专业的学生的平均绩点(不论大学)
使用子查询:
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 |
+-------------------+
It is obvious that the second attempt yields misleading results in our use case, given that it counts duplicates for the computation of the average value. It is also evident that usage of distinct with the join - based statement will not eliminate the problem, given that it will erroneously keep one out of three occurrences of the 3.9 score. The correct case is to account for TWO (2) occurrences of the 3.9 score given that we actually have TWO (2) students with that score that comply with our query criteria.
在某些情况下，除了性能问题，子查询似乎是最安全的方法。
其他回答
在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将其作为静态内容进行优化，并且只能求值一次。
where语句中的子查询
SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)
如果幸运的话，DB会在内部将其优化为一个JOIN。如果不这样做，你的查询在庞大的数据集上会变得非常非常慢，因为它会对foo中的每一行执行子查询，而不是像在select类型中那样只执行结果。
join语句中的子查询
SELECT moo, bar
FROM foo
LEFT JOIN (
SELECT MIN(bar), me FROM wilco GROUP BY me
) ON moo = me
This is interesting. We combine JOIN with a sub-query. And here we get the real strength of sub-queries. Imagine a dataset with millions of rows in wilco but only a few distinct me. Instead of joining against a huge table, we have now a smaller temporary table to join against. This can result in much faster queries depending on database size. You can have the same effect with CREATE TEMPORARY TABLE ... and INSERT INTO ... SELECT ..., which might provide better readability on very complex queries (but can lock datasets in a repeatable read isolation level).
嵌套的子查询
SELECT VARIANCE(moo)
FROM (
SELECT moo, CONCAT(roger, wilco) AS bar
FROM foo
HAVING bar LIKE 'SpaceQ%'
) AS temp_foo
GROUP BY moo
您可以在多个级别中嵌套子查询。如果你必须对结果进行分组或更改，这可以帮助处理庞大的数据集。通常，DB-Server为此创建一个临时表，但有时不需要对整个表进行某些操作，只需要对结果集进行操作。这可能会提供更好的性能，具体取决于表的大小。
结论
子查询不能代替JOIN，您不应该这样使用它们(尽管有可能)。在我看来，子查询的正确用法是作为CREATE TEMPORARY TABLE ....的快速替换一个好的子查询以一种在JOIN的ON语句中无法完成的方式减少数据集。如果子查询具有GROUP BY或DISTINCT关键字之一，并且最好不在选择字段或where语句中，那么它可能会大大提高性能。
首先，为了比较这两个，首先你应该区分查询和子查询:
一个子查询类，它总是使用连接编写相应的等效查询 不能使用连接重写的子查询类
对于第一类查询，一个好的RDBMS将把联接查询和子查询视为等效的，并将产生相同的查询计划。
现在甚至mysql也这么做了。
尽管如此，有时它并不会，但这并不意味着连接总是会赢-我有在mysql中使用子查询提高性能的情况。(例如，如果有一些东西阻止mysql计划器正确估计成本，如果计划器没有看到连接变量和子查询变量相同，那么子查询可以通过强制某个路径来优于连接)。
结论是，如果您想确定哪一种查询性能更好，就应该同时测试连接和子查询变量。
对于第二个类，比较没有意义，因为这些查询不能使用连接重写，在这种情况下，子查询是完成所需任务的自然方式，您不应该歧视它们。
子查询通常用于将单行作为原子值返回，不过它们也可以用于用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中没有找到任何匹配的记录，等等。
如果你只是担心速度，你必须检查你的数据库，写一个好的查询，看看是否有显著的性能差异。
只有当第二个连接表的数据明显多于主表时，才会出现这种差异。我有过这样的经历……
我们有一个用户表，有10万个条目，他们的会员数据(友谊)大约有30万个条目。这是一个join语句，目的是获取好友及其数据，但有很大的延迟。但是当成员表中只有少量数据时，它工作得很好。一旦我们将其更改为使用子查询，它就可以正常工作。
但与此同时，连接查询正在处理比主表拥有更少条目的其他表。
所以我认为连接和子查询语句工作得很好，这取决于数据和情况。
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;
解释说明:
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 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;使用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为两个查询返回相同的行数，并且这两个查询的运行速度相同。
推荐文章
- 如何关闭mysql密码验证?
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql，而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
