Вводная


Когда часто сталкиваешься с какой-либо технологией, языком программирования, стандартом, формируется некая картина их возможностей, границы, в которых они используются. Так может продолжаться достаточно долго, пока на глаза не попадаются примеры, которые расширяют затвердевшие горизонты знания. Сегодня, я хотел бы рассказать о таких примерах и продемонстировать их для языка 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.
Поделиться с друзьями
-->

Комментарии (63)


  1. Fragster
    10.02.2017 10:53

    Последний пример напоминает EXIST и NOT EXISTS. Особенно интересно их использование с корреляцией, т.е. подзапросы с использованием значений из внешней таблицы как параметров.


    1. reforms
      10.02.2017 12:10

      Согласен с Вами, очень напоминает. Про SQL вообще можно сказать, что один и тот же результат можно получить разными способами.


      1. DreamChild
        10.02.2017 18:28

        Небольшое замечание по оформлению: в android приложении совершенно невозможно читать запросы. Почти весь их код написан в одну длинную строку без переносов. В итоге приходится очень много прокручивать по горизонтали, это чертовски неудобно


  1. streetflush
    10.02.2017 11:17
    +4

    Зануда mod:on
    Хорошо бы сказать что входит в стандарт SQL, а что в синтаксис конкретной СУБД.
    Раз статья рассчитана на новичков, хотелось бы видеть «верное» использование той или иной конструкции и для чего она задумана. (Я вот так и не проникся литералами d t ts)
    Зануда mod:off

    Не понял про двойные кавычки, чем они лучше чем одинарные?
    И стоит указать


    1. reforms
      10.02.2017 11:49

      За замечание к статье спасибо.

      Отвечаю на вопросы:

      Хорошо бы сказать что входит в стандарт SQL, а что в синтаксис конкретной СУБД

      Когда я впервые познакомился с BNF Grammars for SQL-92, я не нашел там возможности указывать двойные кавычки (N1) и ничего про {ts/d/t} (N4), все остальное и из него. Однако: в последней версии (может быть редакции?) использование двойных кавычек там есть. Про ts/d/t встречал часто в литературе, форумах — что это стандарт.

      Не понял про двойные кавычки, чем они лучше, чем одинарные?

      В первом случае — это алиас, во втором данные.
      Пример 1
      SELECT name "Алиас колонки" FROM goods WHERE id = 1

      Алиас колонки
      Тапочки


      1. Melkij
        10.02.2017 12:07
        +1

        name 'Это данные' — это синтаксис указания константы указанного типа данных. Да, в 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'
        


        1. reforms
          10.02.2017 12:21

          name 'Это данные' — это синтаксис указания константы указанного типа данных

          Так и есть, поэтому я собственно и написал, про одинарные кавычки, что результат будет совершенно иной. И конкретно в моем случае — он такой.

          Так же литералы timestamp в мануале и обозначаются как...

          Да, Вы опять правы. Но мне пример с timestamp кажется менее интересным, чем с {ts ...}


      1. 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
        



        1. npocmu
          10.02.2017 14:15
          +1

          Двойные кавычки и квадратные скобки это стандартный способ для ограничения идентификаторов (а точнее, квадратные скобки стандартны только для mssql). Одинарные же кавычки в алиасе — видимо работают только там, по некому недоразумению.
          Сравните:


          CREATE TABLE "Тестовая таблица" (id INT)
          DROP TABLE "Тестовая таблица" 

          и


          CREATE TABLE 'Тестовая таблица' (id INT)
          DROP TABLE 'Тестовая таблица' 


        1. d-stream
          10.02.2017 22:30

          select
          [id] = t.id,
          t.code as [код],
          t.descr «описание»
          t.option as option
          from t

          -)


          1. streetflush
            15.02.2017 16:03

            Incorrect syntax near '«'.


            1. d-stream
              15.02.2017 16:48

              Ну логично — редактор заменил двойные кавычки на двойные угловые -)
              Забыл тэгами «код» обернуть

              select 
                   [id] = t.id,
                   t.code as [код],
                   t.descr "описание"
                   t.option as option
               from t
              
              


              1. streetflush
                16.02.2017 11:10

                Тогда неясен смысл вашего предыдущего ответа…


                1. d-stream
                  16.02.2017 11:52

                  Я просто привел вариант вавилонского смешения в одном запросе…

                  На мой вкус наиболее комфортен вариант

                  id =… или [id] =


  1. 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 не добраться.


    1. 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.


      1. 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

        Да, действительно.


        1. reforms
          10.02.2017 14:01

          Угу, пост так озаглавлен.

          Согласен, сам себе противоречу. Но прежде чем запоститься, опробовал перечисленные кейсы на своей фокус группе. К большому удивлению подзапрос в секции FROM оказался мало известным фактом.

          Кое-что пропустил из предыдущего комментария:
          EXCEPT и INTERSECT — постгресовые расширения стандарта, что надо учитывать, говоря про sql.

          С чего Вы это взяли? Все в той же доке EXCEPT/EXCEPT ALL и INTERSECT/INTERSECT ALL присутствуют. Да, например, в ORACLE есть MINUS vs EXCEPT.
          И чтобы больше не халиварить, спрашиваю для себя — 'Вы все указанные примеры знали до прочтения статьи?'
          Спасибо


          1. 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, а часть драйвера и именно драйвера заменяет эту запись на понятную СУБД.


            1. reforms
              10.02.2017 15:13

              Спасибо за честный ответ.

              тестовый PostgreSQL 9.5.5 с таким синтаксисом не согласен и считает недопустимым

              Да, а это уже мой серьезный косяк. Я уже как nth лет использую фронт для работы с СУБД в eclipse и пример c ts примелькался. С Вашего позволения внесу корректив в статью


          1. GlukKazan
            10.02.2017 14:40

            Честно, говоря, примеры действительно какие-то банальные (не в обиду вам будет сказано).
            Но про подзапросы в from новички действительно часто забывают.
            Так что, польза от статьи, вероятно есть.


            1. 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.


              1. GlukKazan
                10.02.2017 22:49

                Это вы мне объясняете? Я говорил про новичков.
                Как они про это забывают — мне непостижимо, но факт остаётся фактом.


            1. nikolayv81
              11.02.2017 21:37

              Подзапросы в from лучше бы и не знали, когда вынесено в with гораздо проще понять что человек хотел получить, особенно когда запрос на пару страниц в pl/sql.


              1. GlukKazan
                12.02.2017 14:56

                with (в Oracle) появился несколько позже


                1. nikolayv81
                  13.02.2017 16:13

                  Думаю сейчас уже "слишком много лет прошло" да и это особенности конкретной СУБД


                  1. GlukKazan
                    13.02.2017 16:18

                    для энтерпрайза никогда не бывает «слишком много»


                    1. nikolayv81
                      13.02.2017 21:01

                      25 марта 2003 года — 9i Release 2
                      Я так понимаю что не так много людей реально работает с 32 битной версией oracle, и думаю те кто работает прекрасно понимают что мой комментарий про "лучше бы пользовались with-ами" относился не к ним а к тем кто пришёл в работу с базами данных сильно позже. В принципе ничто не мешает использовать оба способа записи, просто в тот же план запроса в oracle легче ассоциируется с записью with-ами. Также можно пользоваться "плюсами", просто в при "больших простынях" неудобно, на мой взгляд.


                      1. GlukKazan
                        14.02.2017 09:59

                        Вам знаком термин legacy?
                        Можно не отвечать, вопрос чисто риторический.


              1. torbasow
                13.02.2017 13:47

                WITH материализуется, это не всегда нужно.


                1. nikolayv81
                  13.02.2017 16:15

                  Я так понимаю от СУБД зависит, в Oracle наоборот зачастую hint materialized использовать приходится.


    1. LastDragon
      11.02.2017 08:47
      +1

      Можете проверить, select not 1; будет ошибкой, not применяется к выражению (или полю) строго булева типа

      В MySQL это просто логический оператор, поэтому данный запрос вернет 0.


      1. Melkij
        11.02.2017 10:35
        +1

        Лучше начать с того, что mysql не умеет булевый тип вообще и, к тому же, отличается довольно мягкой системой неявного приведения типов.
        Но да, не указал, что этот момент именно про postgresql. В mysql допустим, в других СУБД — не знаю.


  1. 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.


    1. reforms
      10.02.2017 12:36

      Спасибо, очень здравый комментарий.


    1. UA3MQJ
      10.02.2017 14:18

      Да, я тоже обычно или IN или NOT IN применяю.


      1. darthunix
        11.02.2017 14:13

        Мой опыт PostgreSQL подсказывает никогда не использовать конструкцию NOT IN, это самый медленный вариант из возможных — все жду, когда планировщик PostgreSQL научится с ним нормально работать. Обычно исключение строк реализуется или через EXCEPT, или через LEFT JOIN… WHERE id IS NULL. Но EXCEPT из этих вариантов самый производительный.


    1. GlukKazan
      10.02.2017 14:45

      Кстати, псевдонимы в PostgreSql без as тоже не всегда работают. У меня лично были проблемы с псевдонимом «name» и «value». Разумеется, не специально получилось, а при перетаскивании запросов с Oracle. Так что as лучше ставить.


    1. 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

      Всё это, чтобы повысить читабельность выборок с большим количеством присоединённых таблиц и/или условий выборки.


      1. 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 мне совершенно не понятен. Поля располагаю так, что бы было удобно изучать результат. Например, код в таблице фактов и его описание из справочника будут идти подряд, несмотря на то, что они из разных таблиц.


        1. QuickJoey
          10.02.2017 15:23

          Так пишут многие, поэтому я и спрашиваю «как выглядит». Но я честно, не вижу в этом удобства, все таблицы перемежаются ключевыми словами, и тяжело вычленить алиасы, они все на разных уровнях? Результаты можно поизучать с лёгким неудобством, во имя удобства последующего чтения (все поля таблицы примерно в одном месте).


          1. npocmu
            10.02.2017 15:49
            +2

            ну не знаю… Ради редкого изучения кода жертвовать удобством работы с данными… У меня были случаи, когда я менял порядок полей в таблице/виде, только потому, что их неудобно было просматривать (важные поля находились не рядом или вылезали за границы экрана).


            Хороший инструмент просмотра БД спасает иногда, но не всегда он есть под рукой.


            А про алиасы… Извините, но однобуквенные алиасы это ужас. У меня они осмысленные и всегда одинаковые для таблиц с одинаковыми сущностями (за исключением суффикса, в случае множественного присоединения). Поэтому и смысла вычленения алиасов глазами нет никакого.


            1. QuickJoey
              10.02.2017 15:56

              Видимо я код изучаю сильно чаще, чем данные, мне на данные плевать) да и смотрю я их в приложении, где поля уже построены так, как требует потребитель. А вот код наоборот, ускорить выполнение, понять почему криво работает фильтр и т. д.
              Про осмысленные алиасы – хорошо, когда таблиц немного, или они все имеют разные корни, а когд много похожих названий, сокращения становятся всё длиннее и неудобнее, не только читать, но и писать.


              1. 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())

                Вот это реально помогает понимать код (а не буквы в столбик).


                1. 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 ;
                  


                  ссылка.


                  1. npocmu
                    10.02.2017 19:08

                    Разница в том, что алиас не бессмысленная буковка i или a, а говорящее название:
                    Invoices — все счета,
                    Invoice — один счет
                    InvoicesOld — некие старые счета
                    И я, смотря на любое место многостраничного запроса, могу сразу понять о чем речь, без необходимости прыгать к столбику алиасов в предложении FROM.


                    В-третьих, alias (псевдоним) на то и псевдоним, чтобы использовать его на своё усмотрение для повышения читаемости кода.

                    Хотя бы один пример повышения ЧИТАЕМОСТИ можно? Ваша метода способствует лишь скорости набора кода. А читаемость вы пытаетесь повысить экзотическим форматированием кода.


                    Ссылка на technet лишь подтверждает мое мнение о распространенном заблуждении.


      1. 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;

        Особенности:


        1. SQL в Oracle — регистронезависимый язык, поэтому все ключевые слова, названия таблиц, колонок и переменных пишутся в нижнем регистре. Так меньше визуального шума и «крика». Так же по этой причине имена всех переменных, колонок и таблиц в змеином_стиле.
        2. Локальные переменные предваряются префиксом v_, параметры функций — префиксом p_, константы — префиксом c_.
        3. Таблица обзываются максимально короткими псевдонимами, однако отражающими их смысловую суть. Как правило, в качестве псевдонима выступает первая буква названия таблицы.
        4. Ключевое слово as при объявлении псевдонимов не используется, оно только отвлекает.
        5. Фразы from и join пишутся каждая на своей строке, без отступа. Кроме того, условия после on во фразе join заключаются в скобки. Это особенно удобно, если условия становятся длинными и их приходится разбивать на несколько строк — сразу видно границы соединения.
        6. В условиях on сначала идёт поле присоединяемой таблицы, а потом — той, к которой присоединяем.
        7. Список столбцов в select, group by и order by-списках располагается так, чтобы он занимал как можно меньше места, при этом вмещался на экран по горизонтали, а поля располагались в смысловом порядке. Порядок полей во фразе group by совпадает с таковым во фразе select, кроме того, все они располагаются до полей, по которым ведётся агрегирование.
        8. Условия во фразе where располагаются по одному на строку.
        9. Ключевые слова and и or выравниваются по правому краю слова where. Таким образом все условия начинаются с одной колонки и их значительно легче читать.
        10. Если встречается несколько блоков, соединённых через or, ключевое слово or раполагается на отдельной строке и выравнивается по левому краю.
        11. Нет лишним скобкам! Незачем заключать два условия, соединённых по or, если внутри них есть условия and. Приоритет and выше or, поэтому лишние скобки не нужны, они только затрудняют чтение.


        1. GlukKazan
          10.02.2017 23:01

          1. Идентификаторы в двойных кавычках регистрозависимы (без кавычек преобразует в верхний регистра, в отличии от Постгресса, в этом месте мы огребли)
          2. Полезно чтобы не путать со столбцами
          3. Спорно, про псевдоним очень спорно
          4. Ага, мы тоже так делали, после переезда на Постгресс огребли (у него без as не везде работает)
          5. Про on полезно
          6. Большого смысла не вижу
          7. Почему бы нет (главное чтобы звезды в запросах не использовали)
          8. Большого смысла не вижу
          9. Субъективно (к тому же автоформатеры норовят испортить всю малину)
          10. См. предыдущий пункт
          11. Вредный совет

          Прошу не рассматривать мой комментарий как догму (равно как и советы выше).


      1. nikolayv81
        13.02.2017 21:22

        В Oracle


        1. табуляцией отделяю следующий уровень.
        2. списки полей с запятой (причина комментирование через --, часто приходиться комментировать большие куски запросов)
        3. параметры в блок with в подзапрос по возможности.
        4. порядок join-ов в порядке того, как я, а не оптимизатор, вижу выборку, т.к. частенько приходится его переубеждать.
        5. on на следующей строчке после join
        6. Всегда field as name т.к. подсветка в девелопере выделяет as если запрос не очень большой стараюсь as-выравнивать табуляцией.
        7. Всегда пишу inner join и left join выровненные на 2 пробела сдвинутые от from.
        8. Условия стараюсь 1 на строчку, оператор всегда в начале, после on если несколько условий то общую скобку для выравнивания.
          Самое печальное что так и не вышло заставить встроенный форматировщик делать как мне удобно, а как-то раз испытал небольшой шок, когда один из коллег после правки процедуры в большом пакете взял и сделал в нём автоформат...


  1. UA3MQJ
    10.02.2017 14:17
    +1

    Мне в свое время очень помогло чтение документации по MySQL. Там было очень много примеров, из которых я все и начал понимать.

    Спасибо за статью, интересно! Прям таки матчинг кортежей:

    where (name, price, availability) = ('Наволочка', 400.00, FALSE)


    1. reforms
      10.02.2017 15:22

      И Вам спасибо, что читаете.


  1. 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.


    1. QuickJoey
      10.02.2017 14:54

      Мне кажется, FULL JOIN как-то лаконичнее, и прозрачней в записи.


      1. npocmu
        10.02.2017 15:03

        Серьезно? Ну напишите FULL JOIN для таблицы хотя бы с 10-ю NULLable полями (f0,....,f9), выдающий аналогичный EXCEPTу результат. А финт с EXCEPT прекрасно работает на любом количестве полей и без утомительных проверок NULL/не NULL.


        1. QuickJoey
          10.02.2017 15:08

          Согласен, но как правило я не ищу разницу по _всем_ полям, одно, два.


          1. npocmu
            10.02.2017 15:33
            +1

            В обычном коде у меня EXCEPT вообще ни разу не встречается. Но как без него жить при отладке??? Например, поменяли процедуру загрузки данных. Надо убедиться, что все корректно. Копируем существующую таблицу под другим именем, по новой грузим данные и сравниваем через вышеупомянутый прием, что процедура работает правильно, данные совпадают.
            Или есть два сервера/две БД, надо убедиться, что данные совпадают:


               SELECT * FROM serverA.DBname.dbo.TableX
               EXCEPT
               SELECT * FROM serverB.DBname.dbo.TableX

            на mssql прекрасно работает и выручает постоянно...


            1. QuickJoey
              10.02.2017 15:45

              Да, но это опять же зависит от задач, ну не мигрирую я постоянно данные, что поделать)


  1. 1moreworld
    11.02.2017 00:48
    +1

    Спасибо, в целом интересно. Вспомнил вещи, которыми давно не пользовался. Но алиасы столбцов и подзапросы не кажутся мне редкими в работе с sql. Хотя каждый по разному использует…


  1. zenkz
    11.02.2017 02:01

    Спасибо. Статья интересная. Про ALL, ANY и сравнение блоков данных — не знал.

    Пара советов:
    — старайтесь не использовать русские алиасы колонок и вообще пишите поменьше русских комментариев. Может случиться так, что проект вырастет и прийдут иностранные разработчики… Самый абсурдный пример который я видел — это были русские теги в XML и соответственно русские названия переменных в C# коде, сгенерированном на её основе.
    — некоторые из описанных вами вещей являются «вредными» и поэтому не известны большинству разработчиков. К примеру то же сравнение блоков данных будет сложнее модифицировать, чем обычную AND/OR последовательность. То же самое с ALL / ANY — чаще всего IN или JOIN будет удобнее.


  1. VaalKIA
    11.02.2017 05:35
    +2

    Алиасы таблиц в SQL это базовое понятие, тот кто их не использует, не работал с SQL, а баловался.
    Любое добавление колонок может привести к краху корректного запроса, потому что имена колонок вдруг стали совпадать, с каким-нибудь «именем на лету» в подзапросе. Поэтому правильный стиль: обращение к полям только через алиасы таблиц.
    Лучше бы рассказали про left join и одно и то же условие в блоке on или where с различным результатом, фактически же у вас «вершки для чайников»


  1. sufferingluck
    12.02.2017 14:11

    Свежий взгляд, и даже напоминание о необходимости иногда взглянуть не замыленным взглядом на рутинные действия — это замечательно. Все равно что после од о Гавриле Никифора Ляписа-Трубецкого обратиться к Пушкину… Есть место творчеству и в SQL ;) Спасибо за свежесть подачи, важен сам импульс!


  1. Riosan
    13.02.2017 15:11
    +2

    Ты серьезно ), это ж азы. )


  1. Pusk1
    13.02.2017 17:08

    Можно ещё про Exists добавить. Многие коллеги предпочитают in или not in, что часто приводит к деградации производительности.
    С двойными кавычками надо быть поосторожнее. На Oracle при использовании их в DDL можно позже огрести расзницу в регистрах.