Emad-ud-deen Emad-ud-deen - 2 years ago 145
SQL Question

Subqueries vs Inner joins - Which one executes faster?

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

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.

For example:

DepartmentID IN (SELECT DepartmentID
Department LIKE '%Engineering')

SELECT Employees.Name
INNER JOIN Employees
ON Departments.DepartmentID = Employees.DepartmentID
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.


Answer Source

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 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.

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

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

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.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download