我是一个老派的MySQL用户,总是更喜欢JOIN而不是子查询。但是现在每个人都用子查询,我讨厌它;我不知道为什么。
我缺乏理论知识来判断是否有任何不同。子查询是否与JOIN一样好,因此没有什么可担心的?
我是一个老派的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.
在某些情况下,除了性能问题,子查询似乎是最安全的方法。
其他回答
首先,为了比较这两个,首先你应该区分查询和子查询:
一个子查询类,它总是使用连接编写相应的等效查询 不能使用连接重写的子查询类
对于第一类查询,一个好的RDBMS将把联接查询和子查询视为等效的,并将产生相同的查询计划。
现在甚至mysql也这么做了。
尽管如此,有时它并不会,但这并不意味着连接总是会赢-我有在mysql中使用子查询提高性能的情况。(例如,如果有一些东西阻止mysql计划器正确估计成本,如果计划器没有看到连接变量和子查询变量相同,那么子查询可以通过强制某个路径来优于连接)。
结论是,如果您想确定哪一种查询性能更好,就应该同时测试连接和子查询变量。
对于第二个类,比较没有意义,因为这些查询不能使用连接重写,在这种情况下,子查询是完成所需任务的自然方式,您不应该歧视它们。
使用EXPLAIN查看数据库如何对数据执行查询。这个答案中有一个很大的“视情况而定”……
PostgreSQL可以将子查询重写为连接,或将连接重写为子查询,如果它认为其中一个比另一个快。这完全取决于数据、索引、相关性、数据量、查询等。
摘自MySQL手册(13.2.10.11将子查询重写为连接):
LEFT [OUTER] JOIN可以比等效的子查询更快,因为服务器可以更好地优化它——这不是MySQL服务器独有的事实。
所以子查询可能比LEFT [OUTER] JOIN慢,但在我看来,它们的优势是可读性略高。
在大多数情况下,join比子查询快,子查询比子查询快的情况非常罕见。
在join中,RDBMS可以为您的查询创建一个更好的执行计划,并可以预测应该加载哪些数据来处理并节省时间,不像子查询,它将运行所有的查询并加载所有的数据来进行处理。
子查询的好处是它们比join更可读:这就是为什么大多数新学习SQL的人更喜欢它们;这是简单的方法;但是当涉及到性能时,join在大多数情况下更好,尽管它们也不难读。
只有当第二个连接表的数据明显多于主表时,才会出现这种差异。我有过这样的经历……
我们有一个用户表,有10万个条目,他们的会员数据(友谊)大约有30万个条目。这是一个join语句,目的是获取好友及其数据,但有很大的延迟。但是当成员表中只有少量数据时,它工作得很好。一旦我们将其更改为使用子查询,它就可以正常工作。
但与此同时,连接查询正在处理比主表拥有更少条目的其他表。
所以我认为连接和子查询语句工作得很好,这取决于数据和情况。