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?

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

Solution

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:

Comments

[John Odom]

Have you tried using INNER JOIN instead of LEFT INNER JOIN?

[lisovaccaro]

yes, and it works. However it's not what I want to do (I need all rows from table A not just the ones that overlap)

[John Odom]

Okay, I guess I don't understand what the problem is then. I just know that the error is complaining about the syntax because you can't use LEFT INNER JOIN in SQL server. The list of possible joins can be found at W3Schools SQL Joins.

[lisovaccaro]

I know I have to use a LEFT JOIN in SQL Server. But when I save my LEFT JOIN SQL Server changes it to LEFT OUTER JOIN.

[John Odom]

That's because LEFT JOIN is the same as LEFT OUTER JOIN in SQL server silly :P. The answer on this StackOverflow question provides an excellent explanation about it :).

[lisovaccaro]

the problem is that when trying to add an index to the view I get the error I used an outer join. The whole point of the View is to do JOINS so I'm struggling to understand how to add the index. .

[paparazzo]

If the view requires an outer join then it cannot be index. Calling it a left inner join is not going to fix that.

[lisovaccaro]

hi @Blam could you explain me why it cannot be indexed? I've been told the DB is having a poor performance and I thought I would be able to index it to improve it.

[paparazzo]

Ask Microsoft. That message is very clear.

[LearnByReading]

Your answer saved me; even though I've been a SQL programmer for a few years, for some reason I had this belief that there exists a "LEFT INNER JOIN". No idea why i was thinking that