Очень многие программисты смотрят на SQL, как на страшного зверя. SQL — один из немногих декларативных языков, и его поведение в корне отличается от того, как работают императивные, объектно-ориентированные и даже функциональные языки программирования. Хотя многие рассматривают SQL в некоторой степени функциональным.

И вот…

1. SQL является декларативным

SELECT first_name, last_name 
FROM employees 
WHERE salary > 10000

Все понятно. И вас не волнует, откуда эти записи о сотруднике (employee) берутся. Вам лишь нужны те, у которых достойная зарплата (salary).

Что мы из этого узнаем?

Если все так просто, в чем же проблема? Проблема в том, что большинство из нас интуитивно мыслит рамками императивного программирования. Вроде: «машина, сделай это, затем то, но сначала проверь то и се.» А значит, нужно хранить временные результаты в переменных, составлять циклы, вызывать функции и т. д. и т. п.

Забудьте обо всем этом. Думайте о том, как объявлять вещи. Не о том, как сказать машине, вычислить что-либо.

2. Код SQL не является упорядоченным

Обычно путаница происходит из-за того, что элементы кода SQL не расположены в том порядке, в каком они выполняются. Лексический порядок выглядит так:

• SELECT [ DISTINCT ]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

Для простоты перечислены не все предложения SQL. Этот словесный порядок принципиально отличается от логического порядка (который в свою очередь может отличаться от порядка выполнения в зависимости от выбора оптимизатора):

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

Три пункта, которые следует отметить:

1. Первое предложение здесь FROM, а не SELECT. Сначала происходит загрузка данных с диска в память, чтобы с ними можно было работать.

2. SELECT выполняется после большинства других предложений. Главное, после FROM и GROUP BY. Это важно понимать, если думаете, что можете ссылаться на элементы, которые объявляете в предложении SELECT из предложения WHERE. Следующее невозможно:

SELECT A.x + A.y AS z
FROM A
WHERE z = 10 -- z здесь не доступна!

Есть два варианта повторно использовать z. Либо повторить выражение:

SELECT A.x + A.y AS z
FROM A
WHERE (A.x + A.y) = 10

… либо прибегнуть к производным таблицам, общим табличным выражениям или представлениям данных, чтобы избежать повторения кода. Смотрите примеры ниже.

3. UNION ставится перед ORDER BY в лексическом и логическом порядках. Многие думают, что каждый подзапрос UNION можно упорядочить, но по стандарту SQL и в большинстве диалектов SQL это не так. Хотя в некоторых диалектах позволяется упорядочивание подзапросов или производных таблиц, нет никакой гарантии, что такой порядок будет сохранен после выполнения операции UNION.

Обратите внимание, что не все базы данных реализуют вещи одинаковым образом. Правило номер 2, например, не применяется в точности, как описано выше, для MySQL, PostgreSQL, и SQLite.

Что мы из этого узнаем?

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

Конечно, неплохо, если бы язык был спроектирован таким образом, когда лексическая последовательность фактически отражает логический порядок, как это реализовано в Microsoft LINQ.

3. Вся суть SQL заключается в ссылках на таблицы

Из-за разницы между лексическим упорядочением и логическим упорядочением большинство новичков, вероятно, думают, что значения столбцов являются объектами первого класса в SQL. Но, нет. Наиболее важными являются ссылки на таблицы.

Стандарт SQL определяет предложение FROM следующим образом:

<from clause> ::= 
    FROM <table reference> 
        [ { <comma> <table reference> }... ]

Вывод предложения FROM — это объединенная ссылка на таблицу объединенного значения всех ссылок. Попробуем это переварить.

FROM a, b

Указанная выше запись производит комбинированную ссылку на таблицу значений a и b. Если a имеет 3 колонки и b 5 колонок, тогда выходная таблица “output table” будет состоять из 8 (3 + 5) столбцов.

Записи, содержащиеся в этой комбинированной табличной ссылке являются перекрестным / декартовым произведением a x b. Иными словами, каждая запись a образует пару с записью b. Если a имеет 3 записи, а b 5 записей, описанная выше ссылка на таблицу производит 15 записей (3 x 5).

Этот вывод переводится в предложение GROUP BY (после фильтрации в предложении WHERE), где преобразуется в новый вывод. Разберемся с этим позже.

Если мы посмотрим на эти примеры в свете реляционной алгебры / теории множества, то таблица SQL — это отношение или набор кортежей. Каждое предложение SQL преобразует одно или несколько отношений, чтобы создать новые отношения.

Что мы из этого узнаем?

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

4. Ссылки на таблицы SQL могут быть довольно функциональными

Ссылка на таблицу — мощная штука. Примером их силы является ключевое слово JOIN, которое в действительности не является выражением SELECT, а частью специальной ссылки на таблицу. Объединенная таблица, как определено в стандарте SQL (упрощенный):

<table reference> ::=
    <table name>
  | <derived table>
  | <joined table>

Вернемся к нашему примеру:

FROM a, b

a может по сути быть объединенной таблицей:

a1 JOIN a2 ON a1.id = a2.id

Развивая предыдущее выражение, получим:

FROM a1 JOIN a2 ON a1.id = a2.id, b

Хотя не рекомендуется объединять синтаксис, где приводится разделенный запятыми список ссылок на таблицы, с синтаксисом объединенных таблиц, но так можно делать. В результате, объединенная ссылка будет содержать величины a1+a2+b.

Производные таблицы еще мощнее, чем объединенные таблицы. Мы до этого еще дойдем.

Что мы из этого узнаем?

Всегда нужно думать категориями ссылок на таблицы. Это поможет не только понять, как данные переходят в предложениях SQL, (смотрите предыдущий раздел), но и как создаются сложные ссылки на таблицы.

И, что важно, понять, JOIN является ключевым словом для построения соединенных таблиц. А не частью оператора SELECT. Некоторые базы данных позволяют использование JOIN в операторах INSERT, UPDATE, DELETE

5. В SQL следует использовать таблицы JOIN, вместо разделенных запятыми

Ранее мы видели это предложение:

FROM a, b

Продвинутые разработчики SQL, вероятно, скажут, что не следует вообще использовать список с разделителями-запятыми, и всегда отображать таблицы JOIN. Это улучшит читаемость инструкции SQL, и тем самым предотвратить ошибки.

Одна очень распространенная ошибка заключается в том, чтобы где-то забыть предикат JOIN. Подумайте о следующем:

FROM a, b, c, d, e, f, g, h

WHERE a.a1 = b.bx

AND a.a2 = c.c1

AND d.d1 = b.bc

-- etc...

Синтаксис соединенных таблиц:

• Безопаснее, так как предикаты join можно размещать вблизи соединенных таблиц, тем самым предотвращая ошибки.
• Более выразительные, так как можно различать OUTER JOIN, INNER JOIN и т. д.

Что мы из этого узнаем?

Всегда применяйте JOIN. Никогда не используйте ссылки на таблицы, разделенные запятыми, в предложениях FROM.

6. Различные операции JOIN в SQL

Операции JOIN состоят, в основном, из следующих пяти видов:

• EQUI JOIN
• SEMI JOIN
• ANTI JOIN
• CROSS JOIN
• DIVISION

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

EQUI JOIN

Это наиболее распространенная операция JOIN. Содержит два подвида:

• INNER JOIN (или просто JOIN)
• OUTER JOIN (далее подразделяется на LEFT, RIGHT, FULL OUTER JOIN)

Разницу лучше объяснить на примере:

-- Ссылка на таблицу содержит авторов и их книги
-- Есть одна запись для каждой книги и ее автора.
-- Авторы без книг НЕ включены
author JOIN book ON author.id = book.author_id


-- Ссылка на таблицу содержит авторов и их книги
-- Есть одна запись для каждой книги и ее автора.
--... ИЛИ есть записи "empty" (пустые) для авторов без книг
-- ("empty" означает, что все столбцы книги равны NULL)
author LEFT OUTER JOIN book ON author.id = book.author_id


SEMI JOIN

Эта реляционная концепция в SQL может быть выражена двумя способами: С помощью предиката IN или с использованием предиката EXISTS. «Semi» на латыни означает «половина». Этот тип соединения используется для объединения только «половины» ссылки на таблицу. Что это значит? Рассмотрим вновь вышеуказанное объединение автора и книги. Представим, что нам не нужны комбинации автор — книга, а только авторы, у которых есть книги. Тогда можно написать:

-- Использование IN
FROM author
WHERE author.id IN (SELECT book.author_id FROM book)

-- Использование EXISTS
FROM author
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

Хотя нет общего правила, следует предпочесть IN или EXISTS, но можно сказать примерно так:

Предикаты IN легче читать, чем предикаты EXISTS

Предикаты EXISTS более выразительные, чем предикаты IN (т. е. их проще выразить, чем сложные предикаты SEMI JOIN)

В производительности нет какой-либо заметной разницы. Но большая разница в производительности может быть в некоторых базах данных.

Поскольку INNER JOIN также выдают результаты авторов, у которых есть книги, многие начинающие подумают, что можно удалить дубликаты с помощью ключевого слова DISTINCT. Им кажется, что SEMI JOIN можно выразить следующим образом:

-- Найти только тех авторов, которые имеют книги
SELECT DISTINCT first_name, last_name
FROM author
JOIN book ON author.id = book.author_id

Так делать не рекомендуется по двум причинам:

1. Замедляется производительность, так как база данных только для того, чтобы удалить дубликаты, должна загрузить в память множество данных.
2. Такой способ решения не совсем правильный, хотя и приводит к верным результатам в этом примере. Но если вы соединяете больше ссылок на таблицы, удаление дубликатов превращается в реальную проблему.

ANTI JOIN

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

-- Использование IN
FROM author
WHERE author.id NOT IN (SELECT book.author_id FROM book)

-- Использование EXISTS
FROM author
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

Применяются те же правила в отношении производительности, читаемости и выразительности. Однако есть небольшой нюанс в отношении NULL при использовании NOT IN, но эта тема выходит за рамки нашего урока.

CROSS JOIN

Этот оператор создает перекрестное произведение двух соединенных ссылок на таблицу, комбинируя каждую запись первой ссылки с записью второй ссылки на таблицу. Мы уже видели раньше, что это может быть достигнуто посредством ссылок на таблицы с разделителями-запятыми в предложении FROM. В редких случаях, когда это действительно необходимо, в большинстве диалектов SQL перекрестное соединение CROSS JOIN можно написать явным образом:

-- Объединить каждого автора с книгой
author CROSS JOIN book

DIVISION

Реляционное деление — вот кто настоящий зверь. В кратце, если JOIN это умножение, division (деление) — противоположность операции JOIN. Реляционные деления очень трудно выразить в SQL. Поскольку этот урок для начинающих, деление мы не рассматриваем. Для самых храбрых информация находится здесь, здесь и здесь.

Что мы из этого узнаем?

Много чего. В SQL повсюду ссылки на таблицы. Соединяемые таблицы представляют собой довольно сложные ссылки на таблицы. Но между реляционными выражениями и выражениями SQL есть разница. Не все операции реляционного соединения также являются формальными операциями соединения SQL. Имея немного практики и знаний в реляционной теории, вы всегда сможете выбрать верный тип реляционного соединения JOIN и правильно представить его на языке SQL.

7. Производные таблицы SQL похожи на табличные переменные

Раньше мы узнали, что SQL является декларативным языком, и не имеет переменных (хотя в некоторых диалектах SQL они существуют). Но можно написать нечто похожее на переменные. Эти звери называются производными таблицами.

Производная таблица — это не что иное, как вложенный запрос, заключенный в круглые скобки.

-- Производная таблица
FROM (SELECT * FROM author)

Обратите внимание, что некоторые диалекты SQL требуют, чтобы производные таблицы имели корреляционное имя (также называемое псевдонимом).

-- Производная таблица с псевдонимом
FROM (SELECT * FROM author) a

Производные таблицы великолепны, если необходимо обойти проблемы, вызванные логическим упорядочением предложений SQL. Например, если вы хотите повторно использовать выражение столбца в предложении SELECT и WHERE, просто напишите (диалект Oracle):

-- Получить имя и фамилию авторов и их возраст в днях
SELECT first_name, last_name, age
FROM (
  SELECT first_name, last_name, current_date - date_of_birth age
  FROM author
)

-- Если возраст больше, чем 10000 дней
WHERE age > 10000

Обратите внимание, что некоторые базы данных и стандарт SQL:1999 подняли производные таблицы на следующий уровень введением обобщенных табличных выражений. Это позволит повторно использовать одну и ту же производную таблицу несколько раз в одной инструкции SQL SELECT. Приведенный выше запрос будет переведен на (почти) эквивалент:

WITH a AS (
  SELECT first_name, last_name, current_date - date_of_birth age
  FROM author
)
SELECT *
FROM a
WHERE age > 10000

Очевидно, что «a» можно также вывести в отдельное представление для более широкого использования общих подзапросов SQL. Подробнее о представлениях здесь.

Что мы из этого узнаем?

Снова, снова и снова. В SQL все завязано на ссылках, а не столбцах. Как этим воспользоваться. Не бойтесь написания производных таблиц или других сложных ссылок на таблицы.

8. SQL GROUP BY преобразует прежние ссылки на таблицу

Давайте вновь обсудим наше предложение FROM:

FROM a, b

А теперь к соединенной ссылке (см. выше) применим предложение GROUP BY

GROUP BY A.x, A.y, B.z

Предложение производит новую ссылку на таблицу только с тремя оставшимися столбцами (!). Попробуем это переварить. С применением GROUP BY, уменьшается число доступных столбцов во всех последующих логических предложениях, включая SELECT. Ссылаться только на столбцы из предложения GROUP BY в предложении SELECT можно по синтаксической причине.

Обратите внимание, что другие столбцы могут по-прежнему быть доступными в качестве аргументов агрегатных функций:

SELECT A.x, A.y, SUM(A.z)
FROM A
GROUP BY A.x, A.y

