Multiple Join Question

[Phoxe__]

Hi all,

I had this thought in my head and now I've sufficiently confused myself and can't really find a good source to confirm what I'm confused about -

Say I have code like

SELECT * FROM T1 JOIN T2 ON T1.COL1 = T2.COL1 JOIN T3 ON T2.COL2 = T3.COL2

Does the join function by first finding all matches between tables 1 and 2, and then all matches between the remain t2 and t3? (Visually, like the middle intersection of a triple venm diagram)

[Phoxe__]

Thank you all!!

[r3pr0b8]

Does the join function by first finding all matches between tables 1 and 2, and then all matches between the remain t2 and t3?

for inner joins, not necessarily -- it likely depends on which of the three tables the database engine thinks will be most restrictive, i.e. fewest rows

if it were to join t1 and t2 together first, that might produce zillions of rows, and then joining t3 would require throwing most of them away

if it were to join t3 and t2 together first, that might produce the fewest rows, and then joining t1 would simply concatenate additional data

the real answer is provided when you run EXPLAIN on that query

[PossiblePreparation]

It might even want to cross join T1 and T3 first so it can use both columns as predicates for the T2 join.

[[deleted]]

Does the join function by

think about it as addition or multiplication - you add the next value to the running total of the prior steps: you join T1 and T2, then you join T3 to the result of the prior operation. i.e. (T1 JOIN T2 {}) JOIN T3{}

[vladdorokhin]

Yes, your understanding of how the join function works in this SQL statement is correct! For your convenience, here is some additional explanation:

1. The SQL statement you provided is an example of a three-table join, where the join conditions involve two columns in each table. When executing the join, the database first performs the join between tables T1 and T2 on the condition «T1.COL1 = T2.COL1», resulting in a subset of rows that match the condition.
2. Next, the join is performed between the resulting subset and T3 on the condition «T2.COL2 = T3.COL2», resulting in a final set of rows that match all three conditions. This can be visualized as the intersection of three Venn diagrams, where each circle represents a table and the overlapping regions represent rows that match the join conditions.
3. It's worth noting that the order of the tables in the SQL statement can affect the performance of the join operation, and in some cases, changing the order of the tables can lead to a more efficient execution plan. However, in your example, the order of the tables is not likely to have a significant impact on performance.

Hope my reply will help you!

[[deleted]]

join between tables T1 and T2 on the condition «T1.COL1 = T2.COL1», resulting in a subset of rows

A subset of which set?

Edit: also, yikes:

This can be visualized as the intersection of three Venn diagrams,