CODEWITHSUNDEEP
sqlsql-serverleft-join
user3072613
user3072613

Reputation: 363

How does sql handle multiple LEFT JOINS

I don't have a particular problem so I hope my question is valid. I am trying to learn more about the LEFT JOIN.

I know LEFT JOIN will match the columns of the table from the left with the table on the right.

FROM logs LEFT JOIN logins on logins.login_id = logs.user_id
LEFT JOIN city on logs.city_id = cities.city_id
LEFT JOIN factory on logs.factory_id = factory.factory_id
LEFT JOIN storage on logs.storage_id = storage.storage_id

In what particular order does SQL handle this. Please provide an overview of what is going on.

Thanks.

Just to add: I am confident about what will happen when there is only one LEFT JOIN involved but not so sure what goes on when there is more LEFT JOINs added at the end. I assume in a virtual reality the SQL server will perform the first LEFT JOIN, return the results and then use that result in the next LEFT JOIN and so forth.

Upvotes: 2

Views: 1468

Answers (2)

Oliver
Oliver

Reputation: 3255

There are two ways to answer that:

  1. If you think of all tabels and results as "sets of data" and are only concerned with the end results, then yes, you can think of the result of the first join as just another set that is then used to join the next set to it and so on. This is the right way to think about it when you want to determine the correct result set.

  2. Internally, the server has to build an algorithm (aka execution plan) that it really uses to grab the physical data from the tables. The server of course must guarantee that the END result set that you came up with using method 1. doesn't change, but it is free to rearrange the order of lookups and joins as it wishes. In practice, this will happen quite frequently, especially when you consider that some operations might be able to run in parallel on different processors. For example, the server might decide to do a OUTER JOIN instead of a LEFT JOIN at some point, and filter the result properly later or at the end, if it determines that this will produce the result with less effort.

In short: SQL is a language in which you specify WHAT to get, and leaves some freedom about "how to do it" to the database.

Upvotes: 0

cosmos
cosmos

Reputation: 2303

DB Engines maintain the join order from your query.

Saying that

 1)A left join B left Join C is not same as 
 2)A left join C left join B

In 1) A is joined to B first and then joined to C
   2) A is joined to C first and then joined to C

Note: The above is true semantically. DB engines may chose any other join order to optimize the query while maintaining the correct results as per semantics.So in some DB engines the Query plan will look confusing to you.

Upvotes: 3

Related Questions