Sql select as: Команда AS — новое имя полям или таблицам

SQL

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

Я, как инструктор SQL (советую посетить наш великолепный тренинг!) пишу на нем каждый день, включая работу над библиотекой jOOQ с открытым исходным кодом.

Поэтому я чувствую себя обязанным донести прелести SQL до умов тех, кто все еще испытывает трудности с его пониманием. Этот урок посвящен тем, кто:

• Уже работает с SQL, но не до конца его понимает
• Неплохо знает SQL, но никогда серьезно не задумывался над его структурой
• Хочет обучать SQL других

В этом руководстве мы сосредоточимся только на операторах SELECT. Другие DML-операторы будут подробно рассмотрены в следующий раз.

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

И вот…

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

SELECT first_name, last_name FROM employees WHERE salary > 100000

Все понятно. И вас не волнует, откуда эти записи о сотруднике (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 — это объединенная ссылка на таблицу объединенного значения всех ссылок. Попробуем это переварить.

Указанная выше запись производит комбинированную ссылку на таблицу значений 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>

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

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, вместо разделенных запятыми

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

Продвинутые разработчики 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, но можно сказать примерно так:

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

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

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

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

Подробную информацию о злоупотреблении DISTINCT можно найти в этом посте.

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

SELECT * FROM `USERS` WHERE `ID` > 2 ORDER BY `ID` DESC;

SELECT * FROM `USERS` WHERE `ID` > 2 ORDER BY `ID` ASC;

SELECT * FROM `USERS` ORDER BY `ID` ASC, `DATE` DESC;

SELECT * FROM `USERS` LIMIT 10;

+---------------------+
| SECTION_FULL_NUMBER |
+---------------------+
| 6                   |
+---------------------+
| 118                 |
+---------------------+
| 21                  |
+---------------------+

SELECT `SECTION_FULL_NUMBER` AS 'SECTION' FROM `USERS`;

+---------+
| SECTION |
+---------+
| 6       |
+---------+
| 118     |
+---------+
| 21      |
+---------+

SELECT COUNT(*) FROM `USERS`;

+----------+
| COUNT(*) |
+----------+
| 3        |
+----------+

SELECT COUNT(*) AS 'CNT' FROM `USERS`;

+-----+
| CNT |
+-----+
| 2   |
+-----+

 ВЫБЕРИТЕ FirstName AS First
, LastName AS Last
ОТ персоны. Персона; 

 - Ответ
ВЫБЕРИТЕ FirstName First
, LastName Last
ОТ персоны. Персона; 

 ВЫБРАТЬ ВЕРХНУЮ (Фамилия) 
 ОТ персоны. Персона 

 - Ответ
ВЫБЕРИТЕ ВЕРХНИЙ (Фамилия) как [ПОСЛЕДНЕЕ ИМЯ]
ОТ персоны. Персона 

 - Ответ
ВЫБЕРИТЕ DISTINCT UPPER (FirstName) AS [Отдельные имена]
ОТ персоны. Персона;