🟡 Почему эти картинки не помогают понять работу JOIN'ов?

Возьмем для примера эти две таблицы:

id  id
1   1
1   1
1   1
2   6
3   7
4   9

Смотря на эту картинку, можно ошибочно прийти к выводу, что при LEFT JOIN в результирующей таблице будет 6 строк, однако их будет 12.
Разобраться тут нужно с тем, как на самом деле работает INNER JOIN.

➡️ Легче всего это визуализировать, представив два цикла for: в первом id таблицы 1, во втором id таблицы 2.
Нам необходимо сравнить каждый элемент первой таблицы с элементами второй и, при их равенстве, вывести эту пару:

for id1 in table1:
  for id2 in table2:
    if id1 == id2:
      print(id1, id2)

Понимание остальных JOIN'ов строится на понимании INNER:

LEFT JOIN = результат INNER + оставшиеся id в левой таблице
RIGHT JOIN  = результат INNER + оставшиеся id в правой таблице
FULL JOIN = результат INNER + оставшиеся id в левой таблице + оставшиеся id в правой таблице
CROSS JOIN = все пары, при CROSS JOIN мы не проверяем равны ли id

#de_обсуждение

select distinct name, row_number() over(order by id)
from table

select name, row_number() over(order by id)
from table

⚡️ Эти запросы вернут равное количество строк?
Ответ : да
Сначала с помощью оконной функции, мы добавим колонку с уникальным порядковым номером, и только после отработает DISTINCT, поэтому каждая строка будет уникальной.

Порядок выполнения команд в SQL:

FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET
#de_обсуждение

➡️Вопросы с тех собеса в Иннотех(Т1)◀️

Что такое Data Lake?
Что такое DWH?
Расскажите про слои DWH?
Чем отличается моделирование по Кимбаллу и по Инмону?
Звезда и снежинка это Кимбалл или Инмон?
Чем звезда отличается от снежинки?
Чем отличаются факт таблицы и таблицы измерений?
Расскажите про Data Vault
Расскажите про Anchor Modeling
Зачем нужна нормализация?

Какие вида JOIN'ов знаете?
Расскажите про SELF JOIN.
Что знаете про агрегационные функции?
Можно ли использовать несколько агрегационных функций в select?
Можно ли использовать GROUP BY без агрегационной функции?
Как убрать дубликаты? То есть сделать так, чтобы таблица осталась без дубликатов, а не запрос вывел данные таблицы без дубликатов?

Что такое первичный ключ? Что такое foreign key? В каком разрезе формируют ключ? Что он должен в себе нести?
Что такое суррогатный ключ? Как его построить?

Как RANK() работает с NULL?

Расскажите про способы оптимизации SQL зпросов

#de_собеседование

✨Физические JOIN'ы✨

Мое незнание этой темы привело к 6-месячному куллдауну в Авито😭
Чтобы у вас не было также, давайте разберем ее подробнее.

😍Физический джойн — это алгоритм, который используется для выполнения операции объединения двух таблиц. Иными словами, это то, что происходит "под капотом", когда вы вызываете join в запросе

⭐ Основных алгоритмов всего 3: nested loops, merge join, hash join/hash match.

🩷Nested loops

Принцип работы уже понятен из названия: каждый элемент внешнего цикла сравнивается с каждым элементом внутреннего.
Алгоритмическая сложность - O(n**2)

For Each value in pile1
    For Each value in pile2
        If pile1.value = pile2.value
        Return pile1.value, pile2.value

🩷Merge join

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

Если не считать сортировку, алгоритмическая сложность - O(n).

get first row R1 from   input 1
get first row R2 from   input 2

while not at the end   of either input
      begin
          if R1 joins with R2
              begin
                  get next row R2 from input 2
                  return (R1, R2)
              end
          else if R1 < R2
              get next row R1 from input 1
          else
              get next row R2 from input 2
    end

🩷Hash join

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

// Build phase
FOR each row in BuildTable DO
    Compute hash value for the join key
    Insert row into HashTable based on hash value
END FOR

// Probe phase
FOR each row in ProbeTable DO
    Compute hash value for the join key
    IF hash value exists in HashTable THEN
        Retrieve matching rows from HashTable
        FOR each matching row DO
            Combine rows from ProbeTable and BuildTable
            Add the combined row to the result set
        END FOR
    END IF
END FOR

Рекомендую просмотреть псевдокод и реализовать все на своем языке💖
#de_обсуждение

Во-первых, осень действительно лучшее время для поиска работы.
Во-вторых, мне нужны бусты☹️

У меня заволялись телеги hr'ок из МТС, Билайна, Мегафона, Яндекс Маркета и Сбера. Отправлю их забустившим канал.✨

Забустить тут⬇️
https://news.1rj.ru/str/boost/data_interviews

📍Написать hr'у напрямую всегда лучше отклика на хх, так как личное сообщение гарантирует просмотр резюме.

Также если хотите рефералку в Авито, отпишитесь в комментариях и я свяжусь с вами ❤️

✨Физические JOIN'ы 2.0 ✨

Рекомендую сначала прочесть этот пост.

После оценки алгоритмической сложности физических джойнов можно прийти к выводу, что выбор hash join является оптимальным решением, однако это далеко не так. Как и во многом в программировании, всегда есть space–time trade-off, и выбор оптимального джойна будет зависеть от входных данных.

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

🩷Условие соединения
Для equi-joins (равенство =, неравенство !=) и non-equi-joins (>, <, >=, <=). Для второго типа подойдет только nested loops.

💕Размер таблиц
Также, конечно, важен размер таблиц. Из-за необходимости многократно проходить по второй таблице в случае с nested loops будет велика цена I/O, в случае merge join будет дорогой сортировка, а в случае hash join может не хватить памяти для хеширования, и часть придется переносить на диск. Хешируется, кстати, меньшая таблица.

Если вы работаете с отсортированными данными, выиграет merge join, а с неотсортированными — hash join.

В случае, когда обе таблицы маленькие, эффективнее может быть nested loops, ведь с merge сортировка может вовсе не окупиться.

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

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

#de_обсуждение