CODEWITHSUNDEEP
sql-servert-sqlview
lisovaccaro
lisovaccaro

Reputation: 33956

SQL Server replaces LEFT JOIN for LEFT OUTER JOIN in view query

I need to use a LEFT JOIN in a View however SQL Server replaces LEFT JOIN for LEFT OUTER JOIN every time I save my view.

When trying to use LEFT INNER JOIN explicitly I get the error "Incorrect syntax near word 'INNER'". What is more when I want to create an index for the view I get the error "Cannot add clustered index to views using OUTER JOINS".

It's maddening, and ideas why this could be happening?

enter image description here

So when I try to create an index for the view I get the message I used an outer join though I didn't.

enter image description here

Upvotes: 2

Views: 29888

Answers (1)

Stephan
Stephan

Reputation: 6018

You are getting the joins confused and keep in mind there are different ways of writing joins. What you're looking for is a LEFT OUTER JOIN(OUTER is an optional). There is no LEFT INNER JOIN.

There are three major types of joins.

Type 1: INNER JOIN - only where both tables match

1.) INNER JOIN aka JOIN

Type 2: OUTER JOINS where either one or both tables match

1.) LEFT OUTER JOIN aka LEFT JOIN

2.) RIGHT OUTER JOIN aka RIGHT JOIN

3.) FULL OUTER JOIN aka FULL JOIN

Type 3: CROSS JOIN - Cartesian product(all possible combos of each table)

1.) Cross Join

Here's a graphic showing how each works:

enter image description here

Upvotes: 16

Related Questions