Понимание SQL

Предположим что мы хотим видеть имена и номера всех продавцов которые имеют более одного заказчика. Следующий запрос выполнит это для вас (вывод показывается в Таблица 11.3 ):

SELECT snum, sname

FROM Salespeople main

WHERE 1 <

( SELECT COUNT (*)

FROM Customers

WHERE snum=main.snum );

Обратите внимание что предложение FROM подзапроса в этом примере не использует псевдоним. При отсутствии имени таблицы или префикса псевдонима, SQL может для начала принять, что любое поле выводится из таблицы с именем указанным в предложении FROM текущего запроса. Если поле с этим именем отсутствует( в нашем случае - snum ) в той таблице, SQL будет проверять внешние запросы. Именно поэтому, префикс имени таблицы обычно необходим в соотнесенных подзапросах - для отмены этого предположения. Псевдонимы также часто запрашиваются чтобы давать вам возможность ссылаться к той же самой таблице во внутреннем и внешнем запросе без какой-либо неоднозначности.

SQL Execution Log

SELECT snum sname FROM Salespeople main

WHERE 1 < AND second.odate=10/03/1990;

cnum	cname	city	rating	snum
2001	Hoffman	London	100	1001
2003	Liu	San Jose	200	1002
2008	Cisneros	San Jose	300	1007
2007	Pereira	Rome	100	1004

Таблица 11.3: Нахождение продавцов с многочислеными заказчиками

ИСПОЛЬЗОВАНИЕ СООТНЕСЕННЫХ ПОДЗАПРОСОВ ДЛЯ НАХОЖДЕНИЯ ОШИБОК

Иногда полезно выполнять запросы которые разработаны специально так чтобы находить ошибки. Это всегда возможно при дефектной информации которую можно ввести в вашу базу данных, и, если она введена, бывает трудно ее определить. Следующий запрос не должен производить никакого вывода. Он просматривает таблицу Порядков чтобы видеть совпадают ли поля snum и cnum в каждой строке таблицы Заказчиков и выводит каждую строку где этого совпадения нет. Другими словами, запрос выясняет, тот ли продавец кредитовал каждую продажу (он воспринимает поле cnum, как первичный ключ таблицы Заказчиков, который не будет иметь никаких двойных значений в этой таблице ).

SELECT *

FROM Orders main

WHERE NOT snum=

( SELECT snum

FROM Customers

WHERE cnum=main.cnum );

При использовании механизма справочной целостности (обсужденного в Главе 19 ), вы можете быть гарантированы от некоторых ошибок такого вида. Этот механизм не всегда доступен, хотя его использование желательно во всех случаях, причем поиск ошибки запроса описанный выше, может быть еще полезнее.

СРАВНЕНИЕ ТАБЛИЦЫ С СОБОЙ

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

SELECT *

FROM Orders outer

WHERE amt >

( SELECT AVG amt

FROM Orders inter

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: Выбераются порядки которые >=средней сумме приобретений для их заказчиков.

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

СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING