Modifying or Increase efficiency the Following 'RIGHT OUTER JOIN' query into inner join?

Question

I'm trying to increase the efficiency in some of my queries. Due to the limitation of my knowledge I have always been using Right outer join to bring in a full dataset from multiple tables then culling them down through the where statement. However as I would like to add 'clustered index' to the follow query that is feeding one of my Schema binding view, I'm just wondering if there is a way to turn the following Right outer joins into 'inner join' instead so that the index can be created to decrease load time.

As I've been banging my head on this block for the last 2-3 days, any suggestions will be greatly appreciated!!

Table1 has Client information
Table 2 is a Junction table consisting composite key from Table1 and Table3
Table3 has Account information

FROM dbo.Table1 
RIGHT OUTER JOIN dbo.Table2 
    ON dbo.Table1.[Client ID] = dbo.Table2.[Client ID] 
LEFT OUTER JOIN dbo.Table4 
    ON dbo.Table2.[Account Officer] = dbo.Table4.FullName 
RIGHT OUTER JOIN dbo.Table3 
    ON dbo.Table2.[Account ID] = dbo.Table3.[Account ID]
WHERE (dbo.Table2.[Client Type] LIKE '%Customer 1%') 
AND (dbo.Table3.ServiceDescription NOT LIKE '%Nil%') 
AND (dbo.Table2.[Indicative outcome] IS NULL) 
AND (dbo.Table2.[Finalised outcome] IS NULL) 
AND (dbo.Table2.Outcome IS NULL) 
AND (dbo.Table2.ReviewOutcome IS NULL) 
AND (dbo.Table3.[Account Type] = 15) 
AND (dbo.Table3.AccountReviewComplete = 1)

I have tried to rearrange it so that some of the where criteria goes into the join as I've seen on some of the articles I found (which would apparently limit the data size at the start), which is said to increase efficiency but after reviewing the 'Live Query Statistics' for before and after, there doesn't seem to be that much of a difference. Example below:

FROM dbo.Table1 
RIGHT OUTER JOIN dbo.Table2 
    ON dbo.Table1.[Client ID] = dbo.Table2.[Client ID] 
LEFT OUTER JOIN dbo.Table4 
    ON dbo.Table2.[Account Officer] = dbo.Table4.FullName 
RIGHT OUTER JOIN dbo.Table3 
    ON dbo.Table2.[Account ID] = dbo.Table3.[Account ID] 
    AND (dbo.Table3.[Account Type] = 15) 
    AND (dbo.Table3.AccountReviewComplete = 1)

WHERE (dbo.Table2.[Client Type] LIKE '%Customer 1%') 
AND (dbo.Table3.ServiceDescription NOT LIKE '%Nil%') 
AND (dbo.Table2.[Indicative outcome] IS NULL) 
AND (dbo.Table2.[Finalised outcome] IS NULL) 
AND (dbo.Table2.Outcome IS NULL) 
AND (dbo.Table2.ReviewOutcome IS NULL)

The mixture of right join and left join makes these queries very hard to understand. In fact, some database experts prefer to [avoid right joins](https://stackoverflow.com/a/34192902/3094533) altogether (and I agree with this as well). — Zohar Peled, Nov 08 '18 at 08:03
Table4 can be left out of this picture totally.. and personally I would rather only use inner joins.. but can't seem to obtain the dataset I want with only inner joins currently — SeanY, Nov 08 '18 at 08:06
left joins and right joins are mirror images of each other. an inner join is a different kind of join - because it will only return rows from both tables when the join criteria match - while a left join will return all rows from the left table and only rows from the right table when the join criteria match - so you can't simply replace an outer join with an inner join and expect the same results. — Zohar Peled, Nov 08 '18 at 08:09
It would help to answer the question if you posted sample data as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language) and your desired results. [Here is a good manual on how to post sql related questions.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Nov 08 '18 at 08:12
You need to do more analysis on "can't seem to obtain the dataset I want". _Can't seem_ means you haven't finished understanding what your issue is. Start with one table. Add other tables as _inner joins_ and observe and understand the issue. Forget about rearranging things between where and on for performance. If the result is logically the same, the database usually does it the same way no matter how you write it. — Nick.Mc, Nov 08 '18 at 08:19
Your query just makes no sense. You are checking `NULL` values on the driver table for the `RIGHT JOIN`. I would suggest you just forget that `RIGHT JOIN` exists and only use `LEFT JOIN`. — Gordon Linoff, Nov 08 '18 at 12:37

score 0 · Accepted Answer · answered Nov 08 '18 at 08:32

You do not provide enough information, but I think this might help. Try it out:

SELECT * 
FROM dbo.Table1 cd --ClientData
INNER JOIN dbo.Table2 jd --Junction Data
    ON cd.[Client ID] = jd.[Client ID] 
INNER JOIN dbo.Table3 ai --AccountInformation
    ON jd.[Account ID] = ai.[Account ID]
LEFT JOIN dbo.Table4 ao --AccountOfficer 
    ON jd.[Account Officer] = ao.FullName 
WHERE (jd.[Client Type] LIKE '%Customer 1%') 
AND (ai.ServiceDescription NOT LIKE '%Nil%') 
AND (jd.[Indicative outcome] IS NULL) 
AND (jd.[Finalised outcome] IS NULL) 
AND (jd.Outcome IS NULL) 
AND (jd.ReviewOutcome IS NULL) 
AND (ai.[Account Type] = 15) 
AND (ai.AccountReviewComplete = 1)

Some hints:

Use table aliases. This makes it much easier to read and understand
the inner join will return only rows with matching linked rows.
I used a LEFT JOIN for the officer, because the linkage on its name might be erronous (typos!) and you could miss rows otherwise.
The usage of LIKE with a leading % will not allow for indexes and make your query slow. If there is any chance you should replace Client Type and ServiceDescription with data in determinated sets (use catalog-tables if possible)

Hi Shnugo! your joins work perfectly but the LEFT join from Junction table (jd) to Account Officer table (ao) on the server auto corrects itself to a LEFT OUTER JOIN soon as I save the query. I think its because of the way our data is set up. However this is a lot closer to the result that I am after than my trial and error for over 5hrs.. Really appreciate your input! :) — SeanY, Nov 09 '18 at 01:04
@SeanY Gald to help you. [This answer](https://stackoverflow.com/a/406333/5089204) is a great read about types of joins. You will find, that `LEFT JOIN` and `LEFT OUTER JOIN` is equivalent. If this solves your issue, it would be nice to accept the answer (check below the vote counter). This will mark this question as solved and will pay points to your and to my account. Once you've crossed the 15 points yourself, you are additionally asked to vote on helpful contributions. That's the SO-way to say *thx*. Happy Coding! — Shnugo, Nov 09 '18 at 07:52

Modifying or Increase efficiency the Following 'RIGHT OUTER JOIN' query into inner join?

1 Answers1