19 июля 2016 в 12:09

Как думать на SQL? перевод tutorial

Надо “SELECT * WHERE a=b FROM c” или “SELECT WHERE a=b FROM c ON *” ?

Если вы похожи на меня, то согласитесь: SQL — это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.


А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:


SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
  FROM books
  WHERE stock>(SELECT avg(stock)
    FROM books))
GROUP BY members.firstname, members.lastname;

Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.


Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.


Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.


1. Три волшебных слова


В SQL много ключевых слов, но SELECT, FROM и WHERE присутствуют практически в каждом запросе. Чуть позже вы поймете, что эти три слова представляют собой самые фундаментальные аспекты построения запросов к базе, а другие, более сложные запросы, являются всего лишь надстройками над ними.


2. Наша база


Давайте взглянем на базу данных, которую мы будем использовать в качестве примера в этой статье:





(ссылка на таблицу)


У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.


  • В таблице "books" хранится информация о заголовке, авторе, дате публикации и наличии книги. Все просто.
  • В таблице “members” — имена и фамилии всех записавшихся в библиотеку людей.
  • В таблице “borrowings” хранится информация о взятых из библиотеки книгах. Колонка bookid относится к идентификатору взятой книги в таблице “books”, а колонка memberid относится к соответствующему человеку из таблицы “members”. У нас также есть дата выдачи и дата, когда книгу нужно вернуть.

3. Простой запрос


Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”


Запрос будет таким:


SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';

А результат таким:


id title
2 The Lost Symbol
4 Inferno

Довольно просто. Давайте разберем запрос чтобы понять, что происходит.


3.1 FROM — откуда берем данные


Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.


FROM указывает на таблицу, по которой нужно делать запрос. Это может быть уже существующая таблица (как в примере выше), или таблица, создаваемая на лету через соединения или подзапросы.


3.2 WHERE — какие данные показываем


WHERE просто-напросто ведет себя как фильтр строк, которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author — это “Dan Brown”.


3.3 SELECT — как показываем данные


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


Весь запрос можно визуализировать с помощью простой диаграммы:



4. Соединения (джойны)


Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:


SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author='Dan Brown';

Результат:


Title Return Date
The Lost Symbol 2016-03-23 00:00:00
Inferno 2016-04-13 00:00:00
The Lost Symbol 2016-04-19 00:00:00

По большей части запрос похож на предыдущий за исключением секции FROM. Это означает, что мы запрашиваем данные из другой таблицы. Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице, которая создалась соединением этих двух таблиц.


borrowings JOIN books ON borrowings.bookid=books.bookid — это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет:



А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.


Давайте попробуем чуть более сложное соединение с двумя таблицами.


Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.


На этот раз давайте пойдем снизу вверх:


Шаг Step 1 — откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:


borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid

Результат соединения можно увидеть по ссылке.


Шаг 2 — какие данные показываем? Нас интересуют только те данные, где автор книги — “Dan Brown”


WHERE books.author='Dan Brown'

Шаг 3 — как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:


SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:


SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';

Что даст нам:


First Name Last Name
Mike Willis
Ellen Horton
Ellen Horton

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.


5. Агрегирование


Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну. При этом, во время агрегирования для разных колонок используется разная логика.


Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:


SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;

Что даст нам нужный результат:


First Name Last Name Number of books borrowed
Mike Willis 1
Ellen Horton 2

Почти все агрегации идут вместе с выражением GROUP BY. Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY. В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count, которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе.


Каждая строка в результате представляет собой результат агрегирования каждой группы.



Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY, или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT'ом, то непонятно, какие из возможных значений нужно брать.


В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:


SELECT author, sum(stock)
FROM books
GROUP BY author;

Результат:


author sum
Robin Sharma 4
Dan Brown 6
John Green 3
Amish Tripathi 2

Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.


6. Подзапросы



Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.


6.1 Двумерная таблица


Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов — использовать подзапросы:


SELECT *
FROM (
  SELECT author, sum(stock)
  FROM books
  GROUP BY author
) AS results
WHERE author='Robin Sharma';

Результат:


author sum
Robin Sharma 4

6.2 Одномерный массив


Запросы, которые возвращают несколько строк одной колонки, можно использовать не только как двумерные таблицы, но и как массивы.


Допустим, мы хотим узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех. Разобьем это на два шага:


1. Получаем список авторов с количеством книг больше 3. Дополняя наш прошлый пример:


SELECT author
FROM (
 SELECT author, sum(stock)
 FROM books
 GROUP BY author
) AS results
WHERE sum > 3;

Результат:


author
Robin Sharma
Dan Brown

Можно записать как: ['Robin Sharma', 'Dan Brown']


2. Теперь используем этот результат в новом запросе:


SELECT title, bookid
FROM books
WHERE author IN (
  SELECT author
  FROM (
    SELECT author, sum(stock)
    FROM books
    GROUP BY author
  ) AS results
  WHERE sum > 3);

Результат:


title bookid
The Lost Symbol 2
Who Will Cry When You Die? 3
Inferno 4

Это то же самое, что:


SELECT title, bookid
FROM books
WHERE author IN ('Robin Sharma', 'Dan Brown');

6.3 Отдельные значения


Бывают запросы, результатом которых являются всего одна строка и одна колонка. К ним можно относиться как к константным значениям, и их можно использовать везде, где используются значения, например, в операторах сравнения. Их также можно использовать в качестве двумерных таблиц или массивов, состоящих из одного элемента.


Давайте, к примеру, получим информацию о всех книгах, количество которых в библиотеке превышает среднее значение в данный момент.


Среднее количество можно получить таким образом:


select avg(stock) from books;

Что дает нам:


avg
3.000

И это можно использовать в качестве скалярной величины 3.


Теперь, наконец, можно написать весь запрос:


SELECT *
FROM books
WHERE stock>(SELECT avg(stock) FROM books);

Это то же самое, что:


SELECT *
FROM books
WHERE stock>3.000

И результат:


bookid title author published stock
3 Who Will Cry When You Die? Robin Sharma 2006-06-15 00:00:00 4

7. Операции записи


Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.


7.1 Update


Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT'ом, мы задаем знаения SET'ом.


Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:


UPDATE books
SET stock=0
WHERE author='Dan Brown';

WHERE делает то же самое, что раньше: выбирает строки. Вместо SELECT, который использовался при чтении, мы теперь используем SET. Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках.


img


7.2 Delete


Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE, блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:


DELETE FROM books
WHERE author='Dan Brown';

7.3 Insert


Пожалуй, единственное, что отличается от других типов запросов, это INSERT. Формат такой:


INSERT INTO x
  (a,b,c)
VALUES
  (x, y, z);

Где a, b, c это названия колонок, а x, y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.


Взглянем на конкретный пример. Вот запрос с INSERT, который заполняет всю таблицу "books":


INSERT INTO books
  (bookid,title,author,published,stock)
VALUES
  (1,'Scion of Ikshvaku','Amish Tripathi','06-22-2015',2),
  (2,'The Lost Symbol','Dan Brown','07-22-2010',3),
  (3,'Who Will Cry When You Die?','Robin Sharma','06-15-2006',4),
  (4,'Inferno','Dan Brown','05-05-2014',3),
  (5,'The Fault in our Stars','John Green','01-03-2015',3);

8. Проверка


Мы подошли к концу, предлагаю небольшой тест. Посмотрите на тот запрос в самом начале статьи. Можете разобраться в нем? Попробуйте разбить его на секции SELECT, FROM, WHERE, GROUP BY, и рассмотреть отдельные компоненты подзапросов.


Вот он в более удобном для чтения виде:


SELECT members.firstname || ' ' || members.lastname AS "Full Name"

FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid

WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>  (SELECT avg(stock) FROM books)  )

GROUP BY members.firstname, members.lastname;

Этот запрос выводит список людей, которые взяли из библиотеки книгу, у которой общее количество выше среднего значения.


Результат:


Full Name
Lida Tyler

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

