Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

left join turns into inner join

Tags:

sql

People also ask

Why LEFT join become inner join?

You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.

How do you convert left join into inner join?

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.

Is it possible for left join and inner join to produce the same results?

The reason why LEFT JOIN and INNER JOIN results are the same is because all the records of table branch has at least one match on table user_mast . The main difference between INNER JOIN and LEFT JOIN is that LEFT JOIN still displays the records on the the LEFT side even if they have no match on the RIGHT side table.

IS LEFT join inner?

Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.


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.


The reason you're seeing this is because the left join sets all columns of c to NULL for those rows that don't exist in c (i.e. that can't be joined). This implies that the comparison c.foobar = 'somethingelse' is not true, which is why those rows are not being returned.

In the case where you move the c.foobar = 'somethingelse' into the join condition, that join is still returning those rows (albeit with NULL values) when the condition is not true.


The 'where' clause is performed after the join. This doesn't matter for inner joins but matters for outer joins.

Shorten Example

SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk WHERE c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    1    something      <not in result set>
1    1    somethingelse   1    1    somethingelse  somethingelse


SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk AND c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    null null           null
1    1    somethingelse   1    1    somethingelse  somethingelse