Logo    
Деловая газета CitCity.ru CITKIT.ru - все об Open Source Форумы Все публикации Учебный центр Курилка
CitForum    CITForum на CD    Подписка на новости портала Море(!) аналитической информации! :: CITFORUM.RU
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

29.04.2017

Google
WWW CITForum.ru
С Новым годом!

Уважаемые читатели!

Статья, краткий пересказ которой предлагается вашему вниманию, не содержатся какие-либо открытия. Мне показалось полезным познакомить вас с ней только потому, что на особенности условия NOT EXISTS пользователи языка SQL редко обращают внимание. Конечно, потребность в формулировке запросов, имитирующих операцию реляционного деления, на практике возникает не слишком часто. Тем не менее, нужно быть готовым к этому. Заметка Шерила Ларсена содержит несколько хороших примеров с подробными разъяснениями. Надеюсь, что вам это пригодится.

С уважением, Сергей Кузнецов


The SQL Double Double

DB2 Magazine Online, Spring 1998
Оригинал статьи можно найти по адресу www.db2mag.com/98spLars.htm

Sheryl Larsen, всемирно известный исследователь, консультант и лектор, имеющий богатый практический опыт использования языка SQL

В условиях выборки языка SQL помимо прочего можно использовать подзапросы, или вложенные запросы. Имеется много типов подзапросов, но их основное назначение состоит в возможности подразделения наборов данных - выполнении процесса, называемого "реляционным делением". Для выполнения реляционного деления можно применять и соединения, но при определенных обстоятельствах подзапросы представляют более мощную альтернативу.

Проще всего понять подзапросы без корреляции. Они выполняются снизу вверх по одному разу на каждом уровне. Вот пример подзапроса без корреляции с использованием NOT EXISTS:


	SELECT A.COL1, A.COL2, A.COL6, A.COL7

	FROM TAB1 A

	WHERE NOT EXISTS

	  (SELECT 1

	   FROM TAB2 B

	   WHERE B.COL4 = :hv1)

При выполнении оператора проверяется существование значения :hv1 в столбце COL4 таблицы TAB2. Если такое значение не входит в состав значений COL4, SELECT верхнего уровня возвращает в результирующую таблицу значения столбцов COL1, COL2, COL6 и COL7 из всех строк таблицы TAB1. Если по меньшей мере одно значение :hv1 находится в COL4, SELECT верхнего уровня не выполняется и результатом является пустое множество строк или SQLCODE = +100. Первым выполняется нижний SELECT, возвращающий ответ true, если удается найти хотя бы одну строку, которая удовлетворяет условию B.COL4 = :hv1, и false, если ни одной такой строки найти не удается. Верхний запрос выполняется только в том случае, когда результат нижнего запроса есть false (поскольку используется NOT EXISTS).

Достаточно часто используются одиночные подзапросы с корреляцией. В подзапросе присутствует корреляция, если в нижнем SELECT имеется ссылка на столбец верхнего SELECT (одноуровневое распространение). Такие подзапросы легко распознать, если в разделах FROM применяются псевдонимы, и эти псевдонимы предшествуют любому имени столбца в разделе WHERE. Одиночные подзапросы с корреляцией выполняются в манере сверху-вниз-наверх по одному разу для каждой строки верхнего SELECT. Вот пример запроса с одиночным вложенным запросом и использованием псевдонимов A и B:


	SELECT A.COL1, A.COL2, A.COL6, A.COL7

	FROM TAB1 A

	WHERE A.COL7 = 'X'

	  AND NOT EXISTS

	    (SELECT 1

	     FROM TAB2 B

	     WHERE A.COL2 = B.COL4)

Выполнение этого запроса начинается с обнаружения первой уточненной строки верхнего запроса (WHERE COL7 = 'X'). После этого проверяется существование A.COL2 (с использованием значения этого столбца в первой уточненной строке TAB1) где-либо в стробце COL4 таблицы TAB2. Если значение A.COL2 не входит в состав набора значений COL4, верхний SELECT выбирает значения столбцов COL1, COL2, COL6 и COL7 таблицы TAB1 из первой уточненной строки. Если хотя бы одно значение A.COL2 обнаруживается в COL4, то верхний SELECT продвигается к следующей уточненной строке (WHERE COL7 = 'X'). Процесс продолжается до тех пор, пока верхний запрос не сможет обнаружить следующую уточненную строку. Тем самым, сначала выполняется верхний запрос, подготавливающий список строк, для которых будет проверяться условие существования, по одной строке за раз. Нижний запрос выполняется вторым, возвращая ответ true, если удается найти хотя бы одну строку, и false, если ни одна строка не удовлетворяет условию. Верхний запрос перемещает информацию текущей строки в окончательный результат только в том случае, когда результатом нижнего запроса является false (поскольку используется NOT EXISTS).