Автор оригинала: Soham Chetan Kamani
Рахим Давлеткалиев @freetonik
карма
572,5
рейтинг 4,5
со-основатель Hexlet.io
Похожие публикации
Самое читаемое Разработка

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

  • +10
    Статья для новичков, наверное, не плохая. Однако, большим опытом здесь не пахнет. Сразу в глаза бросается использование
    members.firstname, members.lastname
    • –1
      Я сам так пишу, если нет необходимости в алиасе.
      • +7
        Необходимость в алиасе есть практически всегда: не сразу так потом понадобится. Опять же алиас упрощает использование код-ассиста.
        • 0
          Зачем нужны алиасы? В смысле, в чём именно необходимость?
          • 0
            Необходимость возникает в тот момент, когда запрос надо доработать:
          • +1
            Необходимость возникает в тот момент, когда запрос надо доработать.
            Варианты:
            1) надо добавить еще одно поле и я не помню, как оно точно называется. Набираю короткий алиас, и список полей таблицы у меня перед глазами. Нет алиаса — я вынужден идти смотреть описание таблицы.
            2) надо приджойнить таблицу в которой есть совпадающее поле — тут же становится необходимо завести алиас
            3) надо просто понять, что делает запрос, а в нем идет соединение таблиц без алиасов и не ясно, какое поле к какой таблице относится.
            В общем я считаю, что обязательное использование алиасов — хороший стиль.
          • +2
            Я практически всегда юзаю алиасы по двум причинам:
            • Таблицы, к сожалению, принято называть во множественном числе. Но students.surname и books.title — как-то не комильфо. Т.е. названию таблицы во множественном делаю алиас в единственном числе (или вообще аббревиатурой), чтобы множественное число не задевало моё чувство прекрасного.
            • На практике названия таблиц часто длинные.


            А так, да, если таблица не джоинится сама с собой (и в запросе к таблице нету подзапросов к той же таблице), то критической необходимости использования алиасов, вроде бы, нет.

            P.S.: К этому следует добавить, что я всегда префиксирую название поля названием или алиасом таблицы. Потому что если сейчас название поля уникальное, то не факт, что оно таким останется. Ну и плюс сразу ясно откуда поле.
          • 0

            Например, обращаться к той же самой таблице в подзапросе. Простейший пример, у нас есть таблица-журнал операций:


            create table entity(
                entity_id number(10, 0) not null
                , unit_id number(10, 0) not null
                , some_state varchar2(100)
                -- ...прочие полезные поля
            
                , constraint primary key(op_id)
            );
            
            comment on table entity is 'Журнал состояний некоторой сущности';
            comment on column entity.entity_id is 'Уникальный идентификатор состояния сущности';
            comment on column entity.unit_id is 'Уникальный идентификатор самой сущности';
            comment on column entity.some_state is 'Некоторое значимое состояние';

            и мы хотим выбрать самую последнюю запись по истории:


            select *
            from entity e
            where not exists(
                select 1
                from entity
                where unit_id = e.unit_id
                  -- Если же мы хотим выбрать не просто последнюю запись в истории,
                  -- а последнюю запись с определённым состоянием сущности,
                  -- добавляем подобное условие
                  --and some_state = e.some_state
                  and entity_id > e.entity_id
              )
            order by entity_id
            ;

            Без псевдонима это было бы сделать невозможно, поскольку таблица.и в основном запросе, и в подзапросе одна и та же.

            • +1

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


              1. выражение order by entity_id — поскольку в выборке участвует всего одна таблица, псевдонимы при перечислении колонок указывать нет необходимости. Если бы в запросе участвовало бы две таблицы (соединённые через join, к примеру), то имя каждой колонки в обязательном порядке указывалось бы вместе с псевдонимом таблицы;
              2. поля из таблицы подзапроса — and entity_id > e.entity_id — аналогично, в подзапросе таблица всего одна, нет необходимости указывать псевдоним, движок СУБД сам определит, к какой таблице относятся поля — к самой ближайшей.

              Так запросы становится значительно легче воспринимать.

  • +22
    Неприятная мелочь: я бы обязательно вывел вложенный SELECT на новую строку и уровень индентации (кроме однострочных случаев). Т.е. вместо:

    SELECT author
    FROM (SELECT author, sum(stock)
        FROM books
        GROUP BY author) AS results
    WHERE sum > 3;
    


    написал бы:

    SELECT author
    FROM (
        SELECT author, sum(stock)
        FROM books
        GROUP BY author
    ) AS results
    WHERE sum > 3;
    


    В тыщу раз легче читать, имхо, особенно новичкам.
    • +1
      Вы правы. Поправил пост.
    • +3
      А зачем в данном примере вложенный запрос, можно же:

      SELECT author
      FROM books
      GROUP BY author
      HAVING sum(stock) > 3;
  • +6

    с SQL надо аккуратнее, а то потом подсаживаешься и начинается что то подобное:


    INSERT INTO stats_search_engine_hot
    SELECT 
        CURRENT_DATE as date, 
        visitors.user_id,
        visitors.host_id,
        visitors.search_engine_id,
        visitors.visitorquant,
        IFNULL(dialogs.dialogquant, 0) as dialogquant
    FROM (
        SELECT 
            sess.user_id, 
            sess.host_id,
            sess.search_engine_id,
            count(sess.id) as visitorquant
        FROM (
            SELECT s.id FROM session as s WHERE s.date = current_date
            UNION
            SELECT sh.id FROM session_hot as sh
        ) as subquery
        INNER JOIN session as sess ON sess.id = subquery.id
        WHERE sess.search_engine_id > 0
        GROUP BY 
            sess.user_id, 
            sess.host_id, 
            sess.search_engine_id
    ) as visitors
    LEFT JOIN (
        SELECT 
            s.user_id, 
            s.host_id, 
            s.search_engine_id, 
            count(s.id) as dialogquant
        FROM(
            SELECT
                m.session_id, 
                min(m.id) as mid
            FROM message as m
            WHERE m.date >= DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00")
            GROUP BY 
                DATE(m.date), 
                m.session_id
        ) as a
        LEFT JOIN message as b ON b.id = a.mid 
        LEFT JOIN session as s ON s.id = a.session_id
        WHERE b.direct = 0 
        AND s.search_engine_id > 0
        GROUP BY 
            s.user_id, 
            s.host_id, 
            s.search_engine_id
    ) as dialogs 
    ON visitors.user_id = dialogs.user_id 
    AND visitors.host_id = dialogs.host_id 
    AND visitors.search_engine_id = dialogs.search_engine_id
    • +2

      Это еще нормально выглядит. Хотя бы не в одну строчку, как тот запрос, который я отлаживал на прошлой неделе...

    • +13
      • +1
        Надеюсь, сгенерённое?
        • +1
          Или бенчмарк для СУБД :)
          • 0

            Нет руками писано)

      • 0
        Боженьки мои!
      • +1
        это уже ASCII-графика какая-то 0_о
    • 0
      По-моему тот кто написал это не понял структуры таблиц и условий:

      visitors легко упростить до
      SELECT
      sess.user_id,
      sess.host_id,
      sess.search_engine_id,
      count(sess.id) as visitorquant
      FROM session as sess
      WHERE sess.search_engine_id > 0
      AND (s.date = current_date OR sess.id IN (SELECT sh.id FROM session_hot as sh))
      GROUP BY
      sess.user_id,
      sess.host_id,
      sess.search_engine_id

      dialogs можно заинлайнить тогда там уйдет куча группировок, а min(m.id) и последующий джойн заменить на TOP 1.
      Так же бросается в глаза DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00") который вероятнее всего можно заменить на уже имеющийся CURRENT_DATE
    • 0
      Это вы еще запросы на 1С в конфигурации «Зарплата и управление персоналом».
      Есть запросы на тысячу с лишним строк!!!
      • 0
        Отличные запросы. Особенно здорово их дебажить часами
  • +8
    SELECT members.firstname || ' ' || members.lastname
    AS "Full Name"
    FROM borrowings
    INNER JOIN members
    ON members.memberid=borrowings.memberid
    INNER JOIN books
    ON books.bookid=borrowings.bookid
    WHERE borrowings.bookid IN (SELECT bookid
      FROM books
      WHERE stock>(SELECT avg(stock)
        FROM books))
    GROUP BY members.firstname, members.lastname;


    SELECT 
        members.firstname || ' ' || members.lastname AS "Full Name"
    FROM 
        borrowings, members, books
    WHERE 
        members.memberid=borrowings.memberid AND 
        books.bookid=borrowings.bookid AND 
        books.stock  > (SELECT avg(stock) FROM books)
    GROUP BY 
        members.firstname, 
        members.lastname;
    
    • +4
      Нужно осознать, что SQL — это язык описания запроса, а не язык «почти естественного» общения человека с компьютером.
      Все те же претензии «на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить» — справедливы для того же Си или любого другого языка. Английские термины в качестве ключевых слов — облегчают их мнемоническое запоминание, но не более того. Они не являются эквивалентом конструкций естественного языка.
    • –1
      FROM borrowings, members, books
      Это ужасно. Настолько же ужасно, как
      FROM borrowings
      INNER JOIN members
      INNER JOIN books
      ON members.memberid=borrowings.memberid
      ON books.bookid=borrowings.bookid
      (а MSSQL такое позволяет).
      • +1
        В чём суть «ужасности»? Список источников в своей секции, список условий — в своей.
        Так же как по секциям расположены описание полей на выходе, сортировка, группировка.
        • 0
          Непонятно как об'единены эти три таблицы меж собой — заставляет держать в уме разрозненные таблицы и потом дорисовывать меж ними связи (хорошо что запрос короткий и всё видно). Ну и такая форма подразумевает что там скорее всего inner join и тем печальнее вдруг обнаружить что там left или right join.
          Удивительно что мой пример у вас вызвал отторжение, хотя по структуре он точно такой же, как и первоначальный.
          • +2
            Так все три одинаковы. Разная сложность чтения. Все три источника в любом случае нужно держать в уме, а визуально искать легче, когда искать нужно в одном месте, а не разбросанные по запросу. Перечисленные через запятую они «собираются» — «одним взглядом», растянутые на несколько строчек — пока найдёшь одно, можешь уже забыть другое (особенно в сложном запросе).
            В монстро-запросе будет другая ситуация. Если планировщик базы хорошо оптимизирует подобное — возможно имеет смысл разбивать сложный запрос на более простые и оформлять их в виде функций или WITH выражений, хотя это не всегда полезно.
            Я не претендую на истину в последней инстанции и всегда можно найти обратный пример. Просто привёл пример для сравнения, как можно упростить простой запрос и сделать его легко читаемым для человека. Фанатизм же до добра не доведёт.
            • +1
              Вот именно что сложность разная. У меня слишком маленький об'ём ОЗУ для сложных запросов. В вашем случае я вынужден целиком (целиком — это имя + об'ём) держать в уме ворох таблиц и я даже не догадываюсь что с ними собираются делать и каков масштаб беды (единственное в чём уверен — он должен уменьшиться), пока не найду where. Да, я видел список результирующих колонок, но я пока даже близко не предполагаю откуда они и какую смысловую нагрузку несут. Найдя where, я вынужден сортировать куски условий (а они ещё будут делиться на условия соединения и фильтрации) меж этими таблицами попарно и выстраивать связи между ними (хорошо если разработчик эти куски написал в том же порядке, что и таблицы в from, тогда я просто буду по одной таблице к общей куче пристыковывать, а не держать в уме кучу пар, которые потом будут об'единяться в бОльшие группы) попутно фильтруя весь об'ём соединения.
              В моём же случае я сразу вижу как две таблицы об'единяются (from и первый join), осознаю их смысловую нагрузку, лишнее я уже отбросил и предполагаю об'ём результата. Если в join'ах ещё есть таблицы, то к результату предыдущего шага добавляю ещё одну таблицу и проделываю то же самое дальше.
      • +1
        То, что ужасно
        FROM borrowings
        INNER JOIN members
        INNER JOIN books
        ON members.memberid=borrowings.memberid
        ON books.bookid=borrowings.bookid
        

        не могу не согласиться — здесь просто месиво операторов вместо лаконичного и визуально структурированного перечисления.
      • +1
        В принципе, учитывая структуру наименований, можно также вспомнить и такую форму JOIN'ов:
        FROM borrowings
        INNER JOIN members USING (memberid)
        INNER JOIN books USING (bookid)
        

        Но, имхо, это тоже сложнее читается, чем лаконичная секция FROM и подробная WHERE.
      • 0
        Вы уверены? Oracle такого не позволяет. Но позволяет.

        SELECT *
          FROM books
           INNER JOIN members  
           INNER JOIN borrowings
            ON members.memberid=borrowings.memberid
            ON books.bookid=borrowings.bookid;
        


        И хотя на выходе получается тоже самое, что и в исходном запросе, логика у них разная, это не просто стиль оформления.
        • 0
          Ну я не проверял конкретно этот запрос, конечно, но в том, что все ON можно в конце JOIN'ов писать — уверен.
        • 0
          Хотя не, не уверен. В одном из открытых запросов передвинул ON в конец к другому и там сработало, а сейчас попытался написать похожий на пример запрос и оно не заработало, пришлось колдовать. Планы у запросов одинаковые, так что логику я пока не до конца познал и где такое применять я тоже не знаю.
          • 0

            Логика тут простая:


            SELECT *
            FROM books INNER JOIN (
               members INNER JOIN borrowings ON members.memberid=borrowings.memberid
            ) ON books.bookid=borrowings.bookid;
          • +1
            Все станет совсем понятно если в мой запрос добавить скобочки.

            SELECT *
              FROM books
               INNER JOIN (members  
               INNER JOIN borrowings
                ON members.memberid=borrowings.memberid)
                ON books.bookid=borrowings.bookid;
            


            И это будет тоже самое, что выше. А если чуть переписать запрос и добавить, столь не любимое вами внешнее соединение, то разница станет наглядной.

            SQL> CREATE TABLE borrowings
              2  (
              3     memberid   INTEGER,
              4     bookid     INTEGER
              5  );
            
            Table created.
            
            SQL>
            SQL> CREATE TABLE books (bookid INTEGER);
            
            Table created.
            
            SQL>
            SQL> CREATE TABLE members (memberid INTEGER);
            
            Table created.
            
            SQL>
            SQL> INSERT INTO borrowings
              2         SELECT LEVEL, LEVEL + 1
              3           FROM DUAL
              4     CONNECT BY LEVEL < 10;
            
            9 rows created.
            
            SQL>
            SQL> INSERT INTO books
              2         SELECT LEVEL + 2
              3           FROM DUAL
              4     CONNECT BY LEVEL < 10;
            
            9 rows created.
            
            SQL>
            SQL> INSERT INTO members
              2         SELECT LEVEL + 3
              3           FROM DUAL
              4     CONNECT BY LEVEL < 10;
            
            9 rows created.
            
            SQL>
            SQL>   SELECT members.memberid,
              2           borrowings.memberid,
              3           books.bookid,
              4           borrowings.bookid
              5      FROM books
              6           LEFT JOIN borrowings ON books.bookid = borrowings.bookid
              7           INNER JOIN members ON members.memberid = borrowings.memberid
              8  ORDER BY members.memberid,
              9           borrowings.memberid,
             10           books.bookid,
             11           borrowings.bookid;
            
              MEMBERID   MEMBERID     BOOKID     BOOKID
            ---------- ---------- ---------- ----------
                     4          4          5          5
                     5          5          6          6
                     6          6          7          7
                     7          7          8          8
                     8          8          9          9
                     9          9         10         10
            
            6 rows selected.
            
            SQL>
            SQL>   SELECT members.memberid,
              2           borrowings.memberid,
              3           books.bookid,
              4           borrowings.bookid
              5      FROM books
              6           LEFT JOIN borrowings
              7           INNER JOIN members
              8              ON members.memberid = borrowings.memberid
              9              ON books.bookid = borrowings.bookid
             10  ORDER BY members.memberid,
             11           borrowings.memberid,
             12           books.bookid,
             13           borrowings.bookid;
            
              MEMBERID   MEMBERID     BOOKID     BOOKID
            ---------- ---------- ---------- ----------
                     4          4          5          5
                     5          5          6          6
                     6          6          7          7
                     7          7          8          8
                     8          8          9          9
                     9          9         10         10
                                           3
                                           4
                                          11
            
            9 rows selected.
            


            В первом случае это список всех взятых книг, второе внутренне соединение убирает не взятые книги.
            Во втором это список всех книг без исключения и информация о их взятии исключая те взятия у которых не определены читатели.

            • 0
              Спасибо, я так и понял, но про внешнее соединение не подумал.
    • +3
      Запятая в перечне WHERE — это всего лишь alias к CROSS JOIN.
      Т.е. в Вашем варианте Вы сначала CROSS JOIN'ите все 3 таблицы, а потом фильтруете получившееся месиво с помощью WHERE.
      (Да, DBMS оптимизируют CROSS JOIN + WHERE до того же, что получается в результате INNER JOIN… ON. Только вот мои мозги не оптимизируют. В смысле, что человеку понимать INNER JOIN… ON гораздо проще.)
      • +2
        Так и есть — это полное пересечение и современые DBMS его хорошо «понимают».

        Я не знаю, возможно это отпечаток какой-то привычки, но не могу подтвердить описанного эффекта — моё сознание просто воспринимает «берём то, что нам нужно». Я в реальной жизни не «джойню» в уме сущности, чтобы осознать такие вещи как «все читатели, которые держат хоть одну книгу, которой в наличии больше среднего». Само описание на русском языке в данном случае строится через взятие полного множества всех читателей и последовательного исключения из него через перечисление правил — это, прямо скажем, легко и естественно. И это хорошо ложится на структуру FROM [источники данных] WHERE с перечислением вышеуказанных ограничений. По другому мне наверно нужно было бы думать «возьмём все комбинации книг, читателей и записей в формулярах, теперь начнём проверять, что id читателя указан в одной из записей о чтении и id книги указан в этой же записи, при этом в наличии этой книги больше среднего». Это слишком непривычно, чтобы читать подобным образом, хотя технически именно это и написано.

        Секция FROM — техническая, в ней просто перечислены сущности, секция WHERE логическая — в ней перечислены правила, идущие из естественного языка и при их естественном чтении легко искать ошибки.

        Вот так это выглядит для меня:
        SELECT -- возьмём:
            member.first_name || ' ' || member.last_name AS "full_name" -- имя и фамилию читателя, объединив их в одну строку
        FROM -- будем смотреть источники:
            member, book, borrowing -- «читатель», «книга», «задолженность»
        WHERE -- ожидая получить:
            member.id = borrowing.member_id AND -- «все читатели, которые держат ...»
            book.id = borrowing.book_id AND -- «... хоть одну книгу, ...»
            book.stock  > (SELECT avg(stock) FROM book) -- «... которой в запасе больше среднего»
        

        Подобная логическая интерпретация сразу позволяет обратить внимание на «странности», которые были бы неочевидны в разбросанных JOIN… ON — сравните исходный запрос и реорганизованный и обратите внимание на тезис, с которого начиналась статья — «SQL очень далёк от естественного языка». Получается, что он на самом деле не так уж и далёк, хотя тезис справедлив, поскольку речь шла немного о другом. Можно с помощью ORM нагенерировать ужас, понятный только машине и SQL это с радостью позволит. Но можно адаптировать для человека — у нас есть для этого достаточно свободы.

        Одна проблема: человеки сразу займутся сравнением вкуса фломастеров и этого у нас не отнять :-)
        • +2
          Для меня FROM — не техническая секция. Для меня FROM — основная секция.

          Выбираю я всегда из одной (основной) таблицы:
          SELECT s.* FROM students s;
          

          Хотя к ней могут быть прицеплены цепочки других:
          • по связям многие-к-одному (или один-к-одному):
            SELECT s.*, f.*
            FROM
                students s
                    join faculties f on s.faculty_id=f.id; -- цепочка длиной 1
            
            SELECT s.*, f.*, u.*
            FROM
                students s
                    join faculties f on s.faculty_id=f.id
                        join universities u on f.university_id=u.id; -- цепочка длиной 2
            SELECT s.*, f.*, t.*
            FROM
                students s
                    join faculties f on s.faculty_id=f.id
                    join towns t on s.home_town_id=t.id;  -- две цепочки длиной 1
            

          • реже, по связям один-ко-многим (или многие-ко-многим) для агрегирования:
            SELECT f.*, some_aggs(s.*)
            FROM
                faculties f
                    join students s on f.id=s.faculty_id
            GROUP BY f.*;
            
            SELECT u.*, some_aggs(s.*)
            FROM
                universities u
                    join faculties f on u.id=f.university_id
                        join students s on f.id=s.faculty_id
            GROUP BY u.*; -- цепочка длиной 2
            

            А реже, потому что в таком случае гораздо изящнее смотрятся подзапросы (в смысле, указывать в FROM только саму основную таблицу, а статистику по таблицам, которые прицеплены связями -ко-многим, выбирать подзапросами).
            К сожалению, и со способом «указать -ко-многим в join'е и group by», и со способом «использовать подзапрос для -ко-многим» есть проблемы (в первом случае мы не можем получить статистику по нескольким цепочкам, а во втором случае нам неудобно (приходится через ROW()) получать несколько статистик из одной цепочки).


          Также таблица, из которой идёт запрос, может быть динамически сконструированной — подзапросом. Правда, join — это тоже де-юре динамически сконструированная таблица.

          Join де-юре всегда left (выше не указывал для простоты). Я надеюсь, СУБД автоматически оптимизирует left join при foreign key'е с not null constraint'ом (а также при foreign key'е без not null constraint'а, но с WHERE t.key_field is not null или WHERE t.* is not null) до inner join'а. Хотя де-факто я часто пишу inner join и вручную (если точно уверен, что здесь not null и оно не поменяется).

          Как по мне, технической является скорее секция с набором выражений (до FROM). Логичнее было бы, если бы выражения в запросе не указывались и СУБД всегда возвращала набор handle'ов строк. Хотя, может быть, это действительно фломастеры.
    • +5
      То что вы сделали в запросе называется old-style-join и является устаревшим со времен ввода стандарта SQL-92. Более 20 лет прошло может быть пора уже перейти на «новый» стандарт?
      Есть несколько причин использовать «новый» стиль (если стандарта по вашему мнению не достаточно) могу привести еще несколько аргументов в пользу нового стиля. Во-первых разделения объединения и фильтраци в разные блоки. Во-вторых в случае если вы забыли указать условие объединения старый стиль приведет к расчету огроного объема данных и неверного результата (который может быть не так легко обнаружить), в то время как новый выдаст синтаксическую ошибку (http://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/). Можно продолжить гуглить и находить «за» и «против», а можно просто перейти на использование стандартов и выбросить дурное из головы. Ваш код могут читать люди которые родились после введения стандарта SQL-92 и ваш код будет выглядеть для них так, как будто его писали мамонты.
      • +3
        Как раз хотел что-то в таком духе написать. Когда я вижу 10 INNER JOIN'ов, я всегда знаю, какое количество записей будет: в реальной жизни получается такое же, как в «главной» табличке либо чуть меньше в случае NULL-ов. В случае вот таких перемножений множества нить сразу теряется, непонятно, какой порядок записей вернет запрос — N? N*M? N*M*K? Нужно разбираться с условиями, смотреть что там прописанно. Видешь LEFT JOIN'ы — гарантированно знаешь, сколько записей. видешь INNER JOIN — понимаешь порядок. Видишь CROSS JOIN — предполагаешь M*N записей. А когда оказывается, что это нифига не CROSS, а самый что ни на есть INNER, чувствуешь себя обманутым — пообещали одно, а на деле совсем другое.
  • 0
    freetonik можно ли указать ссылку на исходную учебную таблицу? Чтобы новички могли скопировать её и потренироваться с запросами локально.
  • +1
    А как же INSERT c использованием SELECT вместо VALUES?
  • +8
    Главное чтобы потом тому кто это всё прочитал рассказали про HAVING до того как он начнёт лепить вложенные запросы везде где это нужно и не нужно.
    • +2

      ЕМНИП, HAVING не дает возможности использовать назначенное имя для колонки, а потому вложенные запросы могут быть предпочтительнее даже когда достаточно HAVING:


      SELECT foo, длинное-длинное-выражение as bar
      GROUP BY foo
      HAVING длинное-длинное-выражение > 42
      
      SELECT *
      FROM (
        SELECT foo, длинное-длинное-выражение as bar
        GROUP BY foo
      ) AS temp
      WHERE bar > 42

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

      • –1

        А также, если в качестве длинного-длинного-выражения используется вызов функции, которое во фразу group by не засунешь.

      • +1
        За все СУБД не скажу, но в MySQL можно использовать алиасы из SELECT в HAVING.
        SELECT foo, длинное-длинное-выражение as bar
        GROUP BY foo
        HAVING bar > 42
        
        • 0
          Это идеологически неправильно с точки зрения стандартов, но достаточно удобно для разработчиков. В некотором смысле типичное решение для MySQL.
      • 0
        Начиная с некоторой длины стоит вместо подзапросов использовать CTE, который суть те же подзапросы, только расположенные в человекопонятном порядке.
  • +2
    А чего это Вы member'ов так уважаете (имя и фамилия отдельно), а авторов — нет?
    И вообще, авторов в отдельную таблицу бы. И многие ко многим авторы-книги.
    А то мало ли, вдруг кто-то напишет книгу в соавторстве с кем-то?
    Что потом делать с запросом
    SELECT bookid AS "id", title
    FROM books
    WHERE author='Dan Brown';
    

    А еще таблицу поступлений книг, и триггеры на обновление поля stock на приход/уход.
    Вот тогда уже будет интереснее.
    • 0
      И многие ко многим авторы-книги.

      Как-то писал небольшую соцсеть, и тоже пришлось сделать «многие ко многим», чтобы у статьи могло быть несколько авторов, а у авторов несколько (или много) статей. Так что пожелание весьма правильное.
      • +1
        а еще есть «под редакцией %autorname%» и прочие библиографические нюансы.
  • 0
    Ещё стоит заметить, что
    member_id, book_id, first_name, last_name

    читается легче, чем
    memberid, bookid, firstname, lastname

    Это кажется незначительным различием в простом запросе. Но когда запрос сложный и нужно посидеть, «распарсить» его и понять — каждая подобная мелочь сильно облегчает работу.
  • 0
    Зачем в 6.1 подзапрос? :/
  • +3
    Не хватает оператора HAVING (для выборки по результатам группировки)
    По поводу конструкции:

    WHERE borrowings.bookid IN (SELECT bookid
    FROM books…

    Не знаю, как сегодня, а лет десять назад на файрбёрде она «оптимизировалась» в список сравнений через OR, что при большой выборке делало мегависюк на стороне сервера. MS SQL кушал такое с лёгкостью.
    Есть мнение, что это «джойн для бедных» и вместо него надо использовать именно джойн:
    Вместо

    Select name from authors
    Where id in (select author_id from bests)

    Надо:

    Select a.name
    From authors a
    Inner join bests b
    On a.id=b.author_id
    Where b.id is null
    • +1
      Про WHERE… IN (...) полностью поддерживаю. Скажу за MySQL. До версии 5.6 такие подзапросы в секции WHERE сервер вообще никак не оптимизировал. Позже была добавлена оптимизация простых подзапросов без JOIN'ов.
      Так что лучше избегать подобных конструкций и выносить их в секцию с JOIN'ами как, например, показано в комментарии выше.
      • 0
        Помнится, в IBM DB/2 был инструмент, позволяющий увидеть, как именно (в какой последовательности и как быстро) запрос будет выполняться в реальности.
        Как показала реальная практика, результат оптимизации и скорость выполнения запроса в большей степени зависели от наличия индексов, чем от формы записи запроса.
        И при некоторых комбинациях индексов наличие подзапроса во WHERE существенно ускорало выполнение запроса, в то время как использование JOIN приводило к игнорированию индексов, что приводило к замедлению.
        Например, при наличии индексов по полям, используемым в WHERE для вложенного запроса и отсутствии индексов для таблицы из FROM, если не путаю за давностью лет, конечно.

        Отсюда, кстати, вывод. SQL-оводу имеет смысл знать об индексах, используемых в его СУБД и их особенностях.
    • +1
      «Надо» будет всегда возвращать 0 строк.
      • 0
        в MySql работает, только там = null вместо is null. Сам удивился, когда попробовал.
        • 0
          Нет, ты прав. У меня смешались в голове запросы where… in и where not… in
          Для второго случая — left join и = null
    • +2

      Если в подзапросе есть дубликаты — то JOIN продублирует строки основного запроса, в отличии от IN. Избавляться от дублей придется с помощью группировки — а она может сказаться на оптимальности плана, поскольку требует упорядоченности.


      MS SQL и сейчас кушает такие подзапросы с легкостью (более того, в плане выполнения IN и JOIN превращаются в одинаковые операции).


      Поэтому при использовании СУБД с мощным оптимизатором я бы напротив, советовал в подобных случаях предпочитать IN джойнам.

      • +2
        > Если в подзапросе есть дубликаты — то JOIN продублирует строки основного запроса, в отличии от IN. Избавляться от дублей придется с помощью группировки — а она может сказаться на оптимальности плана, поскольку требует упорядоченности.

        Мне ли учить монстров SQL удалять дубликаты?
        … JOIN (SELECT DISTINCT author_id FROM bests) b

        По поводу неоптимальности. Оптимальность плана — штука такая, её по месту надо смотреть. Выше я писал про «оптимизацию» в файрбёрде (в середине нулевых) именно при использовании where...in. Буквально запрос превращался в что-то вроде WERE (a.OKP='11356432525' or a.OKP='9808958635'… <и так несколько тысяч раз>) и заново разбирался. Эпичненько?

        Я не навязываю решение, но говорю об альтернативном мнении. Так что мнения — отдельно, факты — отдельно. А факты для каждой системы могут быть разные. Я всегда использовал в MSSQL Where...in и не парился, а файрбёрд тогда выкинул (именно потому, что имел много таких запросов). Но если у кого-то где-то запрос where...in не оптимизируется или влечёт проблемы — есть ещё вариант с джойном!!!
        • 0
          Привык уже использовать WHERE EXISTS вместо WHERE X IN (SELECT Y FROM ...)

          Если нужно будет сделать отрицающее условие, то в первом случае надо будет сделать WHERE NOT EXISTS, а вот NOT IN уже использовать опасно, и придется немного переписать в WHERE X NOT IN (SELECT Y FROM… WHERE Y IS NOT NULL)
    • +2
      Меня очень правильно поправили:

      Select a.name
      From authors a
      Inner join bests b
      On a.id=b.author_id

      вместо where… in

      Select a.name
      From authors a
      left join bests b
      On a.id=b.author_id
      Where b.id is null

      вместо where not… in
  • +2
    Мне кажется читать SQL станет намного проще если использовать более реальные примеры. Зачем писать
    WHERE borrowings.bookid IN (SELECT bookid
    FROM books
    WHERE stock>(SELECT avg(stock)
    FROM books))

    Когда можно написать.
    WHERE books.stock>(SELECT avg(stock) FROM books)

    Зачем писать
    SELECT * FROM (SELECT author, sum(stock)
    FROM books
    GROUP BY author) AS results
    WHERE author='Robin Sharma';

    Когда можно написать
    SELECT author, sum(stock)
    FROM books
    WHERE author='Robin Sharma'
    GROUP BY author;

    И почему бы не использовать конструкцию HAVING, здесь?
    SELECT author
    FROM (
    SELECT author, sum(stock)
    FROM books
    GROUP BY author
    ) AS results
    WHERE sum > 3;

    Возможно вы усложнили запросы, чтобы показать некоторые возможности SQL? Но такой подход боюсь только собьет с толку новичка.
  • 0
    Умею писать на SQL по древней методичке. Использую сейчас в основном MSSQL.
    Надеялся, что хоть тут мне толково объяснят что такое INNER JOIN и чем он отличается от JOIN, но, видимо, не судьба :)
    • +1
      Ничем. Если есть слева, то будет и справа. Например, у книги всегда есть автор (я тут разделил книжку с фамилиями авторов, чтоб легче искать было), смысла нет ставить меж ними left join. А вот читатель у книги не всегда может быть, поэтому в книга-join-читатель должен быть left join, если мы хотим найти те книги, которые сейчас никто не взял.
    • 0
      Почитали бы MSDN, например.

      INNER
      Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.
    • 0

      Есть 5 видов операции JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS.


      INNER — это вид JOIN по умолчанию, это слово можно пропускать. Т.е. формы a JOIN b и a INNER JOIN b — это строго одно и то же, просто второй вариант считается "академичнее".


      Слово OUTER тоже можно не писать.

      • 0
        И тут врывается NATURAL JOIN
        • 0
          И говорит: «Ой, я же синтаксический сахар»! Ничего не добавляет к семантике запроса — только сокращает запись, и может быть как inner, так и left/right.
          • 0
            Ну все равно он существует )) И поэтому нельзя сказать, что их всего 5. Конечно в реальной жизни NATURAL JOIN используется так же часто как и RIGHT JOIN.
        • +2
          Бесполезный и опасный тип джоина.

          Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки», по которым джоинить — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)

          Т.е. если подходить формально, джоины можно делить:
          1. По поведению в случае отсутствия записей с одной из сторон:
            1. INNER
            2. LEFT OUTER
            3. RIGHT OUTER
            4. FULL OUTER
          2. По способу указания условий соединения:
            1. ON условие — явное указание условий
            2. USING (колонка1, колонка2, ) — сокращение для «ON левая_таблица.колонка1=правая_таблица.колонка1 and левая_таблица.колонка2=правая_таблица.колонка2 and »
            3. NATURAL — сокращение для «USING (все-колонки-присутствующие-в-обеих-таблицах-сразу)»
            4. CROSS — сокращение для «ON true» (в отличие от прошлых трёх способов, CROSS может быть только INNER, правда возможно это нюанс PostgreSQL)

          • 0
            По бесполезности и возможности отстрелить ногу — я полностью согласен. Как уже говорил, в продакшене я не видел чтобы использовали Natural (и без него веселья много).

            А вот по классификации, я не то что не согласен, просто это бесполезное занятие, вон Oracle класифицирует их так:

            1. Equi Joins
            2. Self Joins
            3. Cartesian Products
            4. Inner Joins
            5. Outer Joins
            6. Antijoins
            7. Semijoins

            Хотя по мне Equijoin это частный случай Inner Join, а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS соответственно
            • 0
              1. А можно источник, где они их так классифицируют?
              2. Ну, скажу честно, я пока не вижу никакой причины классифицировать по-другому, чем у меня.
              • 0
                Да конечно, только ссылка в виде текста, наверное между GT и хабром есть разница в доступности тегов для Read&Comment пользователей:

                Ссылка из доки по 12с: https://docs.oracle.com/database/121/SQLRF/queries006.htm
                Интересно стало, а что же там у MS: https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

                • +1
                  Выводы:

                  1. Oracle не классифицирует джоины так. В той статье у них не классификация, а список важных определений. То, что приведенные названия являются подзаголовками одного уровня, не делает их классификацией (нигде явно не сказано, что это классификация; иначе можно было бы и первый подгалоговок «Join Conditions» считать типом джоина; да и вообще, логика, Equi Join может быть одновременно и Self, и Inner/Outer).

                  2. Oracle в той статье говорит в джоинах в гораздо более широком смысле. Они рассматривают джоины, не как операторы секции FROM, а как любые действия, приводящие к соединению таблиц (в т.ч. подзапросы) — в отличие от статей в доках postgres и MS. Т.е. это, формально говоря, «другие джоины».

                  > Хотя по мне Equijoin это частный случай Inner Join
                  Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств (в впротивоположность, например, «table1 t1 JOIN table2 t2 ON (t1.x-t2.x)²+(t1.y-t2.y)²≤1.0»). Equi/не-Equi и Inner/Outer — это несвязанные вещи.

                  > а Antijoins и Semijoins это вообще NOT EXISTS и EXISTS
                  Так и есть. Поймите, Oracle говорит о «других» джоинах — она в отличие от postgres и MS говорит не о синтаксисе, а о конечном результате. Тот же «FROM table1 t1, table2 t2 WHERE t1.id=t2.id» по postgres/MS будет cross join, а по Oracle — equi inner.

                  Т.е. классификация джоинов-по-Oracle выглядит как-то так: 1. По типу критериев: (а) equi — система равенств, (б) cartesian product — полное отсутствие критериев, (в) все другие. 2. По включению-при-отсутствии-пары: (а) inner — не включаем, (б) outer — включаем, (в) anti — включаем ТОЛЬКО когда нет пары, (г) другое. 3. По связи с самим собой: (а) self, (б) не-self. Semi — отдельная история.


                  MS смотрит аналогично postgres'у.
                  • 0
                    >Oracle говорит о «других» джоинах
                    Магические «другие» JOIN мы можем увидеть в плане запросов.

                    NESTED LOOPS (Equi | Self | Cartesian | Outer | Anti | Semi)
                    HASH JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)
                    MERGE JOIN (Equi | Self | Cartesian | Outer | Anti | Semi)

                    Причем зачастую они будут одинаковы и у Oracle и у SQL Server (pg под рукой сейчас нет)

                    >Нет. Equi Join — это когда критерием соединения является явная (указанная в ON/WHERE) или неявная (с USING/NATURAL) система равенств

                    Тогда скажите запрос:
                    SELECT *
                    FROM a, b
                    WHERE a.id = b.id

                    Это EQUI JOIN или INNER JOIN
                    • 0
                      > Тогда скажите запрос
                      Ответ на Ваш вопрос содержится в том сообщении, на которое Вы отвечаете: equi inner.

                      > Это EQUI JOIN или INNER JOIN
                      Вопрос поставлен некорректно. Тот перечень, что Вы приводили, — это не непересекающиеся классы.

                      Вот типы джоинов.
                      • 0
                        В сухом остатке имеем
                        > An equijoin is an inner join whose join condition contains an equality operator.

                        А типы JOIN-ов (по градации Oracle)
                        * Inner Joins
                        * Outer Joins
                        * Semijoins
                        * Antijoins

                        • 0
                          именно!
        • 0
          Бесполезный и опасный тип джоина.

          Не говоря уже о том, что это не отдельный тип (это всего лишь указание «сам угадай колонки, по которым джоинить» — бывает NATURAL INNER, NATURAL LEFT OUTER, NATURAL RIGHT OUTER и NATURAL FULL OUTER). (Правда, CROSS тоже не отдельный тип.)

          Т.е., строго говоря, джоины можно делить:
          1. По поведению в случае отсутствия записей с одной из сторон:
            1. INNER
            2. LEFT OUTER
            3. RIGHT OUTER
            4. FULL OUTER
          2. По способу указания условий соединения:
            1. ON условие — явное указание условий
            2. USING (колонка1, колонка2, ) — сокращение для «ON левая_таблица.колонка1=правая_таблица.колонка1 and левая_таблица.колонка2=правая_таблица.колонка2 and »
            3. NATURAL — сокращение для «USING (все-колонки-присутствующие-в-обеих-таблицах-сразу)»
            4. CROSS — сокращение для «ON true» (в отличие от способов 1-3, CROSS в PostgreSQL может быть только INNER)

  • +2
    Use having clause, Luke.
  • +1
    Select внутри where сильно замедляет скорость в ряде случаев.
  • –3
    Средний разработчик, боящийся SQL? Импосибру.

    Хотя, «средний» разработчик, который всю дорогу ORM использовал в режиме «оно само», может и испугается.
  • 0
    не хватает описания использования having. Полезно упомянуть, что можно использовать не только COUNT(), но и COUNT(x)
    • +1
      Я бы еще добавил вариант COUNT(DISTINCT x).
      Полезно когда необходимо посчитать количество уникальных значений в поле.
  • –1
    SQL — важная и нужная штука, но мои глаза всегда мозолил такой порядок аргументов, сравните:
    SELECT поля FROM таблица…
    DELETE FROM таблица…
    UPDATE таблица (поля) SET…
    INSERT INTO таблица (поля)…

    Что-то примерно такое представляется мне более структурированным:
    SELECT FROM таблица [FIELDS поля]…
    DELETE IN таблица…
    UPDATE IN таблица FIELDS поля=новые значения…
    INSERT IN таблица [FIELDS поля[=новые значения]]… /*или по-старому*/

    Что лучше:
    1. Консистентный порядок
    2. Более заметна разница между модификацией данных и получением
    3. Единый формат описания полей

    В общем, помечтал и хватит (:
    • 0
      Что список полей перед FROM в SELECT — нелогично, — абсолютно согласен (получается, мы сначала используем alias таблицы и только потом объявляем его).
      Но уж так сложилось…
      • 0
        SELECT импользуєтся не только для таблиц… но и для переменных… потому вот как есть — логичнее…

        INSERT таблица (asd,asd) VALUE («2»,«3») в этих дужечках еще на этапе расшифровки запроса, создается набор значений, которой потом ставится в базу. И запутаться сложнее — вставка и выбор визуально совсем разные…

        • +1
          потому вот как есть — логичнее…

          Отнюдь. Всего лишь можно опускать секцию FROM (аналогично тому, как сейчас опускаются секции FROM/GROUP BY/пр., когда они не нужны), когда запрос идёт не из таблиц (хранимая функция и пр.):
              SELECT FROM users u WHERE u.birthdate<'1950-01-01' EXPRESSIONS u.name, u.surname;
              SELECT EXPRESSIONS myfunction(1, 2) as myfunc12result;
          


          • 0
            Т.е. текущий синтаксис такой как он есть не потому, что он самый логичный, а потому, что так сложилось.
            Про INSERT то не я говорил, INSERT INTO table t (f1, f2) VALUES (v1, v2), (v3, v4) меня устраивает, это уже отвечайте тому, кто писал про INSERT (и, да, синтаксис никак не связан с тем, что «создается набор значений», например, INSERT INTO users SET name='vasya', surname='pupkin' SET name='fedya', surname='petrov' тоже прекрасно бы «создавало набор значений»).
          • 0
            И, кстати, вот ещё одна причина указывать выражения после всего, а не перед FROM (окромя общей логичности и использования ещё-не-объявленых алиасов-таблиц):

            Чайники не понимают, что расчёт этих выражений происходит после группировки GROUP BY'ем, а не до.
            https://habrahabr.ru/post/305926/#comment_9708148
            При "SELECT FROM … WHERE … GROUP BY … HAVING … EXPRESSIONS …" бы таких проблем не было.
    • +1
      Вы так и говорите в жизни «возьмите из корзины мяч и кубик», а не «возьмите кубик и мяч из корзины»?
      • 0
        Это зависит от того, о чем мы говорили последнем.

        Найди большую красную корзину. Нашёл? Возьми из корзины синий мячик.

        Хочешь мячик? Возьми из его из красной корзины.


        Т.е., если мы рассматриваем корзину как важный этап последовательности действий, мы указываем её в начале. Если мы рассматриваем корзину как незначительное уточнение, мы указываем её в конце. (Угадайте, чем является указание таблицы в SQL-запросе.)

        И да, множественность предметов увеличивает вероятность того, что мы назовём корзину вначале: «возьми из корзины мячик, кубик, расчёску, бантик и погремушку».
  • 0
    для начинающих рекомендую почитать что-то из серии head first издательства O`Reily (с аббревиатурой SQL в названии).
    Там в картинках, с хорошими примерами, последовательно и системно будет описана работа с запросами.
    можно скачать в електронном виде…
  • +2

    Конечно, в таком виде этот запрос читать невозможно:


    SELECT members.firstname || ' ' || members.lastname
    AS "Full Name"
    FROM borrowings
    INNER JOIN members
    ON members.memberid=borrowings.memberid
    INNER JOIN books
    ON books.bookid=borrowings.bookid
    WHERE borrowings.bookid IN (SELECT bookid
      FROM books
      WHERE stock>(SELECT avg(stock)
        FROM books))
    GROUP BY members.firstname, members.lastname;

    Но после небольшого форматирования, выкидывания ненужных частей (зачем здесь соединение с books, если таблица вообще никак не используется???) и простановки псевдонимов таблиц разобраться в нём уже не составляет труда:


    select m.firstname||' '||m.lastname "full name"
    from members m
    join borrowings b on (b.memberid = m.memberid)
    where b.bookid in (select bookid from books where stock > (select avg(stock) from books))
    group by m.firstname, m.lastname
    ;
  • –2
    > Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY, или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT'ом, то непонятно, какие из возможных значений нужно брать.
    В MySQL при настройках по умолчанию до 5.7 не обязательно, с 5.7 включено строгое соответствие ANSI SQL, которое можно включить и в более ранних версиях.

    Меня вот всегда раздражает это требование перечислять в GROUP BY всё, что уже перечислил в SELECT.
    • 0
      Требование очень логичное, если не считать одно «но»:
      к сожалению, когда Вы уже перечислили в GROUP BY все поля первичного ключа (или другого уникального ключа) какой-то таблицы, при выборке других полей этой таблицы их всё равно приходится указывать в GROUP BY:
      CREATE TABLE users (
          id serial not null primary key,
          name text not null,
          surname text not null
      );
      CREATE TABLE items (
          id serial not null primary key,
          owner_user_id integer not null references users (id),
          ...
      );
      
      SELECT u.id, u.name, u.surname, count(*) as item_count
      FROM users u left join items i on u.id=i.owner_user_id
      GROUP BY u.id; -- , u.name, u.surname
      

      Почему-то в таком запросе приходится указывать в GROUP BY не только u.id, но и u.name, u.surname. Вот это меня выбешивает (нелогично, ведь если все поля уникального ключа таблицы перечислены в GROUP BY, то перечисляй/не перечисляй остальные — разницы уже нет). Но в остальном это требование очень логично.
      • –1
        > Требование очень логичное
        Почему тогда в MySQL всё работает? У меня всегда поведение при выборке соответствовало ожидаемому.
        > Почему-то в таком запросе приходится указывать в GROUP BY не только u.id, но и u.name, u.surname.
        Я вроде про то и написал.
        • +1
          Я давно не имел дела с MySQL, но насколько я помню, там всё было через ж#пу.
          В случае с GROUP BY: там вообще это требование не проверялось, например, можно было сделать SELECT t.field2 FROM table1 t GROUP BY t.field1, где field1 отнюдь не уникальный ключ.
          Что полный бред (хотя может с тех пор что-то изменилось).
          • +2
            > Я давно не имел дела с MySQL, но насколько я помню, там всё было через ж#пу.
            Это настраивается, как я написал в первом сообщении. Не знаю точно с какой версии, но на всех актуальных так точно.
            > Что полный бред
            Не вижу бреда в выборке одного поля при группировке по другому.
            Просто при не уникальном ключе в группе будет по несколько строк, и возвращена будет первая.
            Конечно, возвращаемое значение не определено и зависит от реализации, но думаю, делающие так знают о последствиях, и сортировка в группе их не интересует.
            А вот требование прописывать остальные, как уже отписались выше- излишне и лишь заставляет дублировать все колонки в каждом запросе.
            А если у меня они прописаны в коде (что поделать, не везде совершенный код), что теперь, менять запросы в куче мест при добавлении нового поля?
            • +1
              Всё, к сожалению, не настраивается. Остаётся всё равно куча бреда не по стандарту. Например, в какой-то версии (не знаю как щас):
              CREATE TABLE t3 (
                  ...,
                  t1_id int not null REFERENCES t1 (id), -- тупо игнорируется!!!
                  t2_id int not null,
                  FOREIGN KEY (t2_id) REFERENCES t2 (id)
              );
              


              Не вижу бреда в выборке одного поля при группировке по другому.

              Если Вы хотите из набора строк (которые получаются в результате GROUP BY) получить одно значение, Вы используете агрегатную функцию. Точка. Получение первого значения из набора и получение неопределённого значения из набора — это, как и, например, получение суммы, тоже агрегатные функции.

              Если таких функций нету, Вы можете их дописать.
              SELECT
                  count(i.*) as count,
                  sum(i.price) as total_price,
                  agg_first(i.name) as first_name,
                  agg_some(i.name) as some_name,
                  agg_last(i.name) as last_name
              FROM
                  items i
              GROUP BY
                  i.category_id -- неуникальное поле
              

              Теперь понимаете?
              • 0
                Давайте покажу на конкретном примере.
                Есть таблица пользователей и сессий, стандартные для phpBB, вот для представления их урезанные версии:
                CREATE TABLE `phpbb_sessions` (
                `session_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
                `session_user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
                `session_time` int(11) unsigned NOT NULL DEFAULT '0',
                `session_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
                `session_viewonline` tinyint(1) unsigned NOT NULL DEFAULT '1',
                PRIMARY KEY (`session_id`),
                KEY `session_time` (`session_time`),
                KEY `session_user_id` (`session_user_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

                CREATE TABLE `phpbb_users` (
                `user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
                `user_type` tinyint(2) NOT NULL DEFAULT '0',
                `username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
                `username_clean` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
                `user_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
                PRIMARY KEY (`user_id`)
                ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

                Вот запрос для поучения текущих активных пользователей:
                SELECT s.session_user_id AS user_id, s.session_viewonline, u.username, u.user_type, u.user_colour
                FROM phpbb_sessions s
                LEFT JOIN phpbb_users u ON s.session_user_id = u.user_id
                WHERE s.session_time >= 1469004330 AND s.session_user_id <> 1
                GROUP BY s.session_user_id
                ORDER BY u.username_clean

                На MySQL 5.6 со стандартными настройками он работает прекрасно, при других настройках или на других БД выдаёт что- то типа
                'phpbb.u.username_clean' isn't in GROUP BY [1055]

                Вопрос- зачем мне прописывать в группировку это поле? Если его прописать, будет ругаться на следующее, и так далее, пока не пропишешь все поля из SELECT:

                GROUP BY s.session_user_id, s.session_viewonline, u.username, u.user_type, u.user_colour
                • +1
                  А что вы хотите этим GROUP BY сделать?
                  Попробую предпроложить, что раз нет ни одной агрегирующей функции, то наверное хотите получить уникальные записи
                  Может тогда лучше так?

                  SELECT DISTINCT
                  s.session_user_id AS user_id
                  , s.session_viewonline
                  , u.username
                  , u.user_type
                  , u.user_colour
                  FROM phpbb_sessions s
                  LEFT JOIN phpbb_users u
                  ON s.session_user_id = u.user_id
                  WHERE s.session_time >= 1469004330
                  AND s.session_user_id <> 1
                  ORDER BY u.username_clean
                  • 0
                    У него в запросе логическая ошибка. Он хочет получить перечень активных пользователей (не сессий; на одного пользователя может приходиться несколько сессий). Для этого он group-by'ит по пользователю (точнее по s.session_user_id, но оно джоинится с первичным ключём пользователя). Однако поля сессии он не пропускает через агрегатные функции.

                    Скорее всего, логически-правильный запрос должен выглядеть так:
                    SELECT u.*, bool_or(s.session_viewonline) as at_least_one_session_viewonline
                    FROM phpbb_sessions s left join phpbb_users u on s.session_user_id=u.user_id
                    WHERE s.session_time>=1469004330 and s.session_user_id<>1
                    GROUP BY u.* -- или "GROUP BY u.user_id", или "GROUP BY s.session_user_id" (пофиг)
                    ORDER BY u.username_clean;
                    
                    • 0
                      Тут же обсуждали невозможность использования конструкции вида GROUP BY u.*. Итог закономерен:
                      > Ошибка в запросe (1064): Syntax error near '* ORDER BY u.username_clean' at line 4
                      Само собой
                      «GROUP BY u.user_id», или «GROUP BY s.session_user_id» (пофиг)
                      не сработало, требует перечисления полей напрямую.
                      Ну и функции bool_or в MySQL моей версии (10.1.15-MariaDB) не завезли.
                      • 0
                        Это я же и обсуждал.
                        Я потому и пишу «логически-правильный»; это псевдокод; в реале конечно же, к сожалению, придётся перечислять после GROUP BY все поля.
                        Я к тому, чтобы Вы отличали реализацию стандарта с недоработкой (когда выражение, единственность значения которого очевидна, нельзя выбрать, не указав явно в GROUP BY) от полной вседозволенности (когда вообще никаких проверок нет, что бред).
                    • 0
                      Ну и выбирать все поля в phpbb_users излишне, как я писал, таблицу я запостил не всю, в оригинале там 50+ колонок.
                  • 0
                    > А что вы хотите этим GROUP BY сделать?
                    Я эту часть кода только портировал, написал её VEG, может он пояснит, если увидит.
                    Вообще да, избавление от GROUP BY в этом месте выглядит логично, возьму на заметку, благодарю.
                • 0
                  Ну, в таком случае я согласен.

                  Просто Вы не путайте две разные вещи:
                  • Употребление полей таблицы вне GROUP BY, когда первичный/уникальный ключ этой таблицы уже указан в GROUP BY. В принципе, с логической точки зрения допустимо, но в стандарте почему-то запрещено.
                  • Употребление вне GROUP BY полей, уникальность которых никак не гарантируется структурой запроса. Стандартом запрещено и очень правильно.


                  Т.е. в Вашем запросе ругается на u.username_clean излишне. В Вашем запросе будет ругаться на s.session_viewonline очень правильно.
                  • +1
                    Потому что единственность s.session_user_id и u.* гарантируется структурой запроса (единственность s.session_user_id гарантируется тем, что оно непосредственно указано в GROUP BY, а единственность u.* гарантируется тем, что u.user_id (который первичный ключ для u) за-join-ен с первым).

                    Единственность всех остальных полей в s.* (кроме s.session_user_id) структурой запроса не гарантируется. Использоваться их вне агрегатных функций — нельзя. Даже просто с логической точки зрения (у Вас на одну строку результата приходится потенциально-несколько значений s.session_viewonline; какое из них брать: первое, bool_and или bool_or?).
                    • 0
                      > Даже просто с логической точки зрения (у Вас на одну строку результата приходится потенциально-несколько значений s.session_viewonline; какое из них брать: первое, bool_and или bool_or?).
                      Интересное замечание. Посмотрел- в оригинале в phpBB (достаточно популярный форумный движок) выбираются все подходящие значения, а дубли пропускаются на стороне PHP. То есть берётся первое. В принципе, как у меня, хотя понятие «первого» могут отличатся.
      • 0

        В один прекрасный день окажется, что то поле, которое раньше было уникальным уже не может таковым быть. И вы убираете ограничение уникальности. Упс. Половина запросов сломалась на ровном месте… Так что о логичности вашей хотелки ещё можно поспорить.

        • 0
          Почему тогда
          SELECT t.* FROM table1 t GROUP BY t.*;
          

          сделать нельзя?

          Упс…
          • +1
            Таки можно, но не так.

            SELECT DISTINCT t.* FROM t

            Не знаю только, можно ли добавить в выборку агрегатные функции. Проверить не могу, ибо сегодня я маляр-штукатур

            Кстати, иметь в таблице полностью идентичные записи очень, очень некошерно, т.к средствами sql их невозможно идентифицировать по отдельности. Именно поэтому и вводят традиционно автоинкрементный id, если нет строгого регламента на наполнение, и осмысленного uid поля или комбинации полей
            • 0
              Речь не о DISTINCT, а о том, что я хочу сделать, например:
              SELECT t.*, count(i.*) as item_count, sum(i.price) as total_price
              FROM table1 t left join items i on t.id=i.parent_id GROUP BY t.*;
              -- а нельзя
              

              И так тоже нельзя:
              SELECT t.*, count(i.*) as item_count, sum(i.price) as total_price
              FROM table1 t left join items i on t.id=i.parent_id GROUP BY t.id;
              -- подразумевая, что table1 имеет PRIMARY KEY (id)
              

              Я об этом.

              Кстати, иметь в таблице полностью идентичные записи очень, очень некошерно

              Это вообще мимо кассы.
              • 0
                Может быть так подойдет?

                SELECT t.*
                , count(*) over (PARTITION BY t.id) as item_count
                , sum(i.price) over (PARTITION BY t.id) as total_price
                FROM table1 t
                LEFT JOIN items i
                ON t.id = i.parent_id
                ;
                • +1
                  Ну и оно вернёт кучу повторяющихся записей (по одной строке на пару (t, i), а не на t).
                  Которые, конечно, можно убрать DISTINCT'ом.
                  Но, по-моему, это не самое логичное решение.
                  (Хотя, не спорю, приемлемое; наравне с ручным перечислением в GROUP BY и подзапросом.)
                  • +1
                    Оконки еще будут медленнее GROUP BY.

                    Еще в голову пришло динамически собирать запрос.

                    Но вообще использовать SELECT * это как то не по феншую.
                    • 0
                      У меня запросы и так динамически собираются :).
                      Тут вопрос (мой) не в том, как сделать, чтоб меньше писать.
                      Тут вопрос в том, что стандарт касательно GROUP BY (или его реализация в PostgreSQL — хотя скорее всё таки стандарт) немного недоработан.
                      То, что нельзя вне аггрегатных функций использовать выражения, единственность значений которых не гарантируется — тут я обомя руками за.
                      То, что считается, что единственность гарантируется только для выражений, явно указанных в GROUP BY — это недоработка. Если первичный ключ какой-то таблицы целиком указан в GROUP BY или целиком за-join-ен с полями, указанными в GROUP BY — то любые выражения над полями этой таблицы имеют единственное значение. Аналогично для non-null UNIQUE-ключей.
                      Особо прикалывает, что PostgresSQL позволяет писать «GROUP BY t» и «GROUP BY t.*» (где t — алиас таблицы), но обрабатывается оно неожиданно: первый вариант позволяет только непосредственно «SELECT t FROM …» и «SELECT func1(t) FROM …» (но не «SELECT t.field1 FROM …» — при том, что мы можем успешно заюзать «CREATE FUNCTION func1(t table1) RETURNS type1 AS $$begin return t.field1; end$$ LANGUAGE plpgsql IMMUTABLE»), а второй, судя по всему, вообще никак не срабатывает.
              • +1
                >Это вообще мимо кассы.
                Ну, каков вопрос, таков и ответ
                SELECT t.* FROM table1 t GROUP BY t.*;
                Здесь группировка бессмысленна, если в таблице нет дубликатов, или я не прав?

                Про запрос с джойном: кто мешает группировать только правую таблицу по внешнему ключу в подзапросе?
                SELECT t.*,i.item_count, i.total_price
                FROM table1 t
                left join (
                select parent_id, count(*) as item_count, sum(price) as total_price
                from items
                group by parent_id
                ) i
                on t.id=i.parent_id;


                Заметь, группировка идёт по одному заведомо индексированному полю, и набор данных для джойна меньше, чем в исходной таблице. Так что и по производительности можно выиграть
                • 0
                  >Это вообще мимо кассы.
                  Ну, каков вопрос, таков и ответ
                  SELECT t.* FROM table1 t GROUP BY t.*;
                  Здесь группировка бессмысленна, если в таблице нет дубликатов, или я не прав?

                  Обсуждение велось в контексте. Имелось в виду «SELECT …, t.*, … FROM table1 t … GROUP BY …, t.*, …» — если уж буквально. Обсуждалась нелогичность того, что нельзя обращаться ко всем полям таблицы, указав в GROUP BY только её первичный ключ, а также того, что просто «t.*» в GROUP BY указать нельзя.

                  Про запрос с джойном: кто мешает группировать только правую таблицу по внешнему ключу в подзапросе?
                  SELECT t.*,i.item_count, i.total_price
                  FROM table1 t
                  left join (
                  select parent_id, count(*) as item_count, sum(price) as total_price
                  from items
                  group by parent_id
                  ) i
                  on t.id=i.parent_id;


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

                  Разумный вариант. Самый разумный из всех предложенных. Очень надеюсь, что он не быстрее (что DBMS оптимизирует его и исходный до одного состояния) — но по красоте он 100% лучше.
                  • 0
                    Небольшая разница только между исходным вариантом и новым: если каким-то строкам table1 не соответствует ни одной строки items, то в новом варианте значения всех статистик будут NULL, а в старом — по специфике аггрегатной функции (например, count(*) в старом варианте вернёт 0). Но это несущественно.

                    P.S.: Если не секрет, почему маляр-штукатур?
                    • 0
                      Уже два дня как сантехник. Берусь за всё. Кушать хочется, а из разработчиков ушёл по глупости лет восемь назад на тёплое место. Место остыло, а специализация старая сегодня не востребована (работал в Дельфи ещё до .NET), теперь с тоски переучиваюсь на веб-разработку. Когда время есть, ибо я кормлю жену, четырёх детей и один банк.

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

                        А почему веб-разработка? C++ разве не поближе к Delphi будет?
                        • +1
                          > А почему веб-разработка? C++ разве не поближе к Delphi будет?

                          Тут важнее не язык, а область его приложения. Дельфи всегда был заточен под БД и чуток под клиент-серверные технологии, из за чего остался живым именно в .NET. Я делал приложения для работы с базами данных управленческого учёта, аддоны для системы разработки технологической документации (с целью приведения интерфейса ко вменяемому уровню UX), до кучи программировал на полставки промышленные контроллеры в цехе спецстанков и систему сбора статистики со станков стороннего производителя. В общем, полноценная жизнь заводского программиста из спецназа местечкового значения АКА «Группа САПР».

                          В сухом остатке, помимо промышленных контроллеров (это были Мицубиси, а сейчас в тренде Сименс), фронтенд и базы данных.

                          Ушёл уже с должности руководителя группы (в мелкую фирму «главным» инженером) из-за того, что завод стагнировал и никому ничего было не надо. Бюджет отсутствовал («Вам же уже все программы купили ещё пять лет назад»), мы вырождались в группу документооборота электронных копий бумажных чертежей с двумя инженерами чтобы слать их по почте и класть на сервер и двумя студентами чтобы сканировать их (на планшетном сканере А3) и сшивать.

                          А в последнем марте я пробежался по работным сайтам, посмотрел, что мне близко из востребованного в нашей местности — и вот, учу веб-девелопмент, начиная с фронтенда (самое востребованное). Как в работе пауза, так и погружаюсь. Хотя мне, вероятно, был бы более по душе бекэнд, с его развитыми и хорошо структурированными фреймворками. Максимальное удовольствие от работы я получал именно тогда, когда строил универсальные инструменты для решения типовых задач или систематизировал и сериализовал подход к решению задач нетривиальных — и вот, пока я спал, оказалось, что это целый мир…

                          Такое вот «почему».

                          P.S. И хочется же в команду с развитыми понятиями о производстве ПО, где другим небезразличны вопросы организации процесса. В общем, в коллектив, который не является «толпой чёрных ящиков». Фриланса не хочется — это не только оставаться одиночкой (чего совсем не хочется), в пределе это наверняка выльется в узкую специализацию, отлаженный процесс и скуку смертную без дальнейшего развития. К тому же я наработался уже «строительным узбеком», и не хочется превращаться в «компьютерного индуса».
    • 0
      Меня тоже раздражает это, мог бы и автоматом доставить все поля, что в агрегатах не участвуют. Учился SQL когда не было такого требования (хочешь — пиши, не хочешь — не пиши), потом перешли на следующую версию и, помнится, тогда много запросов дописывали.
  • +2
    Можно добавить в статью, что тестирования запросов существует
    sqlfiddle.com
    там можно попробовать запросы на разных БД: MySQL, Oracle, PostgesSQL, MSSQL, SQLite

    P.S. а ещё есть
    eval.in
  • 0
    Спасибо за статью
  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      Каждый раз когда вижу этот «русский код» в голове, как будто, что то подклинивает :)
      • НЛО прилетело и опубликовало эту надпись здесь
        • НЛО прилетело и опубликовало эту надпись здесь
        • 0
          да, я знаю об этом, я про сам вариант описания операторов на русском, и про непривычность относительно повседневного SQL для лично меня .
        • +2

          Конкретно в запросе выше проблема не в русском языке — а в форматировании. Для начала — поставить отступы. Конструкцию "КОГДА… ТОГДА… ИНАЧЕ" лучше было бы записать в одну строку. Вместо капитанского "ВложенныйЗапрос" лучше бы написать что именно этот запрос делает. Или напротив, сократить псевдоним до "ВЗ", чтобы не отвлекал.

          • НЛО прилетело и опубликовало эту надпись здесь
            • +3

              Если строить запрос конструктором — то надо выкладывать не текст запроса, а скриншот конструктора :) Автогенерированный код никому не интересен.


              А выкладывать такую простыню текста без форматирования потому что "это так при копи-пасте получилось сорри" — есть неуважение к читателям.

              • НЛО прилетело и опубликовало эту надпись здесь
              • НЛО прилетело и опубликовало эту надпись здесь
              • НЛО прилетело и опубликовало эту надпись здесь
          • НЛО прилетело и опубликовало эту надпись здесь
          • НЛО прилетело и опубликовало эту надпись здесь
          • НЛО прилетело и опубликовало эту надпись здесь
  • +1
    Низкий вам поклон от новичка, так как только начал заниматься SQL статья лично мне уже многим помогла.
  • 0
    Мне кажется в пункте 6.2 результат запроса не соответствует поставленной задаче.
    Нужно: узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех.
    Получили — книги авторов, чьих книг в библиотеке больше 3.

    Книга, экземпляров которой в библиотеке больше 3, одна. Это Who Will Cry When You Die?
  • 0
    Я один вижу, что в примерах используется ключевое слово INNER, но не объясняется что это такое?
    • +3
      «INNER JOIN» и «JOIN» — это одно и то же («INNER» в данном случае просто игнорируемое слово).

      Объяснять 100%-необходимо было бы, если бы он где-то использовал другие формы JOIN'ов: «LEFT [OUTER] JOIN», «RIGHT [OUTER] JOIN» или «FULL [OUTER] JOIN» — но такого у него нет.

      Хотя сказать пару слов по этому поводу и использовать везде однотипный синтаксис (или JOIN, или INNER JOIN) было бы безусловно лучше.
      • НЛО прилетело и опубликовало эту надпись здесь
        • +2
          И?

          (В смысле, в чём противочение с тем что я писал? Я всего лишь имел в виду, что INNER — это тип джоина по-умолчанию (когда не указано другое).)
          • НЛО прилетело и опубликовало эту надпись здесь
    • НЛО прилетело и опубликовало эту надпись здесь
    • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    А есть ли в каких-то БД какой-нибудь auto join? Например, если я задал в таблице связь между ACCOUNT.ACCOUNT_TYPE и ACCOUNT_TYPES.TYPE_ID, почему я не могу написать просто select ACCOUNT.ACCOUNT_NUMBER, ACCOUNT_TYPES.TYPE_NAME from ACCOUNT, ACCOUNT_TYPES или select ACCOUNT.ACCOUNT_NUMBER, ACCOUNT_TYPES.TYPE_NAME as TYPE from ACCOUNT auto join ACCOUNT_TYPES безо всяких дополнительных on или where?
    • 0
      Можно без всяких on и where — using есть и natural join. Но всё это фигня, ибо связей может быть больше одной.
      • 0
        А что мешает в случае если связей несколько просто выдавать ошибку?
        • +1
          То, что схема со временем имеет тенденцию усложняться. Сегодня из таблицы table1 одна связь на table2, через полгода добавили ещё одну (с другим смыслом). И половина уже отлаженного приложения перестала работать.
        • +1
          У меня была когда-то более здравая мысль — разрешить «ходить» по именованым foreign key'ам (при условии, что они указывают на UNIQUE/PRIMARY KEY).

          Например, в схеме:
          CREATE TABLE universities (
              id serial not null PRIMARY KEY,
              …
          );
          CREATE TABLE faculties (
              id serial not null PRIMARY KEY,
              …,
              university_id int not null,
              CONSTRAINT university FOREIGN KEY (university_id) REFERENCES universities (id)
          );
          CREATE TABLE students (
              id serial not null PRIMARY KEY,
              …,
              faculty_id int not null,
              CONSTRAINT faculty FOREIGN KEY (faculty_id) REFERENCES faculties (id)
          );
          
          вместо
          SELECT s.…, f.…, u.…
          FROM
              students s
                  join faculties f on s.faculty_id=f.id
                      join universities u on f.university_id=u.id;
          
          разрешить писать
          SELECT s.…, s.faculty.…, s.faculty.university.… FROM students s;
          
          • 0
            Немного о наболевшем, на чужом проекте можно столкнуться с такими вещами «FK / PK — не мы этим не пользуемся»
    • 0
      Нет, т.к. тебе могут разные типы соединений понадобится с этими таблицами.
      Например, есть таблица USERS у которой необязательный ключ на Т.ACCOUNT — ACCOUNT_ID
      Запрос 1:
      SELECT *
      FROM USERS
      INNER JOIN ACCOUNT ON USERS.ACCOUNT_ID = ACCOUNT.ID
      INNER JOIN ACCOUNT_TYPES ON ACCOUNT.TYPE_ID = ACCOUNT_TYPES.ID
      

      Запрос 2:
      SELECT *
      FROM USERS
      LEFT JOIN ACCOUNT ON USERS.ACCOUNT_ID = ACCOUNT.ID
      LEFT JOIN ACCOUNT_TYPES ON ACCOUNT.TYPE_ID = ACCOUNT_TYPES.ID
      

      И первый, и второй запросы верные, но выдают разные результаты.
      • 0
        Смысл чтоб ON были необязательными, если ключ присутствует. Т.е., left, inner, full, cross остаются, но без on. Тогда разработчик будет каждый раз чесать репу «а по каким полям?», а сервак мусолить винт «а по каким полям?». Такое может прокатить, но на простых запросах. Проще разработчика разок напрячь, чтоб правильно написал что хочет, ему же потом самому легче будет.
        SQL так-то любит чтоб всё было разжёвано.
        • 0
          А так-то, без ON вполне здравая мысль.
          Разработчику особо дела нет как там эти поля называются, БД может проверить что должен существовать FK в единственном числе и одним концом смотреть на PK и, если уж на то пошло, то IDE может подсказать разрабу что это за FK и какие ключи там есть.
          Так что в целом мысль вполне годная.
          Если я создал FK, то и соединять в 99% случаев буду по нему.
          Лучше бы БД варнинги выдавала на JOIN'ы без FK.
          Хотя тут тоже не однозначно, под «ACCOUNT» может быть накручена такая огромная VIEW

Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.