дата инженеретта on Telegram: “❌CROSS APPLY, OUTER APPLY ❌Я буду говорить про M…”

❌CROSS APPLY, OUTER APPLY

❌Я буду говорить про MS SQL Server (потому что очень долго с ним работала), но в Postgres тоже есть похожие конструкции - CROSS JOIN LATERAL, LEFT JOIN LATERAL. Так что с другими бд, я надеюсь, тоже несложно разобраться.

В чем суть?
По факту это те же самые джойны, но не с табличкой, а с результатом функции, которая возвращает табличку.

1️⃣ CROSS APPLY ~ INNER JOIN

Допустим, есть функция поиска книги по автору:

CREATE FUNCTION getBookByAuthorId(@AuthorId int)
RETURNS TABLE AS RETURN
( 
  SELECT * FROM book
  WHERE author_id = @AuthorId
)

Используем в запросе:

SELECT a.author_name, b.book_name, b.price
FROM author a
CROSS APPLY getBookByAuthorId(a.id) b

2️⃣ OUTER APPLY ~ LEFT JOIN

Ненайденные строчки также заполняются NULL.

В этом случае аналогично подзапросу в селекте:

--outer apply
SELECT a.author_name, book_list
FROM author a
OUTER APPLY (
  SELECT STRING_AGG(book_name, ', ') AS book_list
  FROM book b
  WHERE b.author_id = a.id
) sub

--подзапрос
SELECT a.author_name, (
  SELECT STRING_AGG(book_name, ', ')
  FROM book b
  WHERE b.author_id = a.id
) AS book_list
FROM author a
--STRING_AGG соединяет строки в одну ячейку с разделителем

С агрегирующими функциями прокатит только outer apply, потому что агрегировать поверх подзапроса нельзя. А так можно:

SELECT a.author_name, count(book_id) as book_num
FROM author a
OUTER APPLY (
  SELECT b.id as book_id
  FROM book b
  WHERE b.author_id = a.id
) sub
GROUP BY a.author_name

#sql_tips