-1

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 times as there are records 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?

Laurel
  • 5,965
  • 14
  • 31
  • 57
nonopolarity
  • 146,324
  • 131
  • 460
  • 740

1 Answers1

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • hm, i thought i meant, when a record on the LEFT table can satisfy the condition, then those get into the resulting table, but when a record in the LEFT table doesn't, then it gets ONE row (with the other table's field being NULL). So it is either / or, not both... – nonopolarity Apr 25 '10 at 12:20
  • @Jian Lin: This statment isn't precisely correct: "when a record on the LEFT table can satisfy the condition then those get into the resulting table". You need a row from the left table and a row from the right table that *together* satisfy the condition. – Mark Byers Apr 25 '10 at 12:27
  • hm... satisfying the condition: the condition almost always has one value form the LEFT table and one from the RIGHT table, no? Even if it is a self-join, they can still be viewed as left and right table... unless you are looking at a join that says salary > 10000 then it doesn't involve a right table's field. – nonopolarity Apr 25 '10 at 12:47
  • @Jian Lin: I don't understand why you say "the condition almost always has one value form the LEFT table and one from the RIGHT table". It is quite common to see more than one field from each table, for example when your foreign key is two columns, or for finding overlapping intervals. – Mark Byers Apr 25 '10 at 13:41