Normal Join vs Join with Subqueries

Question

What is the best way for query with joins?

First join tables and then add where conditions
First add where conditions with subquery and then join

For example which one of the following queries have a better performance?

select * from person persons
         inner join role roles on roles.person_id_fk = persons.id_pk 
         where roles.deleted is null

or

select * from person persons
         inner join (select * from role roles where roles.deleted is null) as roles
         on roles.person_id_fk = persons.id_pk

The first version should perform better, because it avoids an explicit subquery. For educational purposes, you should run `EXPLAIN` on both of these queries to see for yourself how Postgres would evaluate each one. — Tim Biegeleisen, Aug 05 '20 at 05:07
Does this answer your question? [WHERE clause better execute before IN and JOIN or after](https://stackoverflow.com/questions/5463101/where-clause-better-execute-before-in-and-join-or-after) — Bryan, Aug 05 '20 at 05:08
@Bawpotter No I want the best way for join but this question is about order of query execution. — Omid, Aug 05 '20 at 05:25
I would be very surprised if there was any difference. I am pretty sure the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) will be identical for both — , Aug 05 '20 at 05:43

score 1 · Accepted Answer · answered Aug 05 '20 at 12:04

In a decent database, there should be no difference between the two queries. Remember, SQL is a descriptive language, not a procedural language. That is, a SQL SELECT statement describes the result set that should be returned. It does not specify the steps for creating it.

Your two queries are semantically equivalent and the SQL optimizer should be able to recognize that.

Of course, SQL optimizers are not omniscient. So, sometimes how you write a query does affect the execution plan. However, the queries that you are describing are turned into execution plans that have no concept of "subquery", so it is reasonable that they would produce the same execution plan.

Note: Some databases -- such as MySQL and MS Access -- do not have very good optimizers and such queries do produce different execution plans. Alas.

Normal Join vs Join with Subqueries

1 Answers1