Solved

SQL inner vs left - precedence

Posted on 2013-12-20
1
857 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to replicate a Log table 4 41
Help with SQL pivot 11 49
T-SQL: How to append a column for serialized JSON data? 2 50
T-SQL: Number of Records is Greater Than One 7 51
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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