CODEWITHSUNDEEP
sqlsql-serversql-server-2012left-join
King Decipher
King Decipher

Reputation: 76

SQL Server 2012 - Left Join with Where vs Inner Join

Is a Left Join with a where clause on the right table always the same as doing an inner join?

EX:

SELECT * 
FROM table t
LEFT JOIN table2 t2 on t.id=t2.id
WHERE t2.name='myName'

VS

SELECT * 
FROM table t
INNER JOIN table2 t2 on t.id=t2.id
WHERE t2.name='myName'

Upvotes: 2

Views: 5270

Answers (2)

CodeSmith
CodeSmith

Reputation: 3197

You should know there are only 4 types of join in SQL (AFAIK).

  • INNER (left or right makes no difference here it's the same join)

  • OUTER - LEFT AND RIGHT

  • FULL

NOTE: not counting the WHERE clause variations!

With setting up the WHERE clause when doing OUTER JOINS to explicitly accept only null values, you get the famous 7 joins with Venn diagrams:

7 joins Venn diagrams

Problem is that most DSMS allow you omitting some words from the syntax

Each join should have combination of entire set of the following (1 from each row):

LEFT or RIGHT (left is default)

OUTER or INNER (outer is default)

JOIN

So when you write LEFT JOIN you should at least in head write full syntax: LEFT OUTER JOIN When you write JOIN full syntax is: LEFT INNER JOIN

The 2 are now visibly not the same.

Difference between inner and outer joins is the null values in the columns you are doing join ON

OUTER join has ALL the records from the primary table (1st table if left, 2nd if right). Since there is no match in secondary table there are no values for that columns and their values will be null but those rows will appear in the result.

INNER join has ONLY those values from primary table that it successfully managed to join to a secondary table. If there is null in a column you are doing a join on that row will not be joined and therefore omitted in the result.

QUICK EXAMPLE

table A

1 bla 2

2 bla null

3 bla 3

table B

1 bla 3

2 bla null

3 bla 3

LEFT OUTER JOIN:

1 bla 2 2 bla null

2 bla null null null null

3 bla 3 3 bla 3

JEFT INNER JOIN

1 bla 2 2 bla null

3 bla 3 3 bla 3

YOUR EXAMPLE SPECIFIC NOTE: Since I assume you are joining by the primary key of both tables this will never happen (null values) since primary keys have the constraint of not null built in their definition and you will never see this difference. Additionally, you require the value from a column in 2nd row to be something (not null)

Upvotes: 2

Greg Viers
Greg Viers

Reputation: 3523

Yes you will always get the same result from these two queries. The only time you would get a different result from a LEFT join is if the t2 values returned were all NULL due to no matching id. Your WHERE statement eliminates all those situations.

Upvotes: 2

Related Questions