 |
|||||
go
These “sly” outer joins page 2 |
|||||
|
As a rule equi join is used in examples, i.e. values are equal each other in concatenated rows. This is due to practical using the foreign key joining. Similar examples you can see on the previous page. However a join predicate may be any logical expression. To illustrate this, let's consider the following problem. Find out in Income_o table the incomes each more than each outcome in Outcome_o table. The solution is 
Console
Execute
The above solution uses outer join on the unequality Outcome_o.out >= Income_o.inc which is satisfied for the rows in the Income_o table with income that does not exceed some outcome in Outcome_o table. Moreover, outer join (right in our case) will include the rows from Income_o table for which there are no rows in the Outcome_o table that estimate the predicate as true. These last rows give the solution to the problem. To filter these, use the fact that absent column values from Outcome_o (left) table for those rows contain NULL values. This criterium is in WHERE clause. Obviously this problem could be solved with other ways, for example: max + subquery
Console
Execute
all + subquery
Console
Execute
|
|||||
