Понимание джойнов сломано. Это точно не пересечение кругов, честно +83



Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается INNER JOIN от LEFT JOIN.


Чаще всего ответ примерно такой: "inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null". Еще, бывает, рисуют пересекающиеся круги.


Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.


Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.


Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.


Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин "пересечение" только путает.


(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)


INNER JOIN


Давайте сразу пример.


Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.


INSERT INTO table1
(id)
VALUES
(1),
(1)
(3);

INSERT INTO table2
(id)
VALUES
(1),
(1),
(2);

Давайте, их, что ли, поджойним


SELECT *
FROM table1
   INNER JOIN table2
      ON table1.id = table2.id;

Если бы это было "пересечение множеств", или хотя бы "пересечение таблиц", то мы бы увидели две строки с единицами.



На практике ответ будет такой:


| id  | id  |
| --- | --- |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |


Но как??


Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.


CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:


select * from t1;

 id 
----
  1
  2
  3

select * from t2;

 id 
----
  4
  5

Тогда CROSS JOIN будет порождать 6 строк.


select * 
from t1
   cross join t2; 

 id | id 
----+----
  1 |  4
  1 |  5
  2 |  4
  2 |  5
  3 |  4
  3 |  5

Так вот, вернемся к нашим баранам.
Конструкция


t1 INNER JOIN t2 ON condition

— это, можно сказать, всего лишь синтаксический сахар к


t1 CROSS JOIN t2  WHERE condition

Т.е. по сути INNER JOIN — это все комбинации соединений строк с неким фильтром condition. В общем-то, можно это представлять по разному, кому как удобнее, но точно не как пересечение каких-то там кругов.


Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.


LEFT JOIN


Если вы считаете, что левая таблица всегда остается неизменной, а к ней присоединяется или значение из правой таблицы или null, то это в общем случае не так, а именно в случае когда есть повторы данных.


Опять же, создадим две таблицы:


insert into t1 
(id)
values
(1),
(1),
(3);

insert into t2
(id)
values
(1),
(1),
(4),
(5);

Теперь сделаем LEFT JOIN:


SELECT * 
FROM t1
   LEFT JOIN t2 
       ON t1.id = t2.id;

Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.


| id  | id  |
| --- | --- |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 3   |     |

Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.


LEFT JOIN можно переформулировать так:


SELECT * 
FROM t1 
   CROSS JOIN t2
   WHERE t1.id = t2.id

UNION ALL

SELECT t1.id, null
   FROM t1
   WHERE NOT EXISTS (
        SELECT
        FROM t2
        WHERE t2.id = t1.id
   )

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


Условие ON


Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.


Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город


SELECT s.id, c.city 
FROM users_stats AS s
    JOIN cities_ip_ranges AS c
        ON c.ip_range && s.ip

где && — оператор пересечения (см. расширение посгреса ip4r)


Если в условии ON поставить true, то это будет полный аналог CROSS JOIN


"table1 JOIN table2 ON true"  == "table1 CROSS JOIN table2"

Производительность


Есть люди, которые боятся join-ов как огня. Потому что "они тормозят". Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.


Это, прямо скажем, странно.


Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.


Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.


Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не 'LEFT JOIN… WHERE… IS NULL', а конструкцию EXISTS. Это и читабельнее, и быстрее.


Выводы


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


Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста "Цинковый прод". Не забудьте подписаться.


Update. Кто минусует — отпишитесь плиз, почему. Спасибо за фидбэк.

Вы можете помочь и перевести немного средств на развитие сайта

Теги:



Комментарии (190):

  1. little-brother
    /#20039978 / +8

    по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй.

    Потому что так оно и будет в 99% случаев использования (плюс возможно условия на поля, которые можно вынести в where).
    Как впрочем и с дублированием строк. Очень редко нужны такие запросы, когда соединение проходит не по уникальному ключу.

    • kolu4iy
      /#20039988 / +2

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

      • varanio
        /#20039996 / +1

        в целом согласен

        Однако, к примеру join по пересечению диапазонов ip адресов в моей практике встречался очень часто. Так то зависит от специфики проекта.

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

        • kolu4iy
          /#20040016 / +1

          И я с вами, безусловно, не спорю. У самого на работе самописная erp которой больше 20 лет: там физически невозможно все поддерживать красиво.

    • dimaaan
      /#20040070 / +5

      Прямо прочитали мои мысли :)


      Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей

      Вместо того, чтобы молча записывать человека в "неумехи", лучше задать наводящий вопрос.
      Мол, что будет в случае с дубликатами?
      И тогда уже делать выводы.

      • WinLin2
        /#20044658

        А откуда дубликаты id в обоих таблицах?

        • dimaaan
          /#20044676 / +1

          Просто id — это неправильное название в данном случае.
          Сбивает с толку.
          Считайте, там просто колонка с числами.

    • VMichael
      /#20040610 / +1

      Это проблема на самом деле, что люди считают, что раз в 99% случаев (хотя откуда такая статистика?) будет так, то и лепить можно так во всех случаях, не очень понимая правильно ли это.

  2. SerafimArts
    /#20040084 / +1

    А можно, пожалуйста, для тех кто в танке, что означает вот это условие, что вы привели в качестве примера?

    JOIN cities_ip_ranges AS c ON c.ip_range && s.ip


    Я, конечно, посмотрел по ссылочке но всё равно не понял прикола. Мне казалось, что "&&" — это обычный алиас на AND, а значит, перефразируя на русский ON условие звучит так: «где c.ip_range кастуется в true И s.ip кастуется в true», т.е. что-то вроде такого:
    ... ON CAST(c.ip_range AS BOOLEAN) AND CAST(s.ip AS BOOLEAN)
    


    А дальше моя логическая цепочка привела в тупик, так что решил всё же спросить.

    • varanio
      /#20040136 / +4

      В postgresql можно делать свои расширения. По сути, расширение — это набор инструкций, определяющих новые типы данных и операторы для работы со значениями типов


      В данном случае в расширении ip4r определены типы ip4 и ip4r, т.е. ip адрес и диапазон ip адресов. А также оператор &&, который для этих типов определен как "пересечение". То есть, если диапазоны пересекаются, то результат операции будет true


      В данном случае && не имеет отношения к and


      Завтра запилю более подробный пример, щас с телефона неудобно

      • SerafimArts
        /#20040144 / +1

        Понял, это что-то вроде WHERE s.ip IN c.ip_range получается. Спасибо за объяснение.

    • Melkij
      /#20041340 / +1

      Мне казалось, что "&&" — это обычный алиас на AND

      Этого нет в стандарте. Поэтому поведение необходимо уточнять для каждой СУБД. Где-то это будет алиас для AND, где-то вообще не будет. В postgresql — оператор строго зависит от типов данных операндов. Есть create operator и можете сами на некоторую последовательность символов приклеить любую логику, в том числе можно даже переопределить штатные операторы (операторы ищутся тоже в порядке search_path если не указаны через pg_operator синтаксис).
      Например, в чистом postgresql 11 есть 8 разных операторов &&, для разных типов данных операндов.

  3. MarazmDed
    /#20040126 / +13

    Во-первых, таблица — это вообще не множество.

    Во первых, множество.

    По математическому определению, во множестве все элементы уникальны, не повторяются

    Кто вам сказал такую глупость? Множество — это просто совокупность элементов. КАКИХ — зависит от того, что вам надо. В первом приближении всё можно считать множествами.

    а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.

    Во вторых, есть такая штука — реляционная алгебра. И пересечение двух отношений в терминах реляционной алгебры, это и есть inner join. Да, это не про голые множества: отношение — это множество с особыми свойствами, пересечение — вводится именно для отношений, а не как операция над множествами. Но выглядит это не более чем придиркой.

    t1 CROSS JOIN t2 WHERE condition

    можно еще короче: t1, t2 where condition

    Также, похоже, нужно избегать термина «пересечение».

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

    • asilischev
      /#20040152 / +4

      Кто вам сказал такую глупость? Множество — это просто совокупность элементов. КАКИХ — зависит от того, что вам надо. В первом приближении всё можно считать множествами.

      Ну если разбираться глубже, а не поверхностно, то совокупность всё-таки уникальных элементов. Например, вот определение — economic_mathematics.academic.ru/2630/%D0%9C%D0%BD%D0%BE%D0%B6%D0%B5%D1%81%D1%82%D0%B2%D0%BE
      Могу ещё ссылок накидать.

      • MarazmDed
        /#20040170 / +1

        Ну если разбираться глубже, а не поверхностно, то совокупность всё-таки уникальных элементов

        Все дело в том, что считать уникальным. Если вы можете различить единицы, то, например {1, 1, 1, 1, 1} — спокойно может быть множеством. Все что вам нужно — это уметь различать эти единички. Например, по индексу. Вся математика — это по сути надстройка над теорией множеств. Всё есть множества.

        • mayorovp
          /#20040322 / +2

          Но даже в этом случае пересечение двух множеств по два элемента не может дать множество из 4х элементов.

          • MarazmDed
            /#20040346 / +1

            Но даже в этом случае пересечение двух множеств по два элемента не может дать множество из 4х элементов.

            Пересечение МНОЖЕСТВ действительно не может. А пересечение ОТНОШЕНИЙ, как операция, определенная в реляционной алгебре, может запросто.

            • varanio
              /#20040396 / +1

              Пересечение отношений в реляционной алгебре — это тоже не про join. Это про INTERSECT

              • MarazmDed
                /#20040442 / +1

                Внезапно :) Да, действительно. Вы — правы. Перепроверил, пересечение — это не джойн.

        • mikeus
          /#20040378 / +1

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

          • MarazmDed
            /#20040468 / +1

            Ок, дайте тогда определение матрицы, например. Вас сильно смущает, к примеру нулевая матрица? Там одни нули. Но вы их можете различить.
            Вы не путайте, что относится к самой математике, а что — к ее применению. В определении множества есть требование к различимости элементов. Но нет требования к равенству. Накладываю на множества дополнительное свойство: упорядоченность. И спокойно так различаю одинаковые, на первый взгляд, элементы.

            • mikeus
              /#20040536 / +1

              Ок, дайте тогда определение матрицы, например. Вас сильно смущает, к примеру нулевая матрица? Там одни нули. Но вы их можете различить.
              Вы хотите сказать что вы лично различаете например значение ноль элемента (1,1) и ноль в элементе (2,2) матрицы?

              • vladkorotnev
                /#20040788 / +1

                Видимо имеется в виду, что если представить, что каждый элемент матрицы на самом деле не просто число, а что-то типа
                struct {
                long val;
                uint x;
                uint y;
                } mat_el_t

                от которого «на бумагу» выведен только val, то таки да, каждый из этих mat_el_t элементов будет уникальным.

                Или, например, можно это представить как матрицу
                A = [ x11 x12 x13; x21 x22 x23; x31 x32 x33 ] где x11 = x12 = x13 = x21 = x22 = x23 = x31 = x32 = x33 = 0;

                • mikeus
                  /#20045340

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

                  Если мы возьмем нулевую матрицу 3х3, то множество значений val будет V={0}, множество элементов mat_el_t, определяющих эту матрицу будет A={x11, x12, x13, x21, x22, x23, x31, x32, x33}.
                  Добавив ещё один ноль в множество V={0} никто не получит какое-то иное множество, оно останется таким же = {0}. Точно также, добавив ещё один элемент х11 в множество A, даже если начать теперь всегда скрупулёзно выписывать, что A={x11, x11, x12, x13, x21, x22, x23, x31, x32, x33}, никто не получит множество, определяющее какую-то другую матрицу чем та, что определялась до этого.

                  Ок, дайте тогда определение матрицы, например. Вас сильно смущает, к примеру нулевая матрица? Там одни нули. Но вы их можете различить.
                  Вы не путайте, что относится к самой математике, а что — к ее применению. В определении множества есть требование к различимости элементов. Но нет требования к равенству. Накладываю на множества дополнительное свойство: упорядоченность. И спокойно так различаю одинаковые, на первый взгляд, элементы.
                  Если теперь в множестве A={x11, x11, x12, x13, x21, x22, x23, x31, x32, x33} начать различать один x11 от другого, то мы получим что на самом деле в множество был добавлен элемент, который отличается от всех остальных элементов, которые были в нём до этого, т.е. что был добавлен не x11, что противоречит изначальному действию.

        • 0xd34df00d
          /#20040802 / +1

          Если вы можете различить единицы, то, например {1, 1, 1, 1, 1} — спокойно может быть множеством.

          Значит, это будет не очень корректной записью.


          Если вы хотите различать элементы, значит, в вашей нотации вы потеряли информацию, которую вы используете для различения.


          Если вы различаете элементы по индексу, то у вас не множество S, а функция Nat > S (ну или I > S для произвольных индексирующих множеств, чего мы как первоклассники). А вы выписали её кодомен и теперь удивляетесь, что вас не понимают.


          Если вы не различаете элементы по индексу, но умеете считать их количество (мультимножество, ага), то у вас функция S > Nat (или S > Nat\{0}, есть варианты).


          Да, если вспомнить, что такое функция из A в B (подмножество A?B с очевидным дополнительным условием), то да, это тоже множества, конечно же. Но другие.


          Вся математика — это по сути надстройка над теорией множеств. Всё есть множества.

          Даже классы объектов в категориях, не являющихся малыми, или Hom-классы в категориях, не являющихся локально малыми?

          • badunius
            /#20041816

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

            Позвольте, но ведь последовательность записи (положение в списке) — это информация, и она никуда не терялась.

            • Fenzales
              /#20043634 / +1

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

              • badunius
                /#20043774 / -1

                Хм, нет, простите, у меня на уме был пример индексного буфера. То есть, если я рендерю «ромашку» отдельными треугольниками, то я передаю массив вершин, которые уникальны и массив индексов, которые уже не уникальны, но при этом положение индекса в массиве имеет решающую роль.

                • mayorovp
                  /#20043842

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

            • 0xd34df00d
              /#20043654

              Фигурные скобки используются для того, чтобы подчеркнуть независимость от положения в списке. Понятие множества — действительно достаточно базовое для того, чтобы не догружать интерпретацию ещё какими-то индексами, положениями в списке (что вы будете делать с несчётными множествами, в конце концов?) и прочим подобным.

              А так вы, тащем, вольны решить, что множество { 1, 2, 5 } вы записываете как { 1, 1, 3 } (надеюсь, идея такой записи понятна), но зачем, если есть общепринятая стандартно понимаемая нотация?

              • badunius
                /#20043792

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

        • LonelyDeveloper97
          /#20043898 / +1

          Для того, что вы пытаетесь описать, существует специальный термин — Кортеж.
          Кортеж — это упорядоченный набор элементов.

          Я попробую внести ясность во всю эту дискуссию.

          Первое. Автор поста прав в утверждении, что множество не может содержать повторяющихся элементов. By defenition.
          И запись {1,1,1} — действительно некорректна, если {} — обозначает множество, то это множество может быть ТОЛЬКО {1}

          Второе, из того, что автор прав в предыдущем пункте не следует, что он сделал правильный вывод. Таблица — это множество. Но не совсем обычное.

          На основе понятия «множество», можно построить другие объекты, которые так же будут являться множествами, но при этом иметь другие свойства. С их помощью и можно описать таблицу.
          Например, уже упомянутое мультимножество, в котором элементы имеют кратность. Для нашей задачи оно не подойдет, в таблице важен порядок.

          Однако, еще немного математических преобразований позволяют получить такой объект как "кортеж". Кортеж — это упорядоченная последовательность элементов. И да, он может быть выражен используя только определение множества, и сам является множеством. Обозначение: "()", и судя по всему это именно то, что вы пытались выразить своим примером. Правильная запись «различных единиц» будет выглядеть как: (1,1,1,1)

          Частным случаем кортежа является такой объект как "пара" — это просто кортеж из двух элементов.
          На примере пары можно показать как получается упорядоченность. Пара (a,b) выражается через множество как { {(a)}, {(a), b} }, где (x) = {{/}, {/, x}}, {/} — пустое множество. Соответственно пара (b, b) -> { {(b)}, {(b), b} }. В русской википедии на этом месте ошибка, но можно глянуть в английскую.

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

          В таблице есть строки и столбцы.
          У каждого столбца есть имя, например «id», «name». Если вы хотите взять значения столбцов по определенной строке то вы получите набор пар:
          ((id: 1), (name: «Vasya Pupkin»)) — строка таблицы.
          Но разумеется, у нас много строк, и их порядок важен. Поэтому в целом таблицу можно выразить как кортеж кортежей пар (название столбца, значение):

          (
          ((id: 1), (name: «Vasya Pupkin»)),
          ((id: 2), (name: «Nikita Twink»)),
          ((id: 10), (name: «Petya Petechkin»))
          )
          И да, этот кортеж все еще можно выразить с помощью одного только понятия «множество». Но правильная запись займет весьма значительный объем (почему — можно узнать в уже упомянутой статье Tuples, английской википедии).

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

          • 0xd34df00d
            /#20043930 / +1

            Автор поста прав в утверждении, что множество не может содержать повторяющихся элементов. By defenition.

            Чисто техническая ремарка: это не по определению множества, а по построению языка теоири множеств. Операции над множествами не умеют считать элементы, поэтому это просто бессмысленный вопрос.

          • Rsa97
            /#20044138 / +3

            Но разумеется, у нас много строк, и их порядок важен.
            На момент выполнения запроса порядок строк неважен. Да и вообще, сам по себе SELECT без ORDER BY никакого определённого порядка не гарантирует.

            • LonelyDeveloper97
              /#20044334

              Т.Е. у строк нет какого-нибудь rowNumber, по которому ее можно выбрать не зная о ее содержании?
              Я просто очень давно не работал с таблицами.

              Тогда вы правы и у них нет «порядка», но из этого будет следовать неразличимость двух строк с одинаковыми значениями. Если это так — описывать их кортежем избыточно, можно использовать мультимножество.

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

              Таким образом в случае когда порядок не важен (т.е. мы не можем сказать «столбец номер такой-то» и «строка номер такая-то») получим мультимножество множеств пар.

              • Rsa97
                /#20044404 / +2

                Т.Е. у строк нет какого-нибудь rowNumber, по которому ее можно выбрать не зная о ее содержании?
                Нету. Также, как нету отношений «следующая» или «предыдущая» строка и понятий «первая» и «последняя» строки. Это первая нормальная форма.
                1. Нет упорядочивания строк сверху вниз (другими словами, порядок строк не несет в себе никакой информации).
                2. Нет упорядочивания столбцов слева направо (другими словами, порядок столбцов не несет в себе никакой информации).
                3. Нет повторяющихся строк.
                4. Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).
                5. Все столбцы являются обычными (не скрыты от пользователя и не содержат каких-либо данных, доступных только по специальным функциям).

          • xitt
            /#20044266

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

    • varanio
      /#20040180 / +1

      дело даже не в этом. Бог с ними со можествами. Больше бесит пересечение, особенно с кругами Венна

      • MarazmDed
        /#20040204 / +1

        Больше бесит пересечение, особенно с кругами Венна

        Вот и стоит уточнить, слышал ли испытуемый про реляционную алгебру, и в каком смысле — пересечение.
        ИМХО, круги Венна можно оставить, но внутри них допустимы только названия таблиц, а не элементы-цифирьки. Но только для inner join'а. Как наглядно и просто нарисовать в виде картинки, тот же left join — я не знаю.

        • varanio
          /#20040402 / +1

          Пересечение кругов и пересечение отношений в рел алгебре иллюстрируют sql-оператор INTERSECT, а не join

          • MarazmDed
            /#20040476 / +1

            Да, это так. Ну т.е. получается, что нет лаконичных способов нарисовать все джойны в виде кружочков. Только таблички из двух строк и двух столбцов.

      • Fezzo
        /#20041814 / +6

        Диаграмма Венна отлично показывает какие значения попадут в результат, а не сколько их будет. ИМХО именно в этом и состоит главное отличие этих видов джойна. Именно поэтому, первое что отвечают — это круги и пересечения. Если вас интересуют детали про дубликаты, никто не мешает задать дополнительный вопрос. Люди не склонны на простые вопросы типа «Что такое inner join» отвечать полной математической справкой по теме, а предоставлять самое важное и часто используемое.
        По аналогии с вашим постом можно удивляться, что люди на вопрос «сколько будет 2х2?» отвечают «4» (в общем случае это, конечно же, не так), и говорить что «понимание умножения сломано»

    • Pro100Oleh
      /#20040192 / +1

      Кто вам сказал такую глупость? Множество — это просто совокупность элементов. КАКИХ — зависит от того, что вам надо. В первом приближении всё можно считать множествами.

      Тогда что получается: даны множества A={a, a, b} и B={a, b}. Думаю не будете спорить что a принадлежит A. Также А принадлежит B, а B принадлежит A (потому что каждый елемент левого множества принадлежит правому). Отсюда A=B. Имхо бессмысленная возможность иметь множества с не уникальными элементами.

      • MarazmDed
        /#20040214 / +2

        Отсюда A=B. Имхо бессмысленная возможность иметь множества с не уникальными элементами.

        вопрос в том, можете ли вы в множестве A различить первую a и вторую. Если можете, то никаких парадоксов нет: A не является подмножеством B. И множества не равны.
        Если не можете, то у вас, по факту, множество A={a, b}

        • Pro100Oleh
          /#20040230 / +1

          Как только я начинаю «различать» элементы, то они перестают быть уникальными. Мы же продолжаем говорить о математике а не о своем понимании что такое уникальные элементы?

          • MarazmDed
            /#20040300 / +1

            Как только я начинаю «различать» элементы, то они перестают быть уникальными.

            Бинго! :) В точку! И это сразу решает все проблемы.

            а не о своем понимании что такое уникальные элементы?

            А что такое «уникальные элементы» в математическом смысле?

          • khim
            /#20040756 / +2

            Господи, сколько шума из ничего. В математике есть понятие мультимножества. И да, вокруг них — тоже есть теория. Если хотите смотреть как работают JOIN'ы в таблицах с повторами — вам нужна теория мальтимножеств, а не множеств. Вот и всё.

            • mayorovp
              /#20041090

              Вот только пересечением мультимножеств {1, 1} и {1, 1} будет тоже {1, 1}, а не {1, 1, 1, 1} которые дает соединение

    • nfw
      /#20041234

      Множество — это просто совокупность элементов. КАКИХ — зависит от того, что вам надо. В первом приближении всё можно считать множествами.

      И каким же образом в произвольном множестве вы будете отличать один элемент множества от другого, если они, буквально, одинаковые?


      Во первых, множество.

      Да, вот только таблица не простое множество, а упорядоченное, то есть, например, вот таблица {1,1,1} это не множество цифр, а множество двоек {[1,1],[2,1],[3,1]}.

      • Ndochp
        /#20043452

        У упорядоченных множеств {1, 2, 2, 2, 3} и {2,3,4} пересечение есть? если это множество двоек, то нету. Если пересечение {2, 3}, то я не знаю как это описать.

        • 0xd34df00d
          /#20043682 / +1

          Если вы имели в виду мультимножества, то пересечение { 2, 3 }.


          Если вы имели в виду множества с заданным на них отношением порядка, то тоже { 2, 3 } (независимо от этого отношения, естественно).

    • Druu
      /#20041896 / +1

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

      А в отношении одинаковых кортежей все равно не бывает.

    • devil_oper
      /#20043024 / -1

      пересечение двух отношений в терминах реляционной алгебры, это и есть inner join


      Дед, ты в маразме. Для join в реляционной алгебре есть операция соединения (сюрприз-сюрприз!), которая эквивалентна операции выборки из декартова произведения двух отношений (то есть множеств кортежей).

  4. MarazmDed
    /#20040154 / +4

    Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.

    Если чел — слабо разбирается в SQL и плохо представляет себе, что будет делать СУБД, а что такое план запроса — вообще не слышал. То в такой ситуации, как мне кажется, правильнее будет пихать все в джойны и считать, что СУБД умнее программера. Правило — эвристическое, но для новичков — работает :)
    Это явно будет лучше, чем тысячи мелких запросов внутри цикла, или ручное склеивание таблиц средствами языка.

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

    • littorio
      /#20041244 / -1

      Союз «если… то» вроде нельзя разбивать точкой на два предложения. Или я чего-то путаю?

  5. pilot911
    /#20040166

    некоторые не знают, что после ON можно писать и AND, что, судя по опыту на Mysql, ускоряет в некоторых случаях запрос
    ```
    SELECT s.id, c.city
    FROM users_stats AS s
    JOIN cities_ip_ranges AS c
    ON c.ip_range && s.ip AND s.ip > 2
    ```

    • Neikist
      /#20041162 / +2

      Главное не забывать что это имеет смысл только в inner join чаще всего. Я по первости бывало забывался и в left join тоже внутрь join условие добавлял вместо where.

    • mayorovp
      /#20041174 / +2

      Этот опыт только на Mysql и переносим. СУБД с вменяемым оптимизатором запросов не видят разницы между условием в inner join и условием в where; одно из них ну никак не может работать быстрее другого.

      • Neikist
        /#20041470

        Хм, странно, сейчас замерять не смогу, с 1с больше не работаю, да и вообще с субд, но это вполне распространенная рекомендация среди 1сников для оптимизации. В их файловой субд скорее всего это будет работать, вряд ли там оптимизаторы хитрые есть, но вроде и для ms sql на которой в основном 1с запускают тоже советуют.

        • LMSn
          /#20041690

          Ничего странного. В MS SQL ничего подобного нет, там планировщику без разницы где указано условие. В больших запросах разница очень теоретически может возникнуть на этапе построения плана запроса (два немного разных запроса пойдут по немного разному пути и имеют шансы получить разные планы выполнения из-за ограничения времени на построение плана). Но тут никогда нельзя утверждать, что какая-то конструкция языка будет однозначно быстрее аналогичной. Что там в mysql творится — не знаю, говорю за Oracle, MS SQL.

        • kolu4iy
          /#20041708

          но вроде и для ms sql на которой в основном 1с запускают тоже советуют.

          А зря. Там и MAXDOP=1 советуют, не вникая в суть. Что тоже зря (это вырожденный случай, годный для определённого количества ядер процессора).

      • Nashev
        /#20054872

        Это не так. Например, условие в join не считает поля отсутствующей записи правой таблицы равными null, потому что join на момент его работы ещё не выполнен, и условие примеряется к имеющимся строкам объединяемых джойном таблиц. А условие в where примененяется к сделанной выборке, после отработки всех join, то есть поля, выбранные из второй таблицы в тех строках, где подходящая строка во второй таблице не нашлась, заполнены значением null.


        Это логика SQL, и любой оптимизатор, даже если он выполняет условия в другие моменты времени и в другом контексте, должен эту логику сохранять.

        • mayorovp
          /#20055170

          Это вы поведение left join описали, а тут исключительно inner join обсуждается. По крайней мере, я про него писал.

  6. Hooters
    /#20040210

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

    То есть, множество эквивалентных элементов — это уже не множество?
    К вашему сожалению, в понятии множества нет такой характеристики, как уникальность.

    • varanio
      /#20040212 / +1

      Спасибо. Дописал update к статье

    • Tzimie
      /#20040232 / +3

      Формально определение равенства множеств (в теории ZFC и других) не различает множества, имеющие поповторяющиеся элементы, то есть:


      { x } = { x, x }


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

      • MarazmDed
        /#20040308 / +1

        но с точки зрения выводов теории это не имеет практического смысла

        Это имеет практический смысл с точки зрения построения других математических конструкций. Например, нуль-мерные пространства — тоже не имеют практического смысла, но тем не менее существуют. И что?

        • Tzimie
          /#20040360 / +1

          Не имеет вообще никакого смысла, так как в отличие от нульмерных пространств никаких новых выводов в ZFC не появляется


          Кстати, раз заинтересовались, теория множеств — это теория первого порядка без равенства (surprise, surprise)

      • eteh
        /#20040336 / +1

        с точки зрения БД уникальность кортежа это важное значение

        • MarazmDed
          /#20040356 / -1

          Хотите сказать, что СУБД БЕЗУСЛОВНО запрещает иметь таблицы с одинаковыми кортежами? Т.е. для этого не нужно определять первичные ключи, например? ;)

          • eteh
            /#20040364 / +1

            я вообще то хотел сказать обратное, но ладно…

          • lorc
            /#20043176 / -1

            Первая нормальная форма реляционной БД. Одно из условий — нет повторяющихся строк. Так что да, теория запрещает совершенно одинаковые кортежи в таблице. Потому что непонятно как их отличать друг от друга.

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

            • jevius
              /#20043214

              Ошибся, отредактировано

            • akryukov
              /#20043238

              Первая нормальная форма реляционной БД. Одно из условий — нет повторяющихся строк. Так что да, теория запрещает совершенно одинаковые кортежи в таблице.

              1NF не про дубликаты кортежей, а про то, что в атрибутах мы не храним массивы данных.

              • lorc
                /#20043302 / +1

                Вы как-то узко понимаете 1NF:

                According to Date's definition, a table is in first normal form if and only if it is «isomorphic to some relation», which means, specifically, that it satisfies the following five conditions:[12]

                There's no top-to-bottom ordering to the rows.
                There's no left-to-right ordering to the columns.
                There are no duplicate rows.
                Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
                All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].


                Плюс, нужно отличать 1NF для отношения ( на самом деле отношение всегда в первой нормальной форме) и 1NF для таблицы, как способа записывать отношения.

                • mayorovp
                  /#20043320

                  Ну, определение отношения таки не мешает хранить в колонке структурированные данные, вроде массивов, объектов или таблиц, так что отношение не в 1NF тоже бывают.

                  • lorc
                    /#20043412

                    Не совсем так. Вообще, с атомарностью возникают вопросы.

                    Например, тот же IP-адрес — это на самом деле структура из 4 октетов. Должны ли мы хранить их в отдельных колонках?

                    Если для в нашем домене какая-то структура рассматривается как атомарный объект — то его теоретически можно хранить в одной колонке. Например — мы в таблице логируем ответы от сервера. Сервер отвечает каким-то json-нами, но нам совершенно не интересно что внутри этого json. Поэтому мы запихиваем его в колонку и не паримся. И это не нарушает 1NF. Ровно до тех пор, пока мы не захотим обращаться к элементам внутри этого json.

                    • Melkij
                      /#20043502

                      Например, тот же IP-адрес — это на самом деле структура из 4 октетов.

                      Это одно 32-битное поле. См. структуру заголовка пакета.
                      Отображение в виде 4 октетов в десятичной записи сделано исключительно для чтения человеком.

                      • lorc
                        /#20043692

                        Да, согласен. Разделение на 4 октета — довольно таки условно.

                        Тогда вот вам другой подход — до введения CIDR из IP-адреса можно было однозначно выделить адрес сети и адрес хоста. Т.е. внутри IP-адреса все-таки была структура.

                        Ну или в качестве примера можно взять Ethernet MAC-адрес. Или GUID. С одной стороны для большинства применений — это просто цепочка октетов и пофиг что там внутри. Но с другой стороны — они таки имеют внутреннюю структуру. Соответственно, если применять 1NF бездумно и настаивать на полной атомарности данных — то их всегда надо хранить в отдельных полях.

                        • funca
                          /#20045146

                          Реляционная модель это логическая модель. Что считать ароматным зависит от того, какими мельчайшими объектами вы собираетесь оперировать. Для разных задач выбор может быть разным.

                          • lorc
                            /#20045364

                            Да, именно об этом я и говорил.

      • funca
        /#20040406 / +1

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

        • 0xd34df00d
          /#20040808 / +1

          Почему это мутная?


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

          • Tzimie
            /#20041122

            Это зависит от того, как числа задаются
            Вы наверное про бесконечнозначное представление

            • mayorovp
              /#20041140

              Это ничего не меняет. Если у нас конечнозначное представление — то мы точно так же не можем понять равны числа на самом деле или нет.

              • Tzimie
                /#20041148

                Почему? Задаем число бесконечными строками где первый символ равен f, если число иррациональное (после него идут цифры)

                f3.1415926…

                или r если рациональное (далее идут a/b)

                r100/777

                Очевидно рациональные числа сравниваются за конечное время хотя в общем виде конечно это не так

                • mayorovp
                  /#20041178 / +1

                  Ну вот и всё. Даже если забыть о том, что ваше представление требует бесконечной памяти, вы никак не сможете за конечное время доказать что f3.1415926… = f3.1415926…

                  • funca
                    /#20045126

                    Вот поэтому вместо равенства используется аксиома выбора. Возможность отличать один элемент от другого и вытаскивать по одному просто постулируется. Как это будет реализовано для конкретных объектов уже ваша проблема.

                    • 0xd34df00d
                      /#20045228

                      Это не имеет отношения к вопросу о различении одинаковых вычислимых вещественных чисел.


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

                      • funca
                        /#20050238

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

                        • 0xd34df00d
                          /#20051620

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

                          • Druu
                            /#20052080

                            Я просто беру целые числа с умножением (как моноид) и обозначаю рациональные, например, как группу Гротендика.

                            Нельзя так, ноль же.

                    • mayorovp
                      /#20046122

                      Постулировать можно только само равенство, но не разрешимость проверки равенства.

      • 0xd34df00d
        /#20040806 / +1

        Осталось определить понятие равенства.


        Ща ещё немножко пообсуждаем и до гомотопической теории типов дойдём.

        • Tzimie
          /#20041114 / +1

          Оно давно определено
          Я тут писал habr.com/ru/post/445904
          (раздел «Малоизвестные факты»)

          Сорри за занудство про теорию множеств, но сами понимаете, в интернете ктото неправ, я вынужден действовать)

          • 0xd34df00d
            /#20043732

            Ну, если говорить формально, то ZF — это просто набор формул в логике первого порядка. Как вы там интерпретируете значок = — это просто вопрос, э, интерпретации.


            При этом, естественно, легко придумать интерпретации, в которых интерпретация значка равенства не означает интуитивно понятное нам равенство — можно просто взять и размножить конструкцией типа той, что в теореме Левенгейма-Сколема о повышении мощности.


            И это я ещё не говорю о вопросах равенства, если вы начинаете вводить в теорию некоторый вычислительный компонент и обмазываться definitional equality, propositional equality, extensional equality...

            • Tzimie
              /#20045220

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

              • 0xd34df00d
                /#20045234

                От этого платонизма потом всякие парадоксы вылезают. Значки, только значки, ничего, кроме значков!


                Хотя вдохновляться и затягиваться семантикой прикольно и приятно, кто ж спорит.

  7. Pro100Oleh
    /#20040274 / +1

    Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.

    На сколько я помню, то теория рел. БД говорить что таблица — это множество кортежей. Разные кортежи могут хранить одинаковые значения и тогда это эквивалентные кортежи, но все же разные. Поэтому нет никакого противоречия, так как все элементы таблицы уникальны по определению.

    • eteh
      /#20040348 / +1

      Ну в целом да — при значении кортежа =1, Ваша 1 условная тонна никак не превратится в условную единицу времени кроме алгебраических преобразований, если говорить проще.

  8. kgbplus
    /#20040276 / +1

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

    • Tzimie
      /#20040304 / +1

      См мой комментарий выше

  9. funca
    /#20040374 / +1

    Что вас заставило сомневаться в том, что люди, приходящие на собеседования, могут быть образованнее вас?


    Декартово произведение (Cartesian product, cross join) в реляционной алгебре и в теории множеств работают немного по-разному. Но на уровне логики (формальной) смысл операций один и тот же. Поэтому круги Венна читаются однозначно, даже на уровне деталей, если есть понимание в рамках какой модели строятся рассуждения.

    • asilischev
      /#20040412 / +1

      Что вас заставило сомневаться в том, что люди, приходящие на собеседования, могут быть образованнее вас?

      Интересно, где вы это в статье увидели? Между строк?
      Надо вдумываться в то, что говоришь на собеседовании, а не повторять бездумно заученные фразы. Inner Join явно не «пересечение 2-х множеств». Пример в статье это показывает.

    • varanio
      /#20040440 / +1

      Вы с таким пафосом это написали.

      Вот честно, не представляю, как кругами можно кому-то объяснить декартово произведение. Об этом и статья.

      Кругами хорошо показывать пересечение отношений (intersect в sql), а не декартово произведение (join в sql)

      • koropovskiy
        /#20043284 / -1

        Как часто вам в работе нужно декартово произведение?
        По моему опыту:
        INNER и LEFT джойны нужны почти всегда.
        RIGHT JOIN нужен редко.
        FULL OUTER нужен редко.
        CROSS не нужен почти никогда, даже NATURAL JOIN нужен чаще чем CROSS.

        • akryukov
          /#20043366

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

          • koropovskiy
            /#20043456

            Но подождите. В HIVE вообще не SQL. В SQL для интервала есть BETWEEN. для битовой маски сходу не придумаю, не сталкивался по работе с подобными JOIN, обычно это уже были обычные фильтры.

            • akryukov
              /#20044034

              В HIVE вообще не SQL.

              В составе Apache HIVE есть SQL диалект, который может работать на одном из трех движков. Наличие движка, минорных особенностей синтаксиса вроде "нельзя применять опрацию > в ON" еще не значит, что там "вообще не SQL".

        • smbsmn
          /#20054676

          По опыту:
          right join используется, когда раньше был inner, но ситуация изменилась
          full join — реальная ситуация:
          1. например, нужно для одного клиента с разных счетов суммы с баланса вывести
          2. или взять остатки товара с розницы и со склада
          cross join — есть банда кустомеров, для них есть 1 тариф и всё такое

  10. a0fs
    /#20040398 / +2

    «И всё-таки она вертится»(с). Отношение есть множество картежей. Привёденная ссылка на определение это подтверждает. Приведённый пример — просто занятная штука для вопроса на сообразительность и глубину понимания процесса. Обычно в отношениях в БД не хранится хлам навалом, и отношение имеет первичный ключ, по которому происходит объединение. Попробуйте определить первичный ключ и провести свои опыты, вы удивитесь, но СУБД не даст завести два одинаковых значения для первичного ключа.

    Да полностью согласен, что нужно СУБД довести до состояния строго учителя математики, который лупит по рукам железной линейкой за каждый шаг в неверном направлении, но люд нынче пошёл нежный, и он сразу взвопит, что его унижают, лишают свободы, а после этого развернёт знамёна и уйдёт на монгу… Нет ну правда, СУБД имеют дело именно с множествами, просто в случае плохо организованной базы, СУБД не может различить одну единицу от другой, поскольку она не в курсе, что делают одинаковые данные в разных записях одной таблицы. Она милостиво делает по этому безобразию декартово произведение и выдаёт это пользователю, в надежде, что тот сможет достучаться до печени DBA, с целью приведение схемы в божеский вид. Уважаемого автора устроило бы падение базы с дампом памяти по типу деления на ноль?

    • mayorovp
      /#20041120

      JOIN идет далеко не всегда по первичному ключу, и не всегда есть возможность это исправить. Если точнее, то если у схемы данных различаются пятая и шестая нормальные формы — то как ни крутись, всегда будут запросы с соединением не по ключу.

      • MarazmDed
        /#20041712

        JOIN идет далеко не всегда по первичному ключу,

        Я более того скажу — не всегда это нужно. Например, иногда я хочу, чтобы в результате джойна записи «размножились». Например, у меня есть таблица номенклатуры и характеристик. И я хочу по какому-нибудь правилу получить все допустимые пары номенклатура-характеристика.

  11. Xandrmoro
    /#20040528 / +2

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

    • Kant8
      /#20040614 / +1

      Так с задачами то проблемы, каждый запрос надо рассматривать отдельно. А в общем виде правило достаточно тривиально — всё, что проверяется в запросе и имеет высокую селективность, должно быть помазано индексом. Ну и что порядок колонок в индексе имеет основополагающее значение.
      Дальше чаще всего оптимизатор сам разберется, разве что с покрывающими индексами по крайней мере SqlServer любит перебдеть сильно в своих хинтах о индексах.

  12. dasFlug
    /#20040544 / +2

    Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».

    Круги Венна прекрасно объясняют суть, не надо от них отказываться. Но они про отношения в терминах реляционной алгебры, а не про некие абстрактные множества. Отношения конечно тоже множества, но с многими дополнительными свойствами. Если про это забыть то получается некий беспорядок про который вы пишете. Если же говорить про множества то Join это их умножение, а не пересечние. Остальное — inner, outer, on это, как вы правильно пишете, синтаксический сахар добавленный в язык SQL из практических соображений. Всетаки парсеры и оптимизаторы запросов не настолько умны чтобы эффективно превратить ветхозаветное но строго по Кодду
    select t1.id,t2.id 
    from t1,t2
    where t1.id=t2.id
    union
    select id, null 
    from t1 where id not in (select id from t2)
    

    в красивое и которое еще и подскажет оптимизатору запросов чего программист на самом деле хочет
    select t1.id,t2.id 
    from t1
    left join t2 on t1.id=t2.id
    

    но лет 20 назад приходилось писать по первому варианту или использовать специфический синтаксис конкретной СУБД. Вот для Oracle например
    select t1.id,t2.id 
    from t1,t2
    where t1.id=t2.id(+)
    


  13. sergyx
    /#20040638 / +1

    По-моему, лучшее объяснение джоинов дано в Википедии.

  14. DimonSmart
    /#20040910 / +1

    В примерах в таблицах есть неуникальные значения поля id. IMHO, Неудачно выбрано название.

  15. kznalp
    /#20041064

    Во-первых, таблица — это вообще не множество

    А что же это? C математической точки зрения?

    По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так

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

  16. kznalp
    /#20041092

    У вас ошибка в первой иллюстрации.
    Должно быть так

    • varanio
      /#20041102 / +2

      хорошая попытка, но нет )

      дело в том, что если единичек будет по 3 штуки, то в пересечении должно быть 9 штук
      а если по одной, то только одна

      • kznalp
        /#20041110 / -2

        При чем тут попытка? Попытка чего?
        У вас просто начальная посылка неверная, а из ложной просылки любой вывод ложен.

      • Evir
        /#20042598

        Если диаграммой, то скорее правильно будет вот так:

        Диаграмма
        image

  17. lany
    /#20041180 / +2

    Думал, это перевод старой статьи Лукаса. Оказалось, что нет.

    • arch1tect0r
      /#20041656

      Спасибо, кинул к себе в закладки. Для объяснений кому-то сделано идеально.

  18. greabock
    /#20041336 / +2

    Но как??

    Специально сейчас заморочился, поспрашивал. Я не знаю, кого вы там собеседуете, но ни у одного из опрошенным мной моих знакомых (кто хоть сколько-то имел практики в sql) не возникло сомнений, что записей будет четыре. И ни один из них даже не слышал про какие-то там "пересечения множеств".

    • inspector1985
      /#20041806 / -1

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

      • greabock
        /#20042512

        Мы же говорим понятии «пересечения множеств» применительно к соединению таблиц. Я думал, что из контекста это и так понятно.

        P.S Минус не я поставил.

  19. arch1tect0r
    /#20041620

    varanio, как же я тебя понимаю. Через меня прошло три десятка кандидатов с якобы знанием SQL. У нас это плюс, нежели прям требование, но если кандидат утверждает, что знает, то задаю буквально пару вопросов. Один из вопросов как раз про отличие LEFT от INNER и почти никто не отвечает, хоть с кругами, хоть без них.

    • Sunny-s
      /#20044636

      Плюсую. Знание SQL очень хромает. Редкий кандидат доживает до having. До оконных функций добираются единицы.

  20. GooG2e
    /#20041668

    Может буду не прав, но если уходить от каких-то ассоциаций, то все join'ы так или иначе надо рассматривать как декартово произведение, на которое накладывается определённый набор условий в зависимости от типа.
    Если использовать для объяснения, то мне кажется объяснить один термин «декартово произведение» на порядок проще чем объяснять отдельные типы join'ов

    • Gritsuk
      /#20044398

      В гайде по SAS, например, оно прямо так и объясняется — сначала умножаем, потом фильтруем

  21. konstantin_berkow
    /#20041802 / +3

    В начале подумал что это вольный пересказ Say NO to Venn Diagrams When Explaining JOINs. Там и визуализация джоинов есть.

  22. Milein
    /#20041820 / +1

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

    Но есть же простое и корректное объяснение. Джойн это как таблица умножения (которая в виде квадрата), но результат записан в виде пары чисел. Ключ первой таблицы это строки, другой — столбцы.
    Вся таблица это CROSS JOIN.
    Таблица, где мы затем добавили условие и взяли только те комбинации где оно выполняется — INNER JOIN.
    LEFT/RIGHT объяснить сложнее, но тоже можно. Из строки стираем значение и имеем строку, в которой есть пара число + пустое место. И мы берём результаты соответствующие фильтру и результату где с одной стороны пустое место.

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

    Вроде бы понятно должно быть и однокласснику, так что зачем городить диаграмы венна?

  23. KpuTuK
    /#20041832 / +1

    Спасибо! Позновательно

  24. igrishaev
    /#20041850

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


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


    Ну и добавлю, что у кандидатов действительно проблемы с сырым sql после всяких Джанго-ОРМ.

  25. staticlab
    /#20041924 / +4

    Как объяснить на картинке джойны корректно, я, честно говоря, не представляю.

    А если так?


    INNER JOIN

    • VMichael
      /#20042030 / +2

      Эти картинки запутывают, на мой взгляд.
      Понятнее давать две таблички на входе и результат на выходе.

      • staticlab
        /#20042132

        А что именно запутывает?

        • akryukov
          /#20042216

          На мой взгляд, не хватает легенды.

        • VMichael
          /#20042972

          Не знаю как сюда картинки вставлять.
          -1 что означает в квадратике?
          или
          1- это что значит?
          Четыре квадратика 11 что означают?
          В общем на вопрос, какой результат вернет INNER JOIN ответа эти квадратики не дают.
          Нужно додумывать, придумывать, что для пояснения плохо.

          • staticlab
            /#20043080

            В каждой ячейке данные в формате "[t1.id] [t2.id]", при этом прочерк означает NULL. Четыре квадратика "1 1" собственно дают 4 строки результата.

            • VMichael
              /#20043464

              Вот видите, нужно интерпретировать как то картинки, правила запоминать.
              Гораздо проще просто показать примеры с результатом выполнения, можно с комментариями не очевидных мест.
              Т.е. инструмент для объяснения не должен быть сложнее, чем объясняемый материал, как мне кажется.

              • staticlab
                /#20043520

                Мне кажется, это скорее вопрос тщательности проработки картинки. Я всё-таки её просто за 5 минут накидал в Ворде. Во всяком случае она очевидно отвечает на вопрос: почему возвращается именно столько строк в ответе.

  26. koropovskiy
    /#20042354

    Вообще вся статья исходит из изначально неверной предпосылки что «таблица — это вообще не множество.» Но дальше сводится к верному что JOIN это не INTERSECT.

    Но зачем разработчикам знать нюансы терминологии теории множеств на собеседовании по SQL на стандартном базовом вопросе про SQL?!
    Не знает отличий между INNER и LEFT — не писал в SQL запросы сложнее «дай список по таблице с фильтром» — надо учить или прощаться.
    Знает разницу между INNER и LEFT — ок, даже кружочками (привет универ, или гугл, или 100500 других источников знаний по SQL). Хотите уточнить пределы знаний — задаете дальнейшие вопросы. Про не уникальность, про сравнение NULL, да про что угодно вплоть до оконных функций, и вставки в несколько таблиц одним INSERT запросом

    задаете advanced вопросы? это точно из

    • koropovskiy
      /#20042668

      Я буду перечитывать текст перед отправкой. Я буду перечитывать текст перед отправкой. Я буду перечитывать текст перед отправкой. :(
      Хотел дописать «Это точно не из из числа»

  27. joyfolk
    /#20042400

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

  28. spv32
    /#20042448 / +4

    Еще вариант картинки
    image

    • koropovskiy
      /#20042720

      Статья как раз о том, что при условии не уникальных "*" вы получите больше строк ответа, чем ожидается на основании этой картинки.

      • mayorovp
        /#20043006

        Да нет. В отличии от истории с кругами обозначающими множества, на этой картинке ничего не говорится про пересечение.

        • koropovskiy
          /#20043030

          На картинке с кругами тоже ничего не говорится про пересечение. там только круги и заливка/штриховка.

          • mayorovp
            /#20043118

            Таки говорится. Там обычно первый круг подписывают как первую таблицу-множество, а второй круг — как вторую (и даже если этого не делают — все равно наблюдается явная отсылка к диаграмме Венна). В такой картинке пересечение кругов должно быть пересечением множеств-таблиц, что и ошибочно.

            На этой картинке никто не подписывал какой-то конкретный набор строк как таблицу, все раскрашенные области явно относятся только к их соединениям.

            • koropovskiy
              /#20043190 / -1

              Здесь явно подписан левый прямоугольник и правый. Inner join объединяет одинаковые элементы в обоих прямоугольниках.
              Отличия от кругов скорее косметические.
              Отсылка к диаграмме Венна имеет смыл только для тех кто помнит диаграммы Венна. Помнящих не так много как может показаться :D

              Ни круги, ни прямоугольники не показывают наглядно почему будет 4 записи при наличии 2х единичек с каждой стороны и 6 записей при 2 к 3. Впрочем никакие картинки это нормально не показывают. При не уникальных записях мы получаем мультипликативный эффект. Чтобы его понять надо с другой стороны подходить к JOIN и построению запросов. Уж точно не пытаться это объяснять через cross join, использующийся примерно в 1 запросе из 10000.

              По моему опыту проще заходит объяснение через вложенные циклы.
              Но повторюсь, это уже гораздо дальше простого понимания когда надо ставить INNER, когда LEFT OUTER, а когда FULL OUTER, а именно на это нацелены картинки.

    • Neikist
      /#20043182

      При full outer join может получиться пустой результат если одна таблица пустая а другая нет, насколько помню.

      • koropovskiy
        /#20043250

        Нет. Вы получите все записи. Проверено на Pg10 и Ora11. В MySQL5.7 «FULL» еще нет =-)

        • Neikist
          /#20043260

          Или на cross join… Просто точно помню как то багу словил и наверно день ее выискивал, а дело оказалось в том что в одной таблице не было записей и из за этого вообще ничего не приходило.

  29. menotal
    /#20042552

    Разницу джойна и пересечения множеств можно продемонстрировать уже хотя бы тем, что дословный перевод join = соединение, что не эквивалентно пересечение.

  30. nikitasius
    /#20043716 / -2

    Кривая статья.


    Таблица 1
    image


    Таблица 2
    image


    Вывод по inner join:
    image


    Что собсна криво? 1 встречается в таблице 1 два раза, как и в таблице 2 два раза.


    Далее 2х2 дает 4 результата.


    Если я поменяю вот так вот данные в таблице 2:
    image


    То получу:
    image


    Где ON table1.id = table2.id


    Вот с табличками:
    image

  31. Vlad_fox
    /#20043730 / -1

    некоторые недалекие люди на собеседовании пишут 2*2 = 4
    в этой статье вы узнаете правду, которая сломает ваш мозг и вам дальше с этим жить.
    Это только в евклидовом пространстве 2*2=4, а в неевклидовом может быть и больше!
    да, я знаю, что 99% не оперируют в неевклидовом пространстве ни в быту, ни на работе,
    но у меня вот проектик — и там как раз оно — неевклидово.

    • lorc
      /#20043788

      Кривая аналогия (уж не говоря о том, что 2*2 не зависит от типа пространства).

      Проблема в том, что любой join — это декартово произведение (возможно с добавлением null, если это не inner join). Потом этот уже join фильтруется по условию. Если этого не понимать, то реальность может укусить за задницу в самый неожиданный момент.

      • ksbes
        /#20043860

        2*2 = 11 в обычной троичной системе (0,1,2,10,11).

        А с джоином и сам накалывался. Просто чаще всего его делают по PK/FK, и получают уникальность по определению. Но стоит отойти от «стандартной схемы» — бац и попал. (Я не датабейзник, но если настойчиво просят — куда деваться)

        • 0xd34df00d
          /#20043918

          11 в троичной равно 4 в десятичной, так что вы ничего принципиально нового не выигрываете, это вопрос нотации.


          Вот куда интереснее рассмотреть циклическую группу порядка 3 или 4...

    • luck1ess
      /#20047542

      Ох, мой дорогой любитель неевклидовых пространств, пространства они про вектора, а не скаляры. Какое у вас было пространство, если не секрет?) Кстати вот вам задачка, 1*2=3, угадайте про какую геометрию, работает на вещественных числах.

  32. eviland
    /#20044242

    Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не 'LEFT JOIN… WHERE… IS NULL', а конструкцию EXISTS. Это и читабельнее, и быстрее.

    Читабельность — ок. Но чем быстрее — можно подробнее? Во всех ли случаях одно будет быстрее другого и есть ли вообще разница? Если да, то в каких СУБД?

    • mayorovp
      /#20044558

      В СУБД с полноценным оптимизатором не важно какой метод использовать.

      Но если оптимизатор не в курсе что равенство Not Nullable-поля NULL равносильно отсутствию записи — то вариант с EXISTS и правда будет быстрее.

      А если оптимизатор не умеет нормально работать с подзапросами — то быстрее может оказаться уже LEFT JOIN.

      • eviland
        /#20045132 / +1

        Вот и я об этом. В общем виде, без привязки к конкретной СУБД и более того, без понимания какие у вас данные — этот совет скорее вредный, чем полезный. В одну копилку с «full scan — это плохо», «nested loop — это плохо», а так же классикой жанра «если нет индекса — надо создать». Нет времени объяснять, делайте как я говорю.

        • varanio
          /#20045786

          Да, надо было написать, что речь про postgresql

      • force
        /#20050274

        С помощью NOT EXISTS я в своё время убивал Access и MySql (мой любимый тест на оптимизатора базы), базы поумнее, конечно же себе такого не позволяли, но JOIN'ы отрабатывали правильно все. Так что, на мой взгяд во всех базах идёт оптимизация вначале на джойны, а не на EXISTS

  33. robo2k
    /#20044254 / -2

    Вообще то даже в w3schools приводится объяснение с кругами.
    image
    image
    image
    image

    • mayorovp
      /#20044564 / +1

      Это всего лишь означает, что в w3schools написали неправильное объяснение.

  34. Timyrlan
    /#20044264 / -2

    "-Доктор, когда я делаю вот так, у меня вот тут болит
    -Неделайте так"

    Не надо писать код, который не очевиден для 99% программистов. Это с высокой вероятностью приведет к тому, что в код будет внесен баг рано или поздно. Код, простите за баян, надо писать так, будто поддерживать его будет склонный к насилию маньяк.

    • staticlab
      /#20045076

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

  35. limassolsk
    /#20045082 / +1

    Понимание джойнов сломано. Это точно не пересечение кругов, честно
    «пересечение кругов» — это не сам джойн, а только его условие.

    Из всех вариантов «корректного объяснения джоинов на картинке» мне понравился вариант Evir:

    Но на нём слишком много элементов, можно упростить просто взяв любую картинку с «пересечением» и добавить на неё знак умножения:

    Тогда если у человека, проводящего собеседование, останутся вопросы, то можно легко объяснить, что «пересечение» таблиц будет происходить на основе «условия джойна», а дальше будет «декартово произведение» всех попавших под условие элементов.

    Т.е. для примеров из статьи:
    JOIN {1,1,2} и {1,1,3} = {1,1}x{1,1} = {(1,1), (1,1), (1,1), (1,1)}
    LEFT JOIN {1,1,2} и {1,1,3} = {1,1}x{1,1} + {2}
    RIGHT JOIN {1,1,2} и {1,1,3} = {1,1}x{1,1} + {3}

  36. kurt_mt
    /#20047422 / -1

    Все просто же
    image

  37. dilukhin
    /#20048166 / -1

    Конечно, JOIN это не пересечение кругов, или по-научному это не конъюнкция. Это внезапно операция умножения. Да, она похожа на конъюнкцию, в том, что если где-то чего-то нет, то и в результат оно не попадает. Но в «середине» идёт перебор каждый к каждому, что для пересечения множеств несвойственно.

  38. minamoto
    /#20049034

    В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.

    В общем случае это неверно. Не знаю, как там в Постгре, а вот в MS SQL, например, CTE (если не рекурсивный) — это просто синтаксический сахар для удобства написания, и при выполнении будет развернут в подзапросы, соответственно оптимизатор соединит их, как посчитает нужным, и вы сохраните все те же проблемы с множеством соединяемых таблиц.
    Не раз и не два уже оптимизировал запросы именно за счет избавления от CTE и перевода их в лоб на временные таблицы, которые, действительно, будут выполняться последовательно, и для заполнения каждой временной таблицы будет свой отдельный план выполнения.

    • varanio
      /#20049178

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

      В текущей 11 версии это всегда временная таблица

      • Melkij
        /#20049268

        cte до postgresql 12 всегда материализуется, но это некорректно называть временной таблицей. Временная таблица — это вполне определённый create temporary table

  39. Anarchist
    /#20052006 / -1

    Во-первых, строки в таблицах таки уникальны с точки зрения БД. Другое дело, что эту уникальность «внешнему наблюдателю» можно и не увидеть.

    Во-вторых, самое близкое к джойнам (в случае внутренних — так вообще точное определение) теоретико-множественное понятие — это отношения: ru.wikipedia.org/wiki/%D0%9E%D1%82%D0%BD%D0%BE%D1%88%D0%B5%D0%BD%D0%B8%D0%B5_(%D1%82%D0%B5%D0%BE%D1%80%D0%B8%D1%8F_%D0%BC%D0%BD%D0%BE%D0%B6%D0%B5%D1%81%D1%82%D0%B2)

  40. Mikluho
    /#20053132

    Вот жежь заморочились на ровном месте :)
    Множества, алгебра, картинки…

    Я очень давно теорию изучал и нифига не помню определения, особенно теоретические. Но для себя оставил в голове практическое определение, через которое не мало раз объяснял селекты джунам…

    Join объединяет записи из двух таблиц, при этом для каждой записи в одной таблице подбираются записи из другой. Вид джойна определяет способ отбора записей в объединённый результат. Left/Right Join — все записи из одной таблицы объединяются с найденными по условию из второй таблицы, а где не нашлось, подставляются пустые строки. Inner Join — то же самое, но без добавления пустых строк. Full — добавляются строки для обеих таблиц. Причём, если условие связи таблиц не определено — подходят все записи.

    Ключевое тут — множественное число. Т.е. движок бд «постарается» вытащить всё, что сможет, ибо условия — это ограничения.

  41. smbsmn
    /#20054546

    Джойны (inner-outer) объясняются просто — в 1 книжке читал, не помню, как называется.
    Представьте, что вы на свадьбе.
    INNER JOIN — если со свадьбы выходят только семейные пары.
    LEFT JOIN — если со свадьбы выходят семейные пары и ещё любовницы мужей.
    RIGHT JOIN — если со свадьбы выходят семейные пары и ещё любовники жён.
    FULL OUTER JOIN — если со свадьбы выходят семейные пары и все любовники и любовницы жён и мужей из семейных пар.
    CROSS JOIN — ну это вот…

    • mayorovp
      /#20055172

      По логике, если соединялись таблицы мужчин и женщин по условию брака, то в left join должны добавляться неженатые мужчины, а в right join — незамужние женщины.

      А каким должно быть соединение чтобы туда стали попадать любовники и любовницы — представить не могу.

      • smbsmn
        /#20055426

        В контексте объяснения — для брака — это тоже НАЛЛ.
        Ты прав — твой пример с неженатыми-незамужними лучше.

  42. smbsmn
    /#20054632

    В постгресе вроде такого нет, но в оракле с 12-го наличествует.
    Какой ответ ТС ожидает получить от собеседуемого об APPLY?
    КАК это нужно показать на пальцах?
    ЗЫ
    Я, честно признаться, не очень понимаю валидность этих технических оффлайновых собеседований.
    У меня на гитхабе выложен пет-проект по расчёту сальдооборотов для домашнего учёта.
    Там всё ок.
    Последние несколько лет работаю программистом БД, реально бизнес-логику пишу.
    + рефакторинг и оптимизация легаси.
    НО.
    Возможно, я интроверт. Или панические атаки случаются.
    Последний раз на собеседовании я не смог на бумажке к схеме СКОТТ элементарный запрос написать.
    Хотя на работе на доске я, бывает, примерно такие запросы пишу.

  43. smbsmn
    /#20054690

    Кстати, насичёт синтаксического сахара и реляционной алгебры: ни разу в продакшне не видел EXCEPT или INTERSECT.
    Кто-то видел???