联接vs联接像查询-它们等效吗? [英] Joins vs Join like queries - Are they equivalent?

问题描述

我希望这个问题被多次问及，有不同的曲折..我想尝试并获得对该主题的一般和全面的理解. (它属于编程SO吗?..)

I expect this question has been asked multiple times, with different twists.. I want to try and get a generic and comprehensive understanding of this topic though. (does it belong in programming SO? ..)

让我们说我有一个sports的表和一个matches的表.在其他字段中，matches都有一个sport_id列，这是一对多的关系.

Lets say I have a table for sports and a table for matches. matches, among other fields has a sport_id column, and this is a 1:many relationship.

让我们说一下我想列出在第X天有比赛的运动.我可以用我能想到的3种方式来做到这一点.

Lets say I want to list out sports which have matches on day X. I could do this in 3 ways that I can think of..

嵌套查询-易于推理吗?

Nested queries - easy to reason?

SELECT * 
FROM sports 
WHERE id IN (SELECT sport_id FROM matches WHERE <DATE CHECK>)

从何处/何处-易于编写?

From/where - easy to write?

SELECT sports.* 
FROM sports, matches 
WHERE sports.id = matches.sport_id 
  AND <DATE CHECK>

加入-我不太熟悉，因此请原谅任何错误

Joins - I am not too familiar, so forgive any mistakes

SELECT * 
FROM sports 
JOIN matches ON sports.id = matches.sport_id 
WHERE <DATE CHECK>

基于Join的变体，可能还有其他方法可能更适合这里，也许是内部联接.

There might be other methods based on variations of Join which might be better suited here, inner join perhaps..

我想知道的是如何在

1. 等效响应(返回相同的行?)
2. 数据库性能
3. 全部都是1个查询/网络呼叫还是?
4. 这些答案是否与数据库引擎相关?
5. 我该如何选择?
6. #3的#2语法糖是吗?是#1?还是在某些/所有情况下将它们优化为#3?

推荐答案

第二种形式和第三种形式完全等效(除非您在第三种形式中有一个逗号). FROM sports, matches是隐式联接，FROM sports JOIN matches是显式联接.隐式联接是较早的形式，显式联接是更现代的形式，并且通常被数据库专家青睐.

The second and third forms are completely equivalent (except you have an extra comma in the third version). FROM sports, matches is an implicit join, FROM sports JOIN matches is an explicit join. Implicit joins are the earlier form, explicit joins are more modern and generally preferred by database experts.

与WHERE IN的版本几乎相同，但是有一些区别.首先，SELECT *将返回联接中两个表中的列，但仅返回WHERE IN查询中的sports中的列.其次，如果sports中的行与matches中的多行匹配，则联接将为每对匹配返回一行(它执行叉积)，而WHERE IN只会从sports返回该行一次不管有多少个匹配项.

The version with WHERE IN is almost the same, but there are some differences. First, SELECT * will return columns from both tables in the join, but will only return columns from sports in the WHERE IN query. Second, if a row in sports matches multiple rows in matches, the joins will return a row for each pair of matches (it performs a cross product), while WHERE IN will just return the row from sports once regardless of how many matches there are.

性能差异取决于实现.显式联接和隐式联接之间应该没有任何区别，它们只是语法糖.但是，数据库并非总是以相同的方式优化WHERE IN查询.例如，当我在MySQL中使用EXPLAIN时，WHERE IN查询通常会对外部表执行完整扫描，使该列与子查询中表的索引匹配，即使该子查询可能只返回一小部分行数.我想有人告诉我，最近的MySQL版本在此方面更好.

Performance differences are implementation dependent. There shouldn't be any difference between the explicit and implicit joins, they're just syntactic sugar. However, databases don't always optimize the WHERE IN queries the same. For instance, when I've used EXPLAIN with MySQL, WHERE IN queries often perform a full scan over the outer table, matching the column against the index of the table in the subquery, even though the subquery might only return a small number of rows. I think some people have told me that recent MySQL versions are better at this.

它们都只是1个网络通话.所有查询只是对数据库服务器的一次调用.

They will all be just 1 network call. All queries are just a single call to the database server.

顺便说一句，您还没有列出另一种形式，将WHERE EXISTS与相关子查询一起使用.

BTW, there's another form that you didn't list, using WHERE EXISTS with a correlated subquery.

SELECT *
FROM sports s
WHERE EXISTS (SELECT 1 
              FROM matches m 
              WHERE s.id = m.sport_id AND <DATE CHECK>)

此功能与JOIN之间的性能差异将再次取决于实现.

Performance differences between this and JOIN will again be implementation-dependent.

这篇关于联接vs联接像查询-它们等效吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！