Следует заметить, что MySQL, к сожалению, не придерживается этого стандарта, отчего получается только путаница. Не попадайтесь на уловки в MySQL. GROUP BY преобразует ссылки на таблицу Таким образом, можно ссылаться только на столбцы, также упоминаемые в предложении GROUP BY.

Что мы из этого узнаем?

GROUP BY, опять же, работает с табличными ссылками, преобразовывая их в новую форму.

9. SQL SELECT в реляционной алгебре называется проекцией

Мне лично нравится термин «проекция», так как он используется в реляционной алгебре. После того как вы создали ссылку на таблицу, отфильтровали и преобразовали ее, можете переходить к проецированию в другую форму. Предложение SELECT подобно проектору. Табличная функция использующет выражение значения строки для преобразования каждой записи из ранее созданной ссылки на таблицу в конечный результат.

В предложении SELECT можно работать со столбцами, создавая сложные выражения столбцов как части записи/строки.

Есть много специальных правил в отношении характера доступных выражений, функций и т.д. Главное, нужно помнить следующее:

1. Можно использовать только ссылки на столбцы, полученные из ссылки на таблицу в «output».
2. Если у вас есть предложение GROUP BY, вы можете ссылаться только на столбцы из этого предложения или агрегатные функции.
3. Если нет предложения GROUP BY вместо агрегатных можно использовать оконные функции.
4. Если нет предложения GROUP BY, нельзя сочетать агрегатные и неагрегатные функции.
5. Существуют некоторые правила, касающиеся переноса регулярных функций в агрегатные функции и наоборот.

Есть много сложных правил. Которыми можно заполнить еще один урок. Например, причина почему нельзя комбинировать агрегатные функции с неагрегатными функциями в проекции инструкции SELECT без предложения GROUP BY (правило № 4), такова:

1. Это не имеет смысла. Интуитивно.
2. Если не помогает интуиция (например, новичкам в SQL), выручают синтаксические правила. В SQL:1999 реализован оператор GROUPING SETS, а в SQL:2003 — пустой оператор grouping sets: GROUP BY (). Всякий раз, когда присутствует агрегатная функция и нет явного предложения GROUP BY, применяется неявный пустой GROUPING SET (правило №2). Следовательно, исходные правила о логическом упорядочении больше не являются верными, и проекция (SELECT) влияет на результат логически предшествующего, но лексически последовательного предложения (GROUP BY).

Запутались? Да. Я тоже. Давайте вернемся к более простым вещам.

Что мы из этого узнаем?

Предложение SELECT может быть одним из самых сложных предложений в SQL, даже если оно выглядит просто. Все другие предложения только переносят ссылки на таблицы от одного к другому. Предложение SELECT портит всю красоту этих ссылок, полностью их преобразовывая путем применения к ним правил.

Чтобы понять SQL, перед использованием оператора SELECT нужно усвоить все остальное. Даже если SELECT является первым предложением в лексической упорядоченности, он должен быть последним.

10. SQL DISTINCT, UNION, ORDER BY и OFFSET — намного проще

После сложного SELECT мы можем снова вернуться к простым истинам:

1. Операции Set (DISTINCT и UNION)
2. Операции упорядочивания (ORDER BY, OFFSET .. FETCH)

Операции Set

Операции set работают с наборами, которые на самом деле являются таблицами. Почти. Теоретически, это несложно понять.

• DISTINCT удаляет дубликаты после проекции.
• UNION объединяет два подзапроса и удаляет дубликаты
• UNION ALL объединяет два подзапроса, сохраняя дубликаты
• EXCEPT удаляет записи из первого подзапроса, которые также содержатся во втором подзапросе (и затем удаляет дубликаты)
• INTERSECT сохраняет только записи, содержащиеся в обоих поздапросах (а затем удаляет дубликаты)

Удаление дубликатов во всех этих случаях не имеет смысла. Для объединения подзапросов чаще всего следует применять UNION ALL.

Операции упорядочивания

Упорядочение не является реляционной функцией. Это функция, предназначенная только для SQL. Она применяется в самом конце лексического упорядочения и логического упорядочения инструкции SQL. Использование ORDER BY и OFFSET.. FETCH — это единственный способ гарантировать, что записи могут быть доступны по индексу надежным способом. Все остальные способы упорядочивания всегда произвольны и случайны, даже если они могут показаться воспроизводимыми.

OFFSET .. FETCH — это только один вариант синтаксиса. Другие варианты включают LIMIT, OFFSET в MySQL и PostgreSQL .. или TOP и START AT в SQL Server и Sybase. 

Приступаем к работе

Как и в каждом языке, чтобы освоить SQL, требуется практика. 10 вышеупомянутых простых шагов помогут вам понять обычные операции SQL.