Вводная
Когда часто сталкиваешься с какой-либо технологией, языком программирования, стандартом, формируется некая картина их возможностей, границы, в которых они используются. Так может продолжаться достаточно долго, пока на глаза не попадаются примеры, которые расширяют затвердевшие горизонты знания. Сегодня, я хотел бы рассказать о таких примерах и продемонстировать их для языка 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| Прозвище | Обижается? |
| Тапочки | Да |
| Подушка | Да |
| Одеяло | Да |
| Наволочка | Нет |
| Простынка | Нет |
3. Конструктор блока данных. SQL-92
Звучит страшно, просто из-за того, что я не нашел хорошего перевода или интерпретации. И как всегда на примере легче объяснить:
SELECT name "Имя товара", price "Цена" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)| Имя товара | Цена |
| Тапочки | 100.00 |
| Подушка | 200.00 |
4. Время, Дата и Время-и-Дата
Наверное каждый сталкивался в запросах, с необходимостью указания времени, даты или даты-и-времени. Во многих СУБД поддерживаются литералы t, d и 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 |
Прошу прощение у читателя, что ввел в заблуждение, но все что сказано в пункте 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 |
7. Операторы сравнения с модификаторами ANY, SOME или ALL. SQL-92
Вот здесь требуется пояснение. Но как всегда, сначала пример
SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM goods WHERE availability)| id | name |
| 4 | Наволочка |
| 5 | Простынка |
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.
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 |
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.
Комментарии (63)
streetflush
10.02.2017 11:17+4Зануда mod:on
Хорошо бы сказать что входит в стандарт SQL, а что в синтаксис конкретной СУБД.
Раз статья рассчитана на новичков, хотелось бы видеть «верное» использование той или иной конструкции и для чего она задумана. (Я вот так и не проникся литералами d t ts)
Зануда mod:off
Не понял про двойные кавычки, чем они лучше чем одинарные?
И стоит указать
reforms
10.02.2017 11:49За замечание к статье спасибо.
Отвечаю на вопросы:
Хорошо бы сказать что входит в стандарт SQL, а что в синтаксис конкретной СУБД
Когда я впервые познакомился с BNF Grammars for SQL-92, я не нашел там возможности указывать двойные кавычки (N1) и ничего про {ts/d/t} (N4), все остальное и из него. Однако: в последней версии (может быть редакции?) использование двойных кавычек там есть. Про ts/d/t встречал часто в литературе, форумах — что это стандарт.
Не понял про двойные кавычки, чем они лучше, чем одинарные?
В первом случае — это алиас, во втором данные.Пример 1SELECT name "Алиас колонки" FROM goods WHERE id = 1
Алиас колонки
Тапочки
Melkij
10.02.2017 12:07+1name 'Это данные' — это синтаксис указания константы указанного типа данных. Да, в postgresql есть строковый тип данных name, используется в служебных схемах pg_catalog.
Попробуйте другой тип данных
select id 'test' from testint limit 5;
Получите ошибку ERROR: type «id» does not exist
Попробуйте написать полностью name as 'это данные' — получите ошибку парсера.
Так же литералы timestamp в мануале и обозначаются какwhere created_at = timestamp '2017-01-03 12:43:54'reforms
10.02.2017 12:21name 'Это данные' — это синтаксис указания константы указанного типа данных
Так и есть, поэтому я собственно и написал, про одинарные кавычки, что результат будет совершенно иной. И конкретно в моем случае — он такой.
Так же литералы timestamp в мануале и обозначаются как...
Да, Вы опять правы. Но мне пример с timestamp кажется менее интересным, чем с {ts ...}
streetflush
10.02.2017 12:57+1Я сижу в mssql
там
SELECT name Алиас FROM goods WHERE id = 1 SELECT name "Алиас колонки" FROM goods WHERE id = 1 SELECT name 'Тоже алиас' FROM goods WHERE id = 1 SELECT name [Тоже алиас] FROM goods WHERE id = 1
можно и явно указать со предлогом as для всех вышепреведенных случаях
Например
SELECT name as [Тоже алиас] FROM goods WHERE id = 1
npocmu
10.02.2017 14:15+1Двойные кавычки и квадратные скобки это стандартный способ для ограничения идентификаторов (а точнее, квадратные скобки стандартны только для mssql). Одинарные же кавычки в алиасе — видимо работают только там, по некому недоразумению.
Сравните:
CREATE TABLE "Тестовая таблица" (id INT) DROP TABLE "Тестовая таблица"
и
CREATE TABLE 'Тестовая таблица' (id INT) DROP TABLE 'Тестовая таблица'
d-stream
10.02.2017 22:30select
[id] = t.id,
t.code as [код],
t.descr «описание»
t.option as option
from t
-)
streetflush
15.02.2017 16:03Incorrect syntax near '«'.
d-stream
15.02.2017 16:48Ну логично — редактор заменил двойные кавычки на двойные угловые -)
Забыл тэгами «код» обернуть
select [id] = t.id, t.code as [код], t.descr "описание" t.option as option from tstreetflush
16.02.2017 11:10Тогда неясен смысл вашего предыдущего ответа…
d-stream
16.02.2017 11:52Я просто привел вариант вавилонского смешения в одном запросе…
На мой вкус наиболее комфортен вариант
id =… или [id] =
Melkij
10.02.2017 12:01+5Смогли бы Вы привести пример sql-запроса c использованием двойных кавычек?
Вы это сейчас серьёзно? Единственный стандартный способ сказать «это имя объекта, а не ключевое слово»?
В mysql вообще дефолтно ANSI_QUOTES выключен и where foo = 'bar' эквивалентен where foo = «bar», отчего в безалаберном коде можно видеть постоянно в качестве литералов.
таблица получающаяся в результате подзапроса в секции FROM
Самое банальное, что умеет даже mysql? Подзапрос? Редкий SQL?
А CTE тогда что? Про рекурсивный CTE, видимо, знают не больше десятка человек на всю планету?
Postgresql же такие подзапросы не только умеет выполнять, он их умеет и переписывать до состояния «да нет тут никакого подзапроса, он мне только мешал селективный индекс применять». Как думаете, насколько эта штука редка, что для этого даже специально тюнили оптимизатор?
В секции FROM используется ключевой слово VALUES
Values — это частный случай select. Но да, менее известная штука. Хотя стандартная, необязательная часть аж SQL-92.
Все мы знаем про оператор NOT, но очень часто забывают, что его можно применять как к группе предикатов так и к одиночной колонке
Требует булево выражение, которое по совпадению может быть boolean полем.
Можете проверить, select not 1; будет ошибкой, not применяется к выражению (или полю) строго булева типа. А булево поле является представителем булева типа, поэтому к нему напрямую можно применять булевы операторы.
not к выражению — может быть элегантным способом отстрелить использование индексов, если оптимизатор не догадается переписать. Простые выражения переписывать умеет.
6. Сравнение блоков данных
Аналогично работает и in
(foo, bar) in ((1,2), (3,4))
эквивалентен
(foo = 1 and bar = 2) or (foo = 3 and bar = 4)
Операторы работы с запросами/под запросами
EXCEPT и INTERSECT — постгресовые расширения стандарта, что надо учитывать, говоря про sql.
Напомню заодно лишний раз, что почти всегда, когда говорят про union — подразумевают именно union all. Между ними разница в поведении и производительности, т.к. union подразумевает удаление дубликатов строк.
А вот про редко-используемый, потому что малоизвестный — это filter.
select count(*), count(*) filter(where status = 1) from ...
Посчитает и количество строк всего и количество строк с status = 1. Вместо:
select sum(case when status=1 then 1 else 0)
Доступен только с postgresql 9.4, хотя и стандартная вещь, упрощающая чтение этих sum+case.
Можно упомянуть оконные функции, но редкими их назвать можно только если ранее работали исключительно с mysql, их не умеющим. Ну или со всяким orm, где до нормального sql не добраться.reforms
10.02.2017 12:33Самое банальное, что умеет даже mysql? Подзапрос? Редкий SQL?
Разве я написал обратное? >> Пожалуй самый известный факт
Хотя стандартная, необязательная часть аж SQL-92.
Вы можете подтвердить свои слова?
Вот отрывок из BNF Grammars for SQL-92(ссылка внизу): |table value constructor| ::= VALUES |table value constructor list|
Сравнение блоков данных… Аналогично работает и in: (foo, bar) in ((1,2), (3,4))
Спасибо за новые знания, этого я не знал.
select sum(case when status=1 then 1 else 0)
мне кажется Вы пропустили END.Melkij
10.02.2017 12:57Разве я написал обратное?
Угу, пост так озаглавлен.
Вы можете подтвердить свои слова?
http://modern-sql.com/feature/values
SQL-92 различает 3 уровня поддержки описанных возможностей: entry, intermediate and full
entry-level для insert… values — его должны реализовать все.
values как select — в части full. Соответственно получается тоже SQL-92, но можно быть SQL-92 и не поддерживать эту часть.
мне кажется Вы пропустили END
Да, действительно.reforms
10.02.2017 14:01Угу, пост так озаглавлен.
Согласен, сам себе противоречу. Но прежде чем запоститься, опробовал перечисленные кейсы на своей фокус группе. К большому удивлению подзапрос в секции FROM оказался мало известным фактом.
Кое-что пропустил из предыдущего комментария:
EXCEPT и INTERSECT — постгресовые расширения стандарта, что надо учитывать, говоря про sql.
С чего Вы это взяли? Все в той же доке EXCEPT/EXCEPT ALL и INTERSECT/INTERSECT ALL присутствуют. Да, например, в ORACLE есть MINUS vs EXCEPT.
И чтобы больше не халиварить, спрашиваю для себя — 'Вы все указанные примеры знали до прочтения статьи?'
СпасибоMelkij
10.02.2017 14:35+1С чего Вы это взяли?
Хм, да, числятся как стандартные. Странно, вроде бы раньше имели примечание, что являются расширением. Поленился перепроверить свою глючную память. Так что мой косяк.
Знал все примеры, кроме {ts '2017-01-01 01:01:01.01'}. Который решил сейчас всё-таки проверить: тестовый PostgreSQL 9.5.5 с таким синтаксисом не согласен и считает недопустимым. Враппером через php/pdo — аналогично. На enSO говорят, что это синтаксис ODBC или JDBC литералов. Для JDBC нашёл подтверждение:
The driver will replace the escape clause with the equivalent DBMS-specific representation
Т.е. это получается не часть SQL, а часть драйвера и именно драйвера заменяет эту запись на понятную СУБД.reforms
10.02.2017 15:13Спасибо за честный ответ.
тестовый PostgreSQL 9.5.5 с таким синтаксисом не согласен и считает недопустимым
Да, а это уже мой серьезный косяк. Я уже как nth лет использую фронт для работы с СУБД в eclipse и пример c ts примелькался. С Вашего позволения внесу корректив в статью
GlukKazan
10.02.2017 14:40Честно, говоря, примеры действительно какие-то банальные (не в обиду вам будет сказано).
Но про подзапросы в from новички действительно часто забывают.
Так что, польза от статьи, вероятно есть.
Mingun
10.02.2017 21:11+1Простите, как можно забыть про подзапросы во
from? По-моему это первая и совершенно логичная вещь, которую применяешь даже не задумываясь, когда надо, допустим, отфильтровать / сгруппировать таблицу по неким условиям, вырождающимся в длинные многоступенчатыеcaseвselect-списке. Что-то типа такого:
select another_field, sum(numeric_data) numeric_data from ( select (case /* здесь длинная череда условий when ... then ... else ... возможно, даже вызовы функций (в group by не засунешь) */ end) some_field , (case /* здесь длинная череда условий when ... then ... else ... возможно, даже вызовы функций (в group by не засунешь) */ end) another_field , numeric_data from sometable ) t where somefield = 'somevalue' group by another_field order by another_field ;
Это же идеально ложится на подзапрос во фразе
from.GlukKazan
10.02.2017 22:49Это вы мне объясняете? Я говорил про новичков.
Как они про это забывают — мне непостижимо, но факт остаётся фактом.
nikolayv81
11.02.2017 21:37Подзапросы в from лучше бы и не знали, когда вынесено в with гораздо проще понять что человек хотел получить, особенно когда запрос на пару страниц в pl/sql.
GlukKazan
12.02.2017 14:56with (в Oracle) появился несколько позже
nikolayv81
13.02.2017 16:13Думаю сейчас уже "слишком много лет прошло" да и это особенности конкретной СУБД
GlukKazan
13.02.2017 16:18для энтерпрайза никогда не бывает «слишком много»
nikolayv81
13.02.2017 21:0125 марта 2003 года — 9i Release 2
Я так понимаю что не так много людей реально работает с 32 битной версией oracle, и думаю те кто работает прекрасно понимают что мой комментарий про "лучше бы пользовались with-ами" относился не к ним а к тем кто пришёл в работу с базами данных сильно позже. В принципе ничто не мешает использовать оба способа записи, просто в тот же план запроса в oracle легче ассоциируется с записью with-ами. Также можно пользоваться "плюсами", просто в при "больших простынях" неудобно, на мой взгляд.
torbasow
13.02.2017 13:47WITH материализуется, это не всегда нужно.
nikolayv81
13.02.2017 16:15Я так понимаю от СУБД зависит, в Oracle наоборот зачастую hint materialized использовать приходится.
LastDragon
11.02.2017 08:47+1Можете проверить, select not 1; будет ошибкой, not применяется к выражению (или полю) строго булева типа
В MySQL это просто логический оператор, поэтому данный запрос вернет
0.Melkij
11.02.2017 10:35+1Лучше начать с того, что mysql не умеет булевый тип вообще и, к тому же, отличается довольно мягкой системой неявного приведения типов.
Но да, не указал, что этот момент именно про postgresql. В mysql допустим, в других СУБД — не знаю.
Alexeyslav
10.02.2017 12:06+2К сожалению, это больше специфика именно самого SQL-сервера, на информиксе большая часть примеров синтаксически неправильна.
Двойные кавычки не работают, но зато есть такое ключевое слово как AS и спокойно переваривает только такую запись: SELECT name as Имя_товара FROM goods; хотя во многих случаях AS можно упустить, но сразу теряется в наглядности.
Псевдо-таблицы… это ни что иное как алиасы к именам таблиц в запросе. Когда имена таблиц по 20 символов, в запросе участвует 3-4 таблицы одновременно(а у нас есть такие что и десяток таблиц, 6 из которых одна и та же под разными алиасами) писать запрос становится очень лениво. И тянутся эти алиасы ещё со времен FoxPro, хотя может и раньше но не знаю не застал.
Сравнение блоков данных — не работает. Очень жаль, это сильно упростило бы жизнь.
ANY/ALL на удивление работает.
Работа с множествами происходит через INNER/OUTER.
EXCEPT и INTERSECT заменяются на NOT IN и IN.
UA3MQJ
10.02.2017 14:18Да, я тоже обычно или IN или NOT IN применяю.
darthunix
11.02.2017 14:13Мой опыт PostgreSQL подсказывает никогда не использовать конструкцию NOT IN, это самый медленный вариант из возможных — все жду, когда планировщик PostgreSQL научится с ним нормально работать. Обычно исключение строк реализуется или через EXCEPT, или через LEFT JOIN… WHERE id IS NULL. Но EXCEPT из этих вариантов самый производительный.
GlukKazan
10.02.2017 14:45Кстати, псевдонимы в PostgreSql без as тоже не всегда работают. У меня лично были проблемы с псевдонимом «name» и «value». Разумеется, не специально получилось, а при перетаскивании запросов с Oracle. Так что as лучше ставить.
QuickJoey
10.02.2017 14:52+1Двойные кавычки не работают, но зато есть такое ключевое слово как AS и спокойно переваривает только такую запись: SELECT name as Имя_товара FROM goods; хотя во многих случаях AS можно упустить, но сразу теряется в наглядности.
Вот согласен на 100%, не надо ничего опускать, читать же неприятно.
Кстати, о наглядности, интересно, какое у присутствующих мнение по поводу такой записи:
SELECT a.ClientID ,a.ClientName ,a.ProductID ,a.ManagerID ,b.ProductName ,c.ManagerName FROM Clients a LEFT JOIN Products_view b ON a.ProductID=b.ProductID LEFT JOIN Managers c ON a.ManagerID=c.ManagerID WHERE a.ClientName LIKE '%' AND ((@ManagerName IS NULL) OR (@ManagerName IS NOT NULL AND b.ManagerName=@ManagerName)) AND c.ProductName LIKE @ProductName+'%' ORDER BY a.ClientName ,b.ManagerName
смысл записи примерно в следующем:
1. каждое ключевое слово и поле на новой строке
2. запятые перед полями, в столбик, а не в конце где они рассыпаются в зависимости от длины наименования поля
3. псевдонимы присоединяемых таблиц присваиваются последовательно, по алфавиту, у всех псевдонимов одинаковая длина (зачастую она буква)
4. псевдонимы выровнены так, чтобы быть в одну колонку, а искать выбранные поля из конкретной таблицы становится просто
5. поля в выборку пишутся по алфавиту псевдонима таблицы (сначала поля таблицы a, потом b, потом c и т. д.)
6. условия WHERE по каждому полю пишутся каждое на новой строке
7. параметры сортировки пишутся в столбик, как поля в разделе SELECT
Всё это, чтобы повысить читабельность выборок с большим количеством присоединённых таблиц и/или условий выборки.npocmu
10.02.2017 15:17Я всегда пишу смысловую часть на отдельных строчках от ключевых слов
SELECT,FROM,WHERE,ORDER BY,GROUP BY:
SELECT a.ClientID ,a.ClientName ,a.ProductID ,a.ManagerID
и т.д. Причина: легче делать рефакторинг, т.к. можно менять строки местами и их копипастить без удаления ключевых слов. Ну и джоины отбиваю отступом — но это на любителя:
FROM Clients a LEFT JOIN Products_view b ON a.ProductID = b.ProductID LEFT JOIN Managers c ON a.ManagerID = c.ManagerID
Ну и пункт 5 мне совершенно не понятен. Поля располагаю так, что бы было удобно изучать результат. Например, код в таблице фактов и его описание из справочника будут идти подряд, несмотря на то, что они из разных таблиц.
QuickJoey
10.02.2017 15:23Так пишут многие, поэтому я и спрашиваю «как выглядит». Но я честно, не вижу в этом удобства, все таблицы перемежаются ключевыми словами, и тяжело вычленить алиасы, они все на разных уровнях? Результаты можно поизучать с лёгким неудобством, во имя удобства последующего чтения (все поля таблицы примерно в одном месте).
npocmu
10.02.2017 15:49+2ну не знаю… Ради редкого изучения кода жертвовать удобством работы с данными… У меня были случаи, когда я менял порядок полей в таблице/виде, только потому, что их неудобно было просматривать (важные поля находились не рядом или вылезали за границы экрана).
Хороший инструмент просмотра БД спасает иногда, но не всегда он есть под рукой.
А про алиасы… Извините, но однобуквенные алиасы это ужас. У меня они осмысленные и всегда одинаковые для таблиц с одинаковыми сущностями (за исключением суффикса, в случае множественного присоединения). Поэтому и смысла вычленения алиасов глазами нет никакого.
QuickJoey
10.02.2017 15:56Видимо я код изучаю сильно чаще, чем данные, мне на данные плевать) да и смотрю я их в приложении, где поля уже построены так, как требует потребитель. А вот код наоборот, ускорить выполнение, понять почему криво работает фильтр и т. д.
Про осмысленные алиасы – хорошо, когда таблиц немного, или они все имеют разные корни, а когд много похожих названий, сокращения становятся всё длиннее и неудобнее, не только читать, но и писать.npocmu
10.02.2017 16:43+1У вас распространенное заблуждение, что алиасы это сокращенное название. Тогда как алиас (ИМХО) это смысловое название выборки.
Например:
SELECT Invoices.* FROM dbo.V_Invoices Invoices
но
SELECT Invoice.* FROM dbo.V_Invoices Invoice WHERE Invoice.id = 100
или
SELECT InvoicesOld.* FROM dbo.V_Invoices InvoicesOld WHERE InvoicesOld.issueDate < DATEADD(mm,-1,GETDATE())
Вот это реально помогает понимать код (а не буквы в столбик).
QuickJoey
10.02.2017 17:03Во-первых, я не вижу никакой разницы между вашими примерами, что вы этим хотели показать – загадка.
Во-вторых, вы с собой договоритесь с версией происходящего, у меня «распространённое заблуждение», или всё таки «ваше IMHO».
В-третьих, alias (псевдоним) на то и псевдоним, чтобы использовать его на своё усмотрение для повышения читаемости кода.
Вот, кстати, что по этому поводу пишет technet.
спойлерУдобочитаемость инструкции SELECT может быть улучшена с помощью псевдонима таблицы, известного также как корреляционное имя или переменная диапазона. Псевдоним таблицы можно назначить с ключевым словом AS или без него:
table_name AS table alias
table_name table_alias
В следующем примере псевдоним c назначается таблице Customer, а псевдоним s — таблице Store.
USE AdventureWorks2008R2; GO SELECT c.CustomerID, s.Name FROM Sales.Customer AS c JOIN Sales.Store AS s ON c.CustomerID = s.BusinessEntityID ;
ссылка.npocmu
10.02.2017 19:08Разница в том, что алиас не бессмысленная буковка
iилиa, а говорящее название:
Invoices— все счета,
Invoice— один счет
InvoicesOld— некие старые счета
И я, смотря на любое место многостраничного запроса, могу сразу понять о чем речь, без необходимости прыгать к столбику алиасов в предложенииFROM.
В-третьих, alias (псевдоним) на то и псевдоним, чтобы использовать его на своё усмотрение для повышения читаемости кода.
Хотя бы один пример повышения ЧИТАЕМОСТИ можно? Ваша метода способствует лишь скорости набора кода. А читаемость вы пытаетесь повысить экзотическим форматированием кода.
Ссылка на technet лишь подтверждает мое мнение о распространенном заблуждении.
Mingun
10.02.2017 21:50+1Аналогичный запрос на Oracle я бы написал так (хотся в данном конкретном случае
left join, конечно же, не нужен, поскольку по условиюand m.product_name like v_product_name||'%'таблицаmanagersобязательно будет присоединена, а следовательно, и представлениеproducts_view):
select c.client_id, c.client_name, c.product_id, c.manager_id, p.product_name, m.manager_name from clients c left join products_view p on (p.product_id = c.product_id) left join managers m on (m.manager_id = p.manager_id) where c.client_name like '%' and ( v_manager_name is null or v_manager_name is not null and p.manager_name = v_manager_name ) and m.product_name like v_product_name||'%' order by c.client_name, p.manager_name;
Особенности:
- SQL в Oracle — регистронезависимый язык, поэтому все ключевые слова, названия таблиц, колонок и переменных пишутся в нижнем регистре. Так меньше визуального шума и «крика». Так же по этой причине имена всех переменных, колонок и таблиц в
змеином_стиле. - Локальные переменные предваряются префиксом
v_, параметры функций — префиксомp_, константы — префиксомc_. - Таблица обзываются максимально короткими псевдонимами, однако отражающими их смысловую суть. Как правило, в качестве псевдонима выступает первая буква названия таблицы.
- Ключевое слово
asпри объявлении псевдонимов не используется, оно только отвлекает. - Фразы
fromиjoinпишутся каждая на своей строке, без отступа. Кроме того, условия послеonво фразеjoinзаключаются в скобки. Это особенно удобно, если условия становятся длинными и их приходится разбивать на несколько строк — сразу видно границы соединения. - В условиях
onсначала идёт поле присоединяемой таблицы, а потом — той, к которой присоединяем. - Список столбцов в
select,group byиorder by-списках располагается так, чтобы он занимал как можно меньше места, при этом вмещался на экран по горизонтали, а поля располагались в смысловом порядке. Порядок полей во фразеgroup byсовпадает с таковым во фразеselect, кроме того, все они располагаются до полей, по которым ведётся агрегирование. - Условия во фразе
whereрасполагаются по одному на строку. - Ключевые слова
andиorвыравниваются по правому краю словаwhere. Таким образом все условия начинаются с одной колонки и их значительно легче читать. - Если встречается несколько блоков, соединённых через
or, ключевое словоorраполагается на отдельной строке и выравнивается по левому краю. - Нет лишним скобкам! Незачем заключать два условия, соединённых по
or, если внутри них есть условияand. Приоритетandвышеor, поэтому лишние скобки не нужны, они только затрудняют чтение.
GlukKazan
10.02.2017 23:01- Идентификаторы в двойных кавычках регистрозависимы (без кавычек преобразует в верхний регистра, в отличии от Постгресса, в этом месте мы огребли)
- Полезно чтобы не путать со столбцами
- Спорно, про псевдоним очень спорно
- Ага, мы тоже так делали, после переезда на Постгресс огребли (у него без as не везде работает)
- Про on полезно
- Большого смысла не вижу
- Почему бы нет (главное чтобы звезды в запросах не использовали)
- Большого смысла не вижу
- Субъективно (к тому же автоформатеры норовят испортить всю малину)
- См. предыдущий пункт
- Вредный совет
Прошу не рассматривать мой комментарий как догму (равно как и советы выше).
- SQL в Oracle — регистронезависимый язык, поэтому все ключевые слова, названия таблиц, колонок и переменных пишутся в нижнем регистре. Так меньше визуального шума и «крика». Так же по этой причине имена всех переменных, колонок и таблиц в
nikolayv81
13.02.2017 21:22В Oracle
- табуляцией отделяю следующий уровень.
- списки полей с запятой (причина комментирование через --, часто приходиться комментировать большие куски запросов)
- параметры в блок with в подзапрос по возможности.
- порядок join-ов в порядке того, как я, а не оптимизатор, вижу выборку, т.к. частенько приходится его переубеждать.
- on на следующей строчке после join
- Всегда field as name т.к. подсветка в девелопере выделяет as если запрос не очень большой стараюсь as-выравнивать табуляцией.
- Всегда пишу inner join и left join выровненные на 2 пробела сдвинутые от from.
- Условия стараюсь 1 на строчку, оператор всегда в начале, после on если несколько условий то общую скобку для выравнивания.
Самое печальное что так и не вышло заставить встроенный форматировщик делать как мне удобно, а как-то раз испытал небольшой шок, когда один из коллег после правки процедуры в большом пакете взял и сделал в нём автоформат...
UA3MQJ
10.02.2017 14:17+1Мне в свое время очень помогло чтение документации по MySQL. Там было очень много примеров, из которых я все и начал понимать.
Спасибо за статью, интересно! Прям таки матчинг кортежей:
where (name, price, availability) = ('Наволочка', 400.00, FALSE)
npocmu
10.02.2017 14:26+1Касательно
EXCEPT:
К сожалению нет стандартного ПРОСТОГО способа найти разницу двух таблиц одинаковой структурыTable1иTable2.
Приходится писать или
( SELECT * FROM Table1 EXCEPT SELECT * FROM Table2 ) UNION ALL ( SELECT * FROM Table2 EXCEPT SELECT * FROM Table1 )
или заморачиваться с
FULL JOIN.QuickJoey
10.02.2017 14:54Мне кажется, FULL JOIN как-то лаконичнее, и прозрачней в записи.
npocmu
10.02.2017 15:03Серьезно? Ну напишите
FULL JOINдля таблицы хотя бы с 10-ю NULLable полями (f0,....,f9), выдающий аналогичный EXCEPTу результат. А финт с EXCEPT прекрасно работает на любом количестве полей и без утомительных проверок NULL/не NULL.QuickJoey
10.02.2017 15:08Согласен, но как правило я не ищу разницу по _всем_ полям, одно, два.
npocmu
10.02.2017 15:33+1В обычном коде у меня
EXCEPTвообще ни разу не встречается. Но как без него жить при отладке??? Например, поменяли процедуру загрузки данных. Надо убедиться, что все корректно. Копируем существующую таблицу под другим именем, по новой грузим данные и сравниваем через вышеупомянутый прием, что процедура работает правильно, данные совпадают.
Или есть два сервера/две БД, надо убедиться, что данные совпадают:
SELECT * FROM serverA.DBname.dbo.TableX EXCEPT SELECT * FROM serverB.DBname.dbo.TableX
на mssql прекрасно работает и выручает постоянно...
QuickJoey
10.02.2017 15:45Да, но это опять же зависит от задач, ну не мигрирую я постоянно данные, что поделать)
1moreworld
11.02.2017 00:48+1Спасибо, в целом интересно. Вспомнил вещи, которыми давно не пользовался. Но алиасы столбцов и подзапросы не кажутся мне редкими в работе с sql. Хотя каждый по разному использует…
zenkz
11.02.2017 02:01Спасибо. Статья интересная. Про ALL, ANY и сравнение блоков данных — не знал.
Пара советов:
— старайтесь не использовать русские алиасы колонок и вообще пишите поменьше русских комментариев. Может случиться так, что проект вырастет и прийдут иностранные разработчики… Самый абсурдный пример который я видел — это были русские теги в XML и соответственно русские названия переменных в C# коде, сгенерированном на её основе.
— некоторые из описанных вами вещей являются «вредными» и поэтому не известны большинству разработчиков. К примеру то же сравнение блоков данных будет сложнее модифицировать, чем обычную AND/OR последовательность. То же самое с ALL / ANY — чаще всего IN или JOIN будет удобнее.
VaalKIA
11.02.2017 05:35+2Алиасы таблиц в SQL это базовое понятие, тот кто их не использует, не работал с SQL, а баловался.
Любое добавление колонок может привести к краху корректного запроса, потому что имена колонок вдруг стали совпадать, с каким-нибудь «именем на лету» в подзапросе. Поэтому правильный стиль: обращение к полям только через алиасы таблиц.
Лучше бы рассказали про left join и одно и то же условие в блоке on или where с различным результатом, фактически же у вас «вершки для чайников»
sufferingluck
12.02.2017 14:11Свежий взгляд, и даже напоминание о необходимости иногда взглянуть не замыленным взглядом на рутинные действия — это замечательно. Все равно что после од о Гавриле Никифора Ляписа-Трубецкого обратиться к Пушкину… Есть место творчеству и в SQL ;) Спасибо за свежесть подачи, важен сам импульс!
Pusk1
13.02.2017 17:08Можно ещё про Exists добавить. Многие коллеги предпочитают in или not in, что часто приводит к деградации производительности.
С двойными кавычками надо быть поосторожнее. На Oracle при использовании их в DDL можно позже огрести расзницу в регистрах.
Fragster
Последний пример напоминает EXIST и NOT EXISTS. Особенно интересно их использование с корреляцией, т.е. подзапросы с использованием значений из внешней таблицы как параметров.
reforms
Согласен с Вами, очень напоминает. Про SQL вообще можно сказать, что один и тот же результат можно получить разными способами.
DreamChild
Небольшое замечание по оформлению: в android приложении совершенно невозможно читать запросы. Почти весь их код написан в одну длинную строку без переносов. В итоге приходится очень много прокручивать по горизонтали, это чертовски неудобно