10:14

# 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
`SELECT Income_o.* FROM Outcome_o RIGHT JOIN Income_o ON Outcome_o.out >= Income_o.incWHERE Outcome_o.out IS NULL;`

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
`SELECT Income_o.* FROM Income_o WHERE Income_o.inc > (SELECT MAX(Outcome_o.out) FROM Outcome_o);`

all + subquery

Console
Execute
`SELECT Income_o.* FROM Income_o WHERE Income_o.inc > ALL(SELECT Outcome_o.out FROM Outcome_o);`

 Pages 1 2
Tags
The book was updated
several days ago