Solved

SQL inner vs left - precedence

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Delete Query 9 33
This query failed in sql 2014 5 32
Sql server insert 13 32
How To Convert Date Stored as Varchar as an MSSQL DATE Type Field In a VIEW 3 23
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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