Left Join Turns into Inner Join

left join turns into inner join

It's because of your WHERE clause.

Whenever you specify a value from the right side of a left join in a WHERE clause (which is NOT NULL), you necessarily eliminate all of the NULL values and it essentially becomes an INNER JOIN.

If you write, AND (c.foobar = 'somethingelse' OR c.foobar IS NULL) that will solve the problem.

You can also move the c.foobar portion into your join predicate, and that too will solve the issue.

Why left join turns into inner join if inner join is included in the query?

This is a common behaviour in database implementations, due to the implications of join nesting. A series of left joins followed by an inner join (or a CROSS APPLY instead of an OUTER APPLY) will have this outcome.

To avoid this, you've already hit on the solution:

select * from atbl
left join 
    (select btbl.id, btbl.atblid 
        from btbl
        inner join ctbl on ctbl.btblid=btbl.id) a
        on atbl.id=a.atblid

This is a non-correlated subquery, as you've not referenced ATBL inside the brackets - meaning the engine can select a reasonably good join strategy for it, or compute the entire subquery once rather than row-by-row.

Another option is to change all the table joins to left joins:

select * from atbl
    left join btbl on btbl.atblid=atbl.id
        left join ctbl on ctbl.btblid=btbl.id
WHERE
     -- Rows where the first LEFT is not satisfied, or BOTH are satisfied.
     (btbl.atblid IS NULL OR ctbl.btblid IS NOT NULL)

You can then use the WHERE clause to filter where either neither of the joins from B onward were hit (i.e. either I didn't find a B or I found both a a B and a C).

SQL Left Outer Join acting like Inner Join

Move your WHERE condition to the ON condition:

Select  C.*
      , D.FREEZE_EVENT
      , D.ACADEMIC_PERIOD
      , D.CAMPUS
      , D.COLLEGE
      , D.COLLEGE_DESC
      , D.MAJOR
      , D.MAJOR_DESC
      , D.STUDENT_RATE
From    (Select A.STUDENT_LEVEL_DESC
              , A.CAMPUS
              , A.CAMPUS_DESC
              , A.COLLEGE
              , A.COLLEGE_DESC
              , A.MAJOR_DESC
              , A.MAJOR
              , A.DEGREE_DESC
              , A.PERSON_UID
              , A.ID
              , A.NAME
              , A.OUTCOME_GRADUATION_DATE
              , A.STATUS
              , A.GRAD_YEAR
              , A.TRAINING_LOCATION
              , B.CITIZENSHIP_TYPE
         From   ACAD_OUTOCME A
         Join   PERSON_DETAIL B  On   A.ID = B.ID
         Where  A.STUDENT_LEVEL In ('02', '03')
                And A.GRAD_YEAR = '2015'
                And A.FREEZE_EVENT = '10TH_SEP2016'
                And B.FREEZE_EVENT = '10TH_SEP2016'
        ) C
Left Outer Join ACAD_STUDY D
        On C.CAMPUS = D.CAMPUS
           And C.COLLEGE = D.COLLEGE
           And C.MAJOR = D.MAJOR
           And C.PERSON_UID = D.PERSON_UID
           And D.FREEZE_EVENT = '10TH_SEP2016'
Order By C.NAME;

The WHERE clause is evaluated after the OUTER JOIN, which would cause it to filter out the NULL records from the LEFT JOIN. So, having the right-hand table of a LEFT JOIN in the WHERE clause will effectively transform the OUTER JOIN into an INNER JOIN.

Inner join and left join gives same results with where filter and hence which join to use?

This happens when you apply the filter (where clause condition) on the table you're left joining on. In this case the 'Order' table.

It is because your WHERE clause explicitly filters rows from the Order table where the color is pink. It will then join on only the matching Order rows on the Customer table.

You'll see that when you remove the where clause, the left join will function as you expect. :)

SQL INNER JOIN vs LEFT JOIN with a WHERE

Yes, they will return the same result. The left join without the where clause would read as show me all the records from the header table and the related items from the details table or null for the details where there are no matches.

Adding a where clause relating the ids effectively transforms the left join to an inner join by eliminating the non-matching rows that would have shown up as having null for the detail part.

In some databases, like MS SQL Server, the left join would show up as an inner join in the query execution plan.

Although you stated that you don't want Venn diagrams I can't help referring you to this question and its answers even though they are filled with (in my opinion very helpful) Venn diagrams.

SQL: JOIN vs LEFT OUTER JOIN?

As previously noted above:

JOIN is synonym of INNER JOIN. It's definitively different from all
types of OUTER JOIN

So the question is "When should I use an outer join?"

The short answer your your question is:

• Prefer JOIN (aka "INNER JOIN") to link two related tables. In practice, you'll use INNER JOIN most of the time.
• INNER JOIN is the intersection of the two tables. It's represented by the "green" section in the middle of the Venn diagram above.
• Use an "Outer Join" when you want the left, right or both outer regions.
• In your example, the result set happens to be the same: the two expressions happen to be equivalent.
• ALSO: be sure to familiarize yourself with "Show Plan" (or equivalent) for your RDBMS: https://www.sqlshack.com/execution-plans-in-sql-server/

'Hope that helps...

---

Related Topics