Which join is faster in SQL

The join operation

An SQL query walks into a bar and sees two tables. He walks up to them and asks "Can I join you?"

- Source: Unknown

A join operation transforms data from a normalized model into a denormalized form that is needed for a specific task. Since it brings together scattered data fragments, the speed of a join operation is particularly sensitive to hard drive latencies. Again, careful indexing is the best way to improve response times. With a join, however, the correct indexing depends on which of the three common algorithms is used.

However, one thing applies equally to all join algorithms: They only process two tables at a time. A SQL join with several tables is carried out in stages: first two tables are joined, then the intermediate result with the next. And so on.

Although the order of the individual join operations has no influence on the end result, it still has an impact on performance. The optimizer therefore evaluates all variants with a cost value and ultimately selects the best order. However, this means that optimizing a complex query can itself become a performance problem. The more join operations there are, the more execution plan variants the optimizer has to evaluate - in mathematical terms: n! (Faculty). However, this is not a problem if you use bind parameters.

Important

The more complex an SQL statement becomes, the more important bind parameters are.

Not using bind parameters is like recompiling a program every time.