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

Question

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?

Answer 1

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)