Двойные подзапросы с корреляцией нетипичны для разработчиков, использующих SQL. В частности, меньше 10% разработчиков на базе DB2 когда-либо видели такие запросы. Такие запросы выполняются в стиле сверху-вниз-в середину-вниз-в середину-наверх. Первым выполняется верхний запрос, который подготавливает список строк, для которых будет проверяться условие существования, по одной строке за раз. Затем выполняется средний запрос, который тоже подготавливает список строк, для которых будет проверяться условие существования, по одной за раз. Последним выполняется нижний запрос, как обычно, возвращающий true, если удается найти хотя бы одну строку, удовлетворяющую условию, и false, если не удается найти ни одной такой строки. Вот пример запроса с двойной корреляцией, в котором используются псевдонимы имен таблиц BLK1, BLK2 и BLK3:


	SELECT SNAME

	FROM S BLK1

	WHERE NOT EXISTS

	  (SELECT 1

	   FROM SP BLK2

	   WHERE BLK2.S# = 'S2'

	     AND NOT EXISTS

	       (SELECT 1

	        FROM SP BLK3

	        WHERE BLK3.S# = BLK1.S#

	          AND BLK3.P# = BLK2.P#))

Этот запрос выдает список поставщиков, поставляющих все детали, поставляемые поставщиком S2. Всем процессом управляет верхний список поставщиков (BLK1.S#). Один поставщик передается нижнему запросу. Далее выполняется средний запрос, формирующий список деталей, уточненных условием WHERE BLK2.S# = 'S2'. Одна деталь (BLK2.P#) передается от среднего нижнему запросу. Затем выполняется нижний запрос и возвращает true или false среднему запросу. Если результатом нижнего запроса является true, средний запрос передает нижнему другую деталь (BLK2.P#). Этот цикл продолжается до тех пор, пока либо не встретится результат false, либо не исчерпаются все детали. Если нет больше деталей, то это означает, что результат среднего запроса пуст и верхнему запросу передается false. Это является условием пропуска текущей строки верхнего запроса в окончательный результат. В результирующее множество попадет имя поставщика, поставляющего по меньшей мере все те детали, что и поставщик S2.

Если в какой-то момент результатом нижнего запроса является false, то среднему запросу разрешается выполняться, и он возвращает верхнему запросу true. По этому поводу верхний запрос выбирает следующего поставщика (BLK1.S#) и начинает заново весь цикл. Этот процесс позволяет найти всех поставщиков, которые поставляют по меньшей мере все детали, поставляемые поставщиком S2.

Двойная корреляция с двойным условием NOT EXISTS представляет мощный оператор реляционного деления. Рассмотрим следующий запрос:


	SELECT DISTINCT MAJOR             <----- Верхний запрос

	FROM PARTS T1

	WHERE NOT EXISTS

	  (SELECT *                       <----- Средний запрос

	   FROM QUE T2

	   WHERE NOT EXISTS

	     (SELECT *                    <----- Нижний запрос

	      FROM PARTS T3

	      WHERE T1.MAJOR=T3.MAJOR

	        AND T3.MINOR=T2.ID))

Таблицы имеют следующую структуру и содержание:


	Таблица PARTS       Таблица QUE

	10000000 строк        2 строки

	

	MAJOR MINOR              ID

	----- -----              --

	 10     1                 1

	 10     2                 3

	 10     3

	 11     2

	 11     3

	 12     1

	 12     3

	 12     4

В этом запросе выполняется деление всех значений столбца ID таблицы QUE на значения столбца MINOR таблицы PARTS. Запрос возвращает все значения столбца MAJOR, для каждого из которых набор значений столбца MINOR включает по меньшей мере все значения столбца ID таблицы QUE. Запрос вычисляется следующим образом: в верхнем запросе выбирается строка со значением столбца MAJOR, равным 10. Это значение передается в нижний запрос. Выполняется средний запрос, выбирается строка со значением столбца ID, равным 1, и это значение передается в нижний запрос. Поскольку результатом нижнего запроса является true, продолжает работать средний запрос и передает нижнему запросу значение id, равное 3. Нижний запрос опять дает значение true, но у среднего запроса больше нет строк, поэтому он вырабатывает значение true, и 10 помещается в окончательный результат. На следующем шаге нижний запрос получает значение MAJOR, равное 11, и значение ID, равное 3. Поскольку нижний запрос вычисляется в false, средний запрос вычисляется в true, и это не дает возможности поместить 11 в результирующий набор. Аналогичные рассуждения показывают, что 12 войдет в результирующий набор.

Средний запрос может также передавать информацию из нескольких соединенных вместе таблиц, например, список всех элементов почтовых заказов от калифорнийских клиентов. Можно проверить каждого поставщика на предмет того, поставляет ли он по меньшей мере все эти элементы. Следующий запрос позволяет найти всех поставщиков, которые поставляют по меньшей мере все товары, покупаемые калифорнийскими заказчиками:


	SELECT SNAME

	FROM S BLK1

	WHERE NOT EXISTS

	  (SELECT 1

	   FROM IT BLK2, ORDERS O

	   WHERE O.STATE = 'CA'

	     AND O.ITEM = BLK2.ITEM

	     AND NOT EXISTS

	       (SELECT 1

	        FROM SI BLK3

	        WHERE BLK3.ITEM = BLK2.ITEM

	          AND BLK3.S# = BLK1.S#))

Эффективность выполнения запросов с двойной корреляцией и двойным NOT EXISTS зависит от возможности использования индексов, по крайней мере, для среднего и нижнего запросов. Если в верхнем запросе проверяется каждая строка таблицы, то наиболее подходит последовательный просмотр таблицы. Наличие индексов позволяет запросу передавать значения из списка без потребности предварительной полной материализации списка.

Конечно, чем больше значений true возвращает нижний запрос, тем медленнее выполняется весь запрос целиком. Поэтому, если вероятна выработка более 25% значений true, более эффективно использовать следующий синтаксис:


	SELECT P.MAJOR

	FROM PARTS P, QUE Q

	WHERE P.MINOR = Q.ID

	GROUP BY P.MAJOR

	HAVING COUNT(*) =

	  (SELECT COUNT(*)

	   FROM QUE)

Этот запрос выбирает все значения столбца MAJOR, для каждого из которых множество значений столбца MINOR совпадает с множеством значений столбца ID таблицы QUE. При наличии более 25% значений столбца MAJOR, удовлетворяющих условию запроса этот запрос будет выполняться более эффективно. Если вероятность нахождения "всего ____ что имеет _____ условие(я)" мала, то SQL Double Double является эффективным оператором реляционного деления.

Подзапросы занимают небольшое, но ответственное место в наборе средств разработчика, использующего язык SQL. С помощью подзапросов можно выполнять сравнения данных, которые невозможны при использовании соединений, такие как сравнение детальных и суммарных данных. Кроме того, это механизм позволяет эффективно выполнять проверку существования и реляционное деление.

Размещение рекламы — тел. +7 495 4119920, ICQ 232284597

Подписка на новости IT-портала CITForum.ru
(библиотека, CITKIT.ru, CitCity)

Новые публикации:

24 декабря

CITKIT.ru:

  • Новогодние поздравления
  • Сергей Кузнецов. Цикл Операционные системы: Ностальгия по будущему:

  • Алексей Федорчук. OpenSolaris 2008.11 Release

  • Сергей Голубев:

  • Евгений Чайкин aka StraNNik (Блогометки):

    17 декабря

  • С.Д.Кузнецов. Базы данных. Вводный курс

    10 декабря

    CITKIT.ru:

  • OpenSolaris 2008.11 Release

  • Альтернативные ОС: две грустные истории (С.Кузнецов)
  • Nokia N810 — доведение до ума
  • CitCity:

  • Платформа 2009: заоблачные перспективы Microsoft

    4 декабря

  • Лекция С.Д.Кузнецова Понятие модели данных. Обзор разновидностей моделей данных

    CITKIT.ru:

  • OpenSolaris 2008.11 Release. Первые впечатления

  • Linux vs FreeBSD: продолжим "Священные войны"?

  • Nokia N810 as is

  • Индульгенция для FOSS

  • Друзья СПО'2008

    26 ноября

  • Нечеткое сравнение коллекций: семантический и алгоритмический аспекты

    CitCity:

    CITKIT.ru:

  • Глава из книги А.Федорчука
    Сага о FreeBSD:
  • 19 ноября

  • Проблемы экономики производства крупных программных продуктов

  • Язык модификации данных формата XML функциональными методами

    CITKIT.ru:

  • Главы из книги А.Федорчука
    Сага о FreeBSD:

    Заметки к книге:

  • FreeBSD: монтирование сменных устройств и механизм HAL
  • Текстовый редактор ee

    12 ноября

  • Правило пяти минут двадцать лет спустя, и как флэш-память изменяет правила (Гоц Грейф, перевод: Сергей Кузнецов)

    CITKIT.ru:

  • Главы из книги А.Федорчука
    Сага о FreeBSD:
  • OSS в России: взгляд правоведа (В.Житомирский)

  • Новая статья из цикла С.Голубева "Железный марш":

    29 октября

  • О некоторых задачах обратной инженерии

  • Веб-сервисы и Ruby

  • Тестирование web-приложений с помощью Ruby

    CITKIT.ru:

  • Главы из книги А.Федорчука
    Сага о FreeBSD:

  • PuppyRus Linux - беседа с разработчиком (С.Голубев)

  • Сергей Кузнецов. Заметка не про Linux

    22 октября

  • Обзор методов описания встраиваемой аппаратуры и построения инструментария кросс-разработки

    CITKIT.ru:

  • Сергей Кузнецов. Почему я равнодушен к Linux

  • Глава из книги А.Федорчука
    Сага о FreeBSD:
  • Что надо иметь
    3. Базовые познания

    CitCity:

  • Управление IT-инфраструктурой на основе продуктов Microsoft

    15 октября

  • Методы бикластеризации для анализа интернет-данных

    CitCity:

  • Разъемы на ноутбуках: что они дают и зачем их так много?
  • AMD Puma и Intel Centrino 2: кто лучше?

    CITKIT.ru:

  • Новый цикл статей С.Голубева
    Железный марш:

  • Главы из книги А.Федорчука
    Сага о FreeBSD:

    8 октября

  • Автоматизация тестирования web-приложений, основанных на скриптовых языках
  • Опыт применения технологии Azov для тестирования библиотеки Qt3

    Обзоры журнала Computer:

  • SOA с гарантией качества
  • Пикоджоуль ватт бережет
  • ICT и всемирное развитие

    CitCity:

  • Пиррова победа корпорации Microsoft

    CITKIT.ru:

  • Главы из книги А.Федорчука
    Сага о FreeBSD:

    Статья из архива:

  • Я живу в FreeBSD (Вадим Колонцов)

    Новые Блогометки:

  • Перекройка шаблона Blogger или N шагов к настоящему
  • Blogger. Comment style
  • Screenie или глянцевый снимок экрана

    2 октября

    CITKIT.ru:

  • Сага о FreeBSD (А. Федорчук)

    Zenwalk: пакет недели

  • Банинг — интеллектуальное развлечение (С.Голубев)

    CitCity:

    25 сентября

  • Клермонтский отчет об исследованиях в области баз данных

    CITKIT.ru:

  • Пользователям просьба не беспокоиться... (В.Попов)

  • Снова про ZFS: диск хорошо, а два лучше
  • Командная оболочка tcsh (А.Федорчук)

    Zenwalk: пакет недели

    17 сентября

  • T2C: технология автоматизированной разработки тестов базовой функциональности программных интерфейсов
  • Технология Azov автоматизации массового создания тестов работоспособности

    CITKIT.ru:

  • FreeBSD: ZFS vs UFS, и обе-две — против всех (А.Федорчук)

    Zenwalk: пакет недели

  • Дачнет — практика без теории (С.Голубев)

    10 сентября

  • За чем следить и чем управлять при работе приложений с Oracle
  • Планировщик заданий в Oracle
    (В.Пржиялковский)

    CITKIT.ru:

  • Microsoft: ответный "боян" (С.Голубев)

  • Причуды симбиоза, или снова "сделай сам" (В.Попов)

  • Файловые системы современного Linux'а: последнее тестирование
  • Zsh. Введение и обзор возможностей
    (А.Федорчук)

    Описания пакетов Zenwalk: Zsh, Thunar, Thunar-bulk-rename, Xfce4-places-plugin, Xfce4-fsguard-plugin

    Блогометки:

  • Google Chrome
  • Лончер для ASUS Eee PC 701

    3 сентября

    CITKIT.ru:

  • Заметки о ядре (А.Федорчук):

    Добавлены описания пакетов Zenwalk: Galculator, Screenshot, Gnumeric, Pidgin

    В дискуссинном клубе:

  • И еще о Википедии и Google Knol

  • Лекция для начинающего линуксоида (С.Голубев)

    26 августа

  • Транзакционная память (Пересказ: С. Кузнецов)

    CITKIT.ru:

  • Открыт новый проект Zenwalk: пакет недели

  • Статья Текстовые процессоры и их быстродействие: конец еще одной легенды?

    21 августа

    CITKIT.ru:

  • Почему школам следует использовать только свободные программы (Ричард Столлман)
  • Беседа Сергея Голубева с учителем В.В.Михайловым

  • Википедия или Гуглезнание? Приглашение к обсуждению (Алексей Федорчук)
  • Народная энциклопедия от Google (StraNNik)

  • Обзор Mandriva 2009.0 Beta 1 Thornicrofti
  • Новичок в Линукс: Оптимизируем Mandriva 2008.1

  • Книга Zenwalk. Приобщение к Linux:

    13 августа

    CitCity:

  • Мирный Atom на службе человеку. Обзор платы Intel D945GCLF с интегрированным процессором
  • Обзор процессоров Intel Atom 230 на ядре Diamondville

  • iPhone - год спустя. Скоро и в России?

    CITKIT.ru:

  • Интермедия 3.4. GRUB: установка и настройка (из книги Zenwalk. Приобщение к Linux)

    6 августа

  • СУБД с хранением данных по столбцами и по строкам: насколько они отличаются в действительности? (Пересказ: С. Кузнецов)

    CITKIT.ru:

  • Интермедия 2.2. Что неплохо знать для начала (из книги Zenwalk. Приобщение к Linux)

  • И снова про шрифты в Иксах (А.Федорчук)

  • 20 самых быстрых и простых оконных менеджеров для Linux

  • Дело о трех миллиардах (С.Голубев)

    30 июля

  • OLTP в Зазеркалье (Пересказ: С. Кузнецов)

    CitCity:

  • Будущее BI в облаках?
  • Тиражные приложения и заказная разработка. Преимущества для заказчика
  • Дискуссия со сторонниками заказной разработки

    CITKIT.ru:

  • Новые главы книги Zenwalk. Приобщение к Linux:
  • Глава 8. Пакеты: средства установки, системы управления, системы построения
  • Глава 9. Zenwalk: репозитории, пакеты, методы установки

    23 июля

    CITKIT.ru:

  • Все против всех. 64 vs 32, Intel vs AMD, tmpfs vs ext3
  • Две головы от Intel

  • Zenwalk: обзор штатных приложений (глава из книги "Zenwalk. Приобщение к Linux")

  • Нормально, Григорий...

    16 июля

    Обзоры журнала Computer:

  • Перспективы и проблемы программной инженерии в XXI веке
  • Большие хлопоты с большими объемами данных
  • Перспективы наноэлектроники

    CITKIT.ru:

  • Интермедия о лицензиях (А.Федорчук. "Zenwalk. Приобщение к Linux")

  • Есть ли будущее у KDE?

  • Linux в школе: альтернативный вариант в задачах

  • Шифр (приключения агента Никодима)

    10 июля

    CITKIT.ru:

  • Новые разделы книги А. Федорчука Zenwalk. Приобщение к Linux:
  • Интермедия вступительная. Linux или GNU/Linux? Как вас теперь называть?
  • Глава 5. Среда Xfce
  • Глава 6. Xfce: приложения и плагины

  • ZUR (Zenwalk User Repository) FAQ

    2 июля

  • Персистентность данных в объектно-ориентированных приложениях (С. Кузнецов)

    CITKIT.ru:

  • Новые разделы книги А. Федорчука Zenwalk. Приобщение к Linux:
  • Интермедия 1.2. Дорога к Zenwalk'у. Период бури и натиска
  • Интермедия 3.3. Немного о Linux'е и "железе"
  • Глава 4. Настройка: инструментами и руками
  • Интермедия 4.1. Zenpanel и конфиги: поиски корреляции

  • Интервью с Жан-Филиппом Гийоменом, создателем дистрибутива Zenwalk

  • Linux в школе: первые итоги (С. Голубев)

    25 июня

    CITKIT.ru:

  • Zenwalk. Приобщение к Linux (А. Федорчук)

  • Логика и риторика (С.Голубев)

  • Технология Tru64 AdvFS

  • Ханс Райзер предлагает отвести полицейских к телу Нины

    18 июня

  • Проекты по управлению данными в Google (Пересказ: С. Кузнецов)

    CITKIT.ru:

  • ОС и поддержка "железа": мифы и реальность (А. Федорчук)

  • Linux в школе: другие дистрибутивы

  • Пинок (С. Голубев)

    4 июня

  • Ландшафт области управления данными: аналитический обзор (С. Кузнецов)

    CITKIT.ru:

  • Linux в школе: слово заинтересованным лицам

  • SlackBuild: пакеты своими руками

  • Linux от компании Novell. Установка и обзор openSUSE Linux

    Все публикации >>>




  • IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

    Информация для рекламодателей PR-акции, размещение рекламы — тел. +7 495 4119920, ICQ 232284597 Пресс-релизы — pr@citcity.ru
    Послать комментарий
    Информация для авторов
    Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
    Copyright © 1997-2000 CIT, © 2001-2007 CIT Forum
    Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...