子查询与内部连接——哪一个执行得更快? [英] Subqueries vs Inner joins - Which one executes faster?

问题描述

我希望可以发这样的帖子.

I hope it's ok to make a posting like this.

我使用 SQL 已经有一段时间了，工作人员一直在使用 2 种不同的方法来返回数据库中相同的数字或行.

I have been using SQL for quite some time and people at work have been using 2 different ways to return the same number or rows in a database.

例如:

SELECT Name
FROM
    Employees
WHERE
    DepartmentID IN (SELECT DepartmentID
                         FROM
                             Departments
                         WHERE
                             Department LIKE '%Engineering')

SELECT Employees.Name
FROM
    Departments
    INNER JOIN Employees
        ON Departments.DepartmentID = Employees.DepartmentID
WHERE
    Departments.Department LIKE '%Engineering'

两者都返回相同的数据.人们一直告诉我使用子查询是最好的方法.

Both return the same data. People have been telling me that using subqueries is the best way to do it.

我的问题是:这两个中哪一个会执行得更快?我的猜测是内连接，但我可能错了.

My question is this: Which of these 2 will execute faster? My guess would be the one with the inner join but I may be wrong.

谢谢.

推荐答案

在这种情况下，两者是等效的.但是，对于INNER JOIN的，如果选择了1个以上部门ID相同的部门，会多次返回该部门ID的员工.

In this case, both are equivalent. However, for the one with the INNER JOIN, if there is more than 1 department selected with the same DepartmentID, employees with that DepartmentID will be returned multiple times.

SQL 是一种声明性语言，这意味着该语言不应该说明应该如何执行查询，而应该说明应该找到什么结果.由 DMBS 决定如何执行.

SQL is a declarative language, which means that the language is not supposed to say how the query should be performed, only what result should be found. It is up to the DMBS to work out how to perform it.

一个体面的 SQL 数据库可能会优化它们以做相同或相似的事情.

A decent SQL database will probably optimize them to both do the same or similar things.

要检查它们是否在做同样的事情，请对查询运行 EXPLAIN.

To check if they are doing the same thing, run EXPLAIN on the query.

如果它们具有相同的步骤，显然它们将花费相同的时间来运行.否则，您将看到数据库以不同的方式处理这些数据.DBMS 如何优化它取决于实现.所以最好的方法是使用EXPLAIN.

If they have the same steps, obviously they will take the same amount of time to run. Otherwise, you will see what ways the database is treating the differently. How the DBMS optimizes it is implementation dependent. So the best way is to use EXPLAIN.

注意:EXPLAIN 是一个 SQL 命令，就像 SELECT 等.参见 http://www.sql.org/sql-database/postgresql/manual/sql-explain.html.

Note: EXPLAIN is an SQL command, just like SELECT etc. See http://www.sql.org/sql-database/postgresql/manual/sql-explain.html.

假设它以相同的步骤执行，您编写它的方式将是您认为最清晰的方式.

Assuming it executes with the same steps, the way you write it will be the way you think appears clearest what the intention is.

这篇关于子查询与内部连接——哪一个执行得更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！