Сравнение таблицы с собой

Вы можете также использовать соотнесенный подзапрос, основанный на той же самой таблице, что и основной запрос. Это даст вам возможность извлечь определенные сложные формы произведенной информации. Например, мы можем найти все Заказы со значениями сумм приобретений выше среднего для их заказчиков (вывод показан в Рисунке 11.4):

SELECT *
FROM Orders outer
WHERE amt > (SELECT AVG (amt)
FROM Orders inner
WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3010 1309.00 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================

Рисунок 11.4. Соотнесение таблицы с собой.

Конечно, в нашей маленькой типовой таблице, где большинство заказчиков имеют только один заказ, большинство значений являются одновременно средними и, следовательно, не выбираются. Давайте введем команду другим способом (вывод показывается в Рисунке 11.5):

SELECT *
FROM Orders outer
WHERE amt >= (SELECT AVG (amt)
FROM Orders inner
WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > = |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3003 767.19 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================

Рисунок 11.5. Выбираются Заказы, которые >= средней сумме приобретений для их заказчиков.

Различие, конечно, в том, что реляционный оператор основного предиката включает значения, которые равняются среднему (что обычно означает, что они — единственные Заказы для данных заказчиков).

Примечание. В примерах запросов, приведенных в этом разделе, используются псевдонимы "inner" и "outer", которые в СУБД Interbase (возможно, и в других СУБД) являются ключевыми словами, и, следовательно, должны быть изменены.