Русский    English

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

  1. SELECT Income_o.* FROM Outcome_o RIGHT JOIN Income_o ON Outcome_o.out >= Income_o.inc
  2. WHERE 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

  1. SELECT Income_o.* FROM Income_o
  2. WHERE Income_o.inc > (SELECT MAX(Outcome_o.out) FROM Outcome_o);

all + subquery

  1. SELECT Income_o.* FROM Income_o
  2. WHERE Income_o.inc > ALL(SELECT Outcome_o.out FROM Outcome_o);

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100