2

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

lisovaccaro
  • 32,502
  • 98
  • 258
  • 410
  • Have you tried using `INNER JOIN` instead of `LEFT INNER JOIN`? – John Odom May 20 '15 at 19:45
  • 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) – lisovaccaro May 20 '15 at 19:50
  • 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](http://www.w3schools.com/sql/sql_join.asp). – John Odom May 20 '15 at 19:52
  • 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`. – lisovaccaro May 20 '15 at 19:54
  • 5
    That's because `LEFT JOIN` is the same as `LEFT OUTER JOIN` in SQL server silly :P. The answer on this [StackOverflow](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) question provides an excellent explanation about it :). – John Odom May 20 '15 at 19:57
  • 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. . – lisovaccaro May 20 '15 at 20:06
  • 1
    If the view requires an outer join then it cannot be index. Calling it a left inner join is not going to fix that. – paparazzo May 20 '15 at 20:29
  • 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. – lisovaccaro May 21 '15 at 00:58
  • Ask Microsoft. That message is very clear. – paparazzo May 21 '15 at 10:00

1 Answers1

16

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

Stephan
  • 5,891
  • 1
  • 16
  • 24
  • 1
    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 – LearnByReading Aug 03 '16 at 03:04