Статьи

10 простых шагов к полному пониманию SQL

Декабрь 30, 2018

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

Как инструктор по SQL ( посетите наше обучение, это здорово !) Я пишу SQL каждый день и использую SQL с библиотекой Open Source нашей компании jOOQ .

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

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

Этот учебник будет сосредоточен только на операторах SELECT. Другие операторы DML будут рассмотрены в другом руководстве.

Примечание. Это руководство было ранее опубликовано исключительно на Tech.Pro ( см. Историческую версию здесь ). К сожалению, Tech.Pro отключился. С разрешения Tech.Pro мы снова публикуем этот контент в блоге jOOQ.

Здесь…

10 простых шагов к полному пониманию SQL

1. SQL декларативный

Сначала запомни это. Декларативная. Единственная парадигма, где вы «просто» объявляете природу результатов, которые вы хотели бы получить. Не то, как ваш компьютер будет вычислять эти результаты. Разве это не прекрасно?

1
2
3
SELECT first_name, last_name
FROM employees
WHERE salary > 100000

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

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

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

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

2. Синтаксис SQL не является «упорядоченным»

Распространенным источником путаницы является тот простой факт, что элементы синтаксиса SQL не упорядочены по способу их выполнения. Лексический порядок:

  • ВЫБРАТЬ [ОТЛИЧИТЬ]
  • ОТ
  • ГДЕ
  • ГРУППА ПО
  • HAVING
  • UNION
  • СОРТИРОВАТЬ ПО

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

  • ОТ
  • ГДЕ
  • ГРУППА ПО
  • HAVING
  • ВЫБРАТЬ
  • DISTINCT
  • UNION
  • СОРТИРОВАТЬ ПО

Следует отметить три вещи:

  1. FROM — это первое предложение, а не SELECT. Первое, что происходит, — это загрузка данных с диска в память для работы с такими данными.
  2. SELECT выполняется после большинства других пунктов. Самое главное, после FROM и GROUP BY. Это важно понимать, когда вы думаете, что можете ссылаться на вещи, которые вы объявляете в предложении SELECT из предложения WHERE. Следующее невозможно:
    1
    2
    3
    SELECT A.x + A.y AS z
    FROM A
    WHERE z = 10 -- z is not available here!

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

    1
    2
    3
    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, чтобы избежать очень распространенных ошибок. Если вы поймете это различие, станет совершенно очевидно, почему одни вещи работают, а другие нет.

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

3. SQL — это ссылки на таблицы

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

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

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

«Вывод» предложения FROM является объединенной ссылкой на таблицу объединенной степени всех ссылок на таблицы. Давайте переварим это медленно.

1
FROM a, b

Выше приведено комбинированное табличное указание степени a + степень b . Если a имеет 3 столбца, а b имеет 5 столбцов, то в «выходной таблице» будет 8 ( 3 + 5 ) столбцов.

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

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

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

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

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

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

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

1
2
3
4
<table reference> ::=
    <table name>
  | <derived table>
  | <joined table>

Если мы возьмем снова пример из ранее:

1
FROM a, b

a может быть объединенной таблицей как таковой:

1
a1 JOIN a2 ON a1.id = a2.id

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

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

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

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

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

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

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

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

Ранее мы видели этот пункт:

1
FROM a, b

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

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

1
2
3
4
5
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...

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

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

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

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

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

Операции JOIN по сути идут с пятью вариантами:

  • EQUI JOIN
  • ПОЛУ СОЕДИНЕНИЯ
  • ANTI JOIN
  • CROSS JOIN
  • DIVISION

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

EQUI JOIN

Это самая распространенная операция JOIN. У него есть два вкуса:

  • ВНУТРЕННИЙ РЕЙТИНГ (или просто РЕАКЦИЯ
  • ВНЕШНЕЕ ОБЪЕДИНЕНИЕ (дополнительно подправлено как ВЛЕВО, ВПРАВО, ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ)

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

01
02
03
04
05
06
07
08
09
10
-- This table reference contains authors and their books.
-- There is one record for each book and its author.
-- authors without books are NOT included
author JOIN book ON author.id = book.author_id
 
-- This table reference contains authors and their books
-- There is one record for each book and its author.
-- ... OR there is an "empty" record for authors without books
-- ("empty" meaning that all book columns are NULL)
author LEFT OUTER JOIN book ON author.id = book.author_id

ПОЛУ СОЕДИНЕНИЯ

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

1
2
3
4
5
6
7
-- Using IN
FROM author
WHERE author.id IN (SELECT book.author_id FROM book)
 
-- Using EXISTS
FROM author
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

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

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

1
2
3
4
-- Find only those authors who also have books
SELECT DISTINCT first_name, last_name
FROM author
JOIN book ON author.id = book.author_id

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

  • Это очень медленно, так как база данных должна загружать много данных в память, просто чтобы снова удалить дубликаты.
  • Это не совсем правильно, даже если это дает правильный результат в этом простом примере. Но как только вы ПРИСОЕДИНИТЕ больше ссылок на таблицы, вам будет очень трудно правильно удалить дубликаты из ваших результатов.

Еще немного информации о злоупотреблении DISTINCT можно увидеть в этом блоге .

ANTI JOIN

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

1
2
3
4
5
6
7
-- Using IN
FROM author
WHERE author.id NOT IN (SELECT book.author_id FROM book)
 
-- Using EXISTS
FROM author
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

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

CROSS JOIN

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

1
2
-- Combine every author with every book
author CROSS JOIN book

DIVISION

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

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

Много. Опять давайте вбиваем это в наши головы. SQL — это ссылки на таблицы. Объединенные таблицы — довольно сложные ссылки на таблицы. Но есть разница в реляционном и SQL-говорящем. Не все реляционные операции соединения также являются формальными операциями соединения SQL. Приложив немного практики и знаний о реляционной теории, вы всегда сможете выбрать правильный тип реляционного JOIN и перевести его на правильный SQL.

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

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

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

1
2
-- A derived table
FROM (SELECT * FROM author)

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

1
2
-- A derived table with an alias
FROM (SELECT * FROM author) a

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

1
2
3
4
5
6
7
8
-- Get authors' first and last names, and their age in days
SELECT first_name, last_name, age
FROM (
  SELECT first_name, last_name, current_date - date_of_birth age
  FROM author
)
-- If the age is greater than 10000 days
WHERE age > 10000

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

1
2
3
4
5
6
7
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:

1
FROM a, b

А теперь давайте применим предложение GROUP BY к вышеупомянутой комбинированной ссылке на таблицу

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

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

  • Обратите внимание, что другие столбцы все еще могут быть доступны в качестве аргументов агрегатных функций:
    1
    2
    3
    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. Существуют некоторые правила в отношении обертывания обычных функций в агрегатные функции и наоборот.
  6. Есть …

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

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

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

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

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

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

10. SQL DISTINCT, UNION, ORDER BY и OFFSET снова просты

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

  • Операции над множествами (DISTINCT и UNION)
  • Операции заказа (ORDER BY, OFFSET .. FETCH)

Операции над множествами

Операции над множествами работают с «множествами», которые на самом деле являются ничем иным, как… таблицами. Ну, почти. Концептуально их легко понять.

  • DISTINCT удаляет дубликаты после проекции.
  • UNION объединяет два подвыбора и удаляет дубликаты
  • UNION ALL объединяет два подвыбора, сохраняя дубликаты
  • ИСКЛЮЧИТЬ удаляет записи из первого подвыбора, которые также содержатся во втором подвыборе (и затем удаляет дубликаты)
  • INTERSECT сохраняет только записи, содержащиеся в обоих подвыборах (а затем удаляет дубликаты)

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

Заказ операций

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

OFFSET .. FETCH — только один вариант синтаксиса. Другие варианты включают LIMIT .. OFFSET в MySQL и PostgreSQL или TOP .. START AT для SQL Server и Sybase. Хороший обзор различных способов реализации OFFSET .. FETCH можно увидеть здесь .

Давай приступим к работе

Как и на любом языке, для освоения SQL требуется много практики. Вышеуказанные 10 простых шагов помогут вам лучше понять каждый день SQL, который вы пишете. С другой стороны, также полезно учиться на общих ошибках. В следующих двух статьях перечислено много типичных ошибок, которые Java (и другие) разработчики делают при написании SQL:

Ссылка: 10 простых шагов к полному пониманию SQL от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
38
Share: