Menu
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With