我认为在引用的答案中没有强调的是重复的问题和可能由特定(使用)案例引起的有问题的结果。

(尽管马塞洛·坎托斯提到过)

我将引用斯坦福大学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.

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

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

然而，实际上，答案通常归结于性能。当给出连接和子查询时，一些优化器会很糟糕，而另一些则相反，这是特定于优化器、特定于dbms版本和特定于查询的。

从历史上看，显式连接通常会胜出，因此已经建立的智慧是连接更好，但优化器一直在变得更好，因此我更喜欢先以逻辑一致的方式编写查询，然后在性能限制的情况下重新构造查询。

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为两个查询返回相同的行数，并且这两个查询的运行速度相同。

只有当第二个连接表的数据明显多于主表时，才会出现这种差异。我有过这样的经历……

我们有一个用户表，有10万个条目，他们的会员数据(友谊)大约有30万个条目。这是一个join语句，目的是获取好友及其数据，但有很大的延迟。但是当成员表中只有少量数据时，它工作得很好。一旦我们将其更改为使用子查询，它就可以正常工作。

但与此同时，连接查询正在处理比主表拥有更少条目的其他表。

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

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

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

我只是在考虑同样的问题，但我在FROM部分使用子查询。 我需要连接和查询大表，“从”表有2800万条记录，但结果只有128个这样小的结果大数据!我在它上面使用MAX()函数。

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

LEFT JOIN运行时:1.12s SUB-SELECT运行时间:0.06秒

子选择比连接快18倍!只是在chokito广告。subselect看起来很糟糕，但结果…