saiki4116 saiki4116 - 21 days ago 5
SQL Question

Inner Joins over Multiple Tables

I think, I could have a misconception, how join works on multiple tables. Lets say you have three tables A, B, C and you need to join them.

Here is the V1

SELECT A.NAME AS name1
,B.NAME AS name2
,C.NAME AS name3
FROM A
INNER JOIN B
ON A.id = B.id
INNER JOIN C
ON B.id = C.id


Here is V2

SELECT A.NAME AS name1
,D.NAME AS name2
,D.NAME AS name3
FROM A
INNER JOIN (
SELECT B.NAME AS name2
,C.NAME AS name3
FROM B
INNER JOIN C
ON B.id = C.id
) AS D
ON A.id = D.id


Is there any performance difference between the two versions?
Even though the first query looks cleaner, I need to build a Query Builder UI, restricting joins to only 2 tables helps.

Answer

In general, a problem with subselects is that it can require the RDBMS to create a temporary table to fulfill the subselect - and on a large enough table this can be a serious expense - and potentially can't use any indexes on the subselect to optimize the query, and then you're scanning the table.

Of course, the DDL and size of the tables aren't included, so the best way is to have Postgres explain what it plans to do and compare/contrast.

Comments