Вводная
Когда часто сталкиваешься с какой-либо технологией, языком программирования, стандартом, формируется некая картина их возможностей, границы, в которых они используются. Так может продолжаться достаточно долго, пока на глаза не попадаются примеры, которые расширяют затвердевшие горизонты знания. Сегодня, я хотел бы рассказать о таких примерах и продемонстировать их для языка 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. Пожалуй самый известный факт в этой статье
Псевдо таблица
В нашем примере mock — это псевдо таблица (иногда называют виртуальной таблицей). Естественно, предназначены они вовсе не для того, чтобы переврать истинный смысл. Пример такой.SELECT mock.nickname "Прозвище", (CASE WHEN mock.huff THEN 'Да' ELSE 'Нет' END) "Обижается?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
| Прозвище | Обижается? |
| Тапочки | Да |
| Подушка | Да |
| Одеяло | Да |
| Наволочка | Нет |
| Простынка | Нет |
3. Конструктор блока данных. SQL-92
Звучит страшно, просто из-за того, что я не нашел хорошего перевода или интерпретации. И как всегда на примере легче объяснить:
Пример конструктора блока данных
В секции FROM используется ключевой слово VALUES, за которым в скобках данные, строка за строкой. Суть в том, что мы вообще не выбираем данные из какой-то таблицы, а просто создаем их налету, 'называем' таблицей, именуем колонки и далее используем по своему усмотрению. Эта штука оказалось крайне полезной при тестировании разных кейсов sql-запроса, когда данных для некоторых таблиц нет (в Вашей локальной БД), а писать insert лень или иногда очень сложно, ввиду связанности таблиц и ограничений.SELECT name "Имя товара", price "Цена" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)
| Имя товара | Цена |
| Тапочки | 100.00 |
| Подушка | 200.00 |
4. Время, Дата и Время-и-Дата
Наверное каждый сталкивался в запросах, с необходимостью указания времени, даты или даты-и-времени. Во многих СУБД поддерживаются литералы t, d и ts соответственно для работы с этими типами. Но проще объяснить на примере:
Пример с литералом ts
Для литералов d и t все аналогично.SELECT name "Имя товара", act_time "Точное время" FROM goods WHERE act_time = {ts '2017-01-01 01:01:01.01'}
| Имя товара | Точное время |
| Тапочки | 2017-01-01 01:01:01.01 |
Прошу прощение у читателя, что ввел в заблуждение, но все что сказано в пункте 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
В очередной раз прошу прощение за терминологию. Это один из любимых моих примеров
Пример сравнения блоков данных
Как видно из примера, сравнение блоков данных аналогично сравнению поэлементно значение_1_block_1 = значение_1_block_2, значение_2_block_1 = значение_2_block_2, значение_3_block_1 = значение_3_block_2 с использованием AND между ними.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 |
7. Операторы сравнения с модификаторами ANY, SOME или ALL. SQL-92
Вот здесь требуется пояснение. Но как всегда, сначала пример
Пример сравнения c ALL
Что означает ALL в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 4 и 5), больше любого из найденных значений в подзапросе (1, 2 и 3). 4 больше чем 1 и чем 2 и чем 3. 5 аналогично. Что будет, если мы заменим ALL на ANY?SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM goods WHERE availability)
| id | name |
| 4 | Наволочка |
| 5 | Простынка |
Пример сравнения c ANY
Что означает ANY в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 2, 3, 4 и 5), больше хотя бы одного из найденных значений в подзапросе (1, 2 и 3). Для себя я ассоциировал ALL с AND, а ANY с OR. SOME и ANY аналоги между собой.SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM goods WHERE availability)
| id | name |
| 2 | Подушка |
| 3 | Одеяло |
| 4 | Наволочка |
| 5 | Простынка |
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.
