If Inner Join can be thought of as Cross Join but filtering out the records satisfying the condition, then Left Outer Join is the above, plus 1 record for the "no match" on the Left?

Question

If an Inner Join can be thought of as a cross join and then getting the records that satisfy the condition, then a LEFT OUTER JOIN can be thought of as that, plus ONE record on the left table that doesn't satisfy the condition.

In other words, it is not a cross join that "goes easy" on the left records (even when the condition is not satisfied), because then the left record can appear many times (as many as how many records there are in the right table).

So the LEFT OUTER JOIN is the Cross JOIN with the records satisfying the condition, plus ONE record from the LEFT TABLE that doesn't satisfy the condition.

Answer 1

I don't think it is correct to say a left outer join is: "the cross join with the records satsifying the condition and one record for the left table that doesn't satisy the condition".

An inner join without a condition is the same as a cross join. An inner join on x is the same as a cross join where x. But prefer the first as it is more explicit and harder to get wrong.

However with an outer join you don't always get the row "that doesn't satisfy the condition". The difference between a left outer join and an inner join is:

• Inner join: If the join condition for a row in the left table fails for every row in the right table, you don't get that row.
• Outer join: If the join condition for a row in the left table fails for every row in the right table, you get the row from the left table with NULLs for the columns in the right table.

You don't get both the rows that match and one row that doesn't - you either get the first situation or the second. Your statement seems to suggest that you can get both.