Pull to refresh

Редкий SQL

Reading time 6 min
Views 49K

Вводная


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

Нюансы


Меня часто спрашивают, а для кого эта статья? Но, поверьте, не всегда легко дать ответить: с одной стороны, есть ниндзя разработчики, которых сложно чем то удивить, а с другой — молодые падаваны. Но одно точно могу сказать — для читателя, которого интересует SQL, который способен дополнять свою богатую картину мелкими, но очень интересными деталями. В данной статье не будет километровых страниц sql-запроса, максимум 1, 2 строчки и только то, что встречается на мой взгляд редко. Но так как я хочу быть до конца откровенным, если Вы с sql на ты, статья покажется скучноватой. Все примеры в статье, за исключением первого и четвертого можно отнести к стандарту SQL-92.

Данные


Для того, чтобы упростить нам жизнь, я накидал простую табличку с данными, на которой будут опробованы те или иные моменты и для краткости, я буду приводить результат эксперимента над ними. Все запросы я проверяю на PostgreSql.
Скрипты и таблица с данными
CREATE TABLE goods(
    id bigint NOT NULL,
    name character varying(127) NOT NULL,
    description character varying(255) NOT NULL,
    price numeric(16,2) NOT NULL,
    articul character varying(20) NOT NULL,
    act_time timestamp NOT NULL,
    availability boolean NOT NULL,
    CONSTRAINT pk_goods PRIMARY KEY (id));

INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (1, 'Тапочки', 'Мягкие', 100.00, 'TR-75', {ts '2017-01-01 01:01:01.01'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (2, 'Подушка', 'Белая', 200.00, 'PR-75', {ts '2017-01-02 02:02:02.02'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (3, 'Одеяло', 'Пуховое', 300.00, 'ZR-75', {ts '2017-01-03 03:03:03.03'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (4, 'Наволочка', 'Серая', 400.00, 'AR-75', {ts '2017-01-04 04:04:04.04'}, FALSE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (5, 'Простынка', 'Шелковая', 500.00, 'BR-75', {ts '2017-01-05 05:05:05.05'}, FALSE);

id name description price articul act_time availability
1 Тапочки Мягкие 100.00 TR-75 2017-01-01 01:01:01.01 true
2 Подушка Белая 200.00 PR-75 2017-01-02 02:02:02.02 true
3 Одеяло Пуховое 300.00 ZR-75 2017-01-03 03:03:03.03 true
4 Наволочка Серая 400.00 AR-75 2017-01-04 04:04:04.04 false
5 Простынка Шелковая 500.00 BR-75 2017-01-05 05:05:05.05 false


Запросы


1. Двойные кавычки


И первое что у меня есть — это простой вопрос: Смогли бы Вы привести пример sql-запроса c использованием двойных кавычек? Да, не с одинарными, двойными?
Пример с двойными кавычками
SELECT name "Имя товара" FROM goods
Имя товара
Тапочки
Подушка
Одеяло
Наволочка
Простынка

Я был очень удивлен, когда увидел это в первый раз. Если попробовать изменить двойные кавычки на одинарные, результат будет совершенно иной!
Пример с одинарными кавычками
SELECT name 'Это данные' FROM goods WHERE id = 1

name
Это данные


Может показаться, что это не очень полезный пример для реальной разработки. Для меня это не так. Теперь я его активно использую во всех своих sql-заготовках. Суть проста, когда возвращаешься через пол года к sql-запросу из 40 колонок, ой как спасает 'нашенское' их название. Не смотря, что я не указал про SQL-92, в последней редакции упоминание про двойные кавычки имеются.

2. Псевдо таблица. SQL-92


Немного не точно, с точки зрения терминологии, но суть проста — таблица получающаяся в результате подзапроса в секции FROM. Пожалуй самый известный факт в этой статье
Псевдо таблица
SELECT mock.nickname "Прозвище", (CASE WHEN mock.huff THEN 'Да' ELSE 'Нет' END) "Обижается?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
Прозвище Обижается?
Тапочки Да
Подушка Да
Одеяло Да
Наволочка Нет
Простынка Нет
В нашем примере mock — это псевдо таблица (иногда называют виртуальной таблицей). Естественно, предназначены они вовсе не для того, чтобы переврать истинный смысл. Пример такой.

3. Конструктор блока данных. SQL-92


Звучит страшно, просто из-за того, что я не нашел хорошего перевода или интерпретации. И как всегда на примере легче объяснить:
Пример конструктора блока данных
SELECT name "Имя товара", price "Цена" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)
Имя товара Цена
Тапочки 100.00
Подушка 200.00
В секции FROM используется ключевой слово VALUES, за которым в скобках данные, строка за строкой. Суть в том, что мы вообще не выбираем данные из какой-то таблицы, а просто создаем их налету, 'называем' таблицей, именуем колонки и далее используем по своему усмотрению. Эта штука оказалось крайне полезной при тестировании разных кейсов sql-запроса, когда данных для некоторых таблиц нет (в Вашей локальной БД), а писать insert лень или иногда очень сложно, ввиду связанности таблиц и ограничений.

4. Время, Дата и Время-и-Дата


Наверное каждый сталкивался в запросах, с необходимостью указания времени, даты или даты-и-времени. Во многих СУБД поддерживаются литералы t, d и ts соответственно для работы с этими типами. Но проще объяснить на примере:
Пример с литералом ts
SELECT name "Имя товара", act_time "Точное время" FROM goods WHERE act_time = {ts '2017-01-01 01:01:01.01'}
Имя товара Точное время
Тапочки 2017-01-01 01:01:01.01
Для литералов d и t все аналогично.
Прошу прощение у читателя, что ввел в заблуждение, но все что сказано в пункте 4 не относится к языку SQL, а относится к возможностям предобработки запросов в JDBC.

5. Отрицание. SQL-92


Все мы знаем про оператор NOT, но очень часто забывают, что его можно применять как к группе предикатов так и к одиночной колонке:
Пример с отрицанием
SELECT id, name, availability FROM goods WHERE NOT availability
-- или так
SELECT id, name FROM goods WHERE NOT (id = 1 OR id = 2 OR id = 3)
id name availability
4 Наволочка false
5 Простынка false


6. Сравнение блоков данных. SQL-92


В очередной раз прошу прощение за терминологию. Это один из любимых моих примеров
Пример сравнения блоков данных
SELECT * FROM goods WHERE (name, price, availability) = ('Наволочка', 400.00, FALSE)
-- или его аналог
SELECT * FROM goods WHERE name = 'Наволочка' AND price = 400.00 AND availability = FALSE
id name description price articul act_time availability
4 Наволочка Серая 400.00 AR-75 2017-01-04 04:04:04.04 false
Как видно из примера, сравнение блоков данных аналогично сравнению поэлементно значение_1_block_1 = значение_1_block_2, значение_2_block_1 = значение_2_block_2, значение_3_block_1 = значение_3_block_2 с использованием AND между ними.

7. Операторы сравнения с модификаторами ANY, SOME или ALL. SQL-92


Вот здесь требуется пояснение. Но как всегда, сначала пример
Пример сравнения c ALL
SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM goods WHERE availability)
id name
4 Наволочка
5 Простынка
Что означает ALL в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 4 и 5), больше любого из найденных значений в подзапросе (1, 2 и 3). 4 больше чем 1 и чем 2 и чем 3. 5 аналогично. Что будет, если мы заменим ALL на ANY?
Пример сравнения c ANY
SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM goods WHERE availability)
id name
2 Подушка
3 Одеяло
4 Наволочка
5 Простынка
Что означает ANY в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 2, 3, 4 и 5), больше хотя бы одного из найденных значений в подзапросе (1, 2 и 3). Для себя я ассоциировал ALL с AND, а ANY с OR. SOME и ANY аналоги между собой.

8. Операторы работы с запросами/под запросами. SQL-92


Достаточно известно, что можно объединить 2 запроса между собой с помощью операторов UNION или UNION ALL. Этим пользуются часто. Но существуют еще 2 оператора EXCEPT и INTERSECT.
Пример с EXCEPT
SELECT * FROM goods EXCEPT (SELECT * FROM goods WHERE availability)
id name description price articul act_time act_time
4 Наволочка Серая 400.00 AR-75 2017-01-04 04:04:04.04 false
5 Простынка Шелковая 500.00 BR-75 2017-01-05 05:05:05.05 false
Собственно из первого множества значений исключаются данные второго множества.
Пример с INTERSECT
SELECT * FROM goods WHERE id > 2 INTERSECT (SELECT * FROM goods WHERE availability)
id name description price articul act_time act_time
3 Одеяло Пуховое 300.00 ZR-75 2017-01-03 03:03:03.03 true
Собственно происходит пересечение первого множества значений и второго множества.
На этом все, спасибо за Ваше внимание.

Источники


BNF Grammars for SQL-92, SQL-99 and SQL-2003
SQL Tutorial

Редакция


N1. Спасибо streetflush за конструктивную критику. Внес статью информацию о том, что является стандартом языка, а что нет.
N2. Исправлен пункт 4, с пояснение о том, что ts/d/t не являюься частью языка SQL. Спасибо за внимательность Melkij.
Tags:
Hubs:
+13
Comments 63
Comments Comments 63

Articles