Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL inner vs left - precedence

Posted on 2013-12-20
1
Medium Priority
?
899 Views
Last Modified: 2013-12-20
Hi,
 In MS SQL 2008, I am looking at a sample code like below:

Select *
from table1  t1 inner join table2 t2 on t1.id = t2.id

inner join table3 t3 on t1.id = t3.id

left join table4 t4 on t1.id = t4.id


I wanted to understand which join takes precedence:
1. Do all the three inner join execute first and then the resultset is left joined to the last table? In this case, the precedence is by virtue of which join is written first?

2. Inner joins take precedence over left join , regardless of in which order they are submitted?

3. Left join takes precedence over the inner join?

thx
0
Comment
Question by:LuckyLucks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 2000 total points
ID: 39732593
Join order evaluates left to right.  The result of the previous join is the left input to the next join.  Where this can introduce unintended consquences is when you have an outer join before an inner join and then you join to the table that was outer joined.  If there are null values that are preserved from the outer join and those values are used to join another table via an inner join, they will be filtered out.  You can get around this by parenthetically grouping your joins together to force the inner join to be evaluated before the outer join.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question