Где использовать Outer Apply

Question

МАСТЕР СТОЛ

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

ДЕТАЛИ СТОЛ

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

Я получаю те же результаты, когда LEFT JOIN а также OUTER APPLY используется.

LEFT JOIN

SELECT T1.ID,T1.NAME,T2.PERIOD,T2.QTY 
FROM MASTER T1
LEFT JOIN DETAILS T2 ON T1.ID=T2.ID

OUTER APPLY

SELECT T1.ID,T1.NAME,TAB.PERIOD,TAB.QTY 
FROM MASTER T1
OUTER APPLY
(
   SELECT ID,PERIOD,QTY 
   FROM DETAILS T2
   WHERE T1.ID=T2.ID
)TAB

Где я должен использовать LEFT JOIN И где я должен использовать OUTER APPLY

Answer 1

LEFT JOIN следует заменить на OUTER APPLY в следующих ситуациях.

1. Если мы хотим объединить две таблицы на основе TOP n Результаты

Подумайте, нужно ли нам выбирать Id а также Name от Master и последние две даты для каждого Id от Details Таблица.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

который формирует следующий результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Это приведет к неправильным результатам, т. Е. К последним данным за две последние даты. Details стол независимо от Id хотя мы присоединяемся к Id, Таким образом, правильное решение использует OUTER APPLY,

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

Вот рабочая: В LEFT JOIN, TOP 2 даты будут присоединены к MASTER только после выполнения запроса внутри производной таблицы D, В OUTER APPLY, он использует присоединение WHERE M.ID=D.ID внутри OUTER APPLY так, чтобы каждый ID в Master будет объединен с TOP 2 даты, которые принесут следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. Когда нам нужно LEFT JOIN использование функциональности functions ,

OUTER APPLY может быть использован в качестве замены LEFT JOIN когда нам нужно получить результат от Master стол и function,

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

И функция идет здесь.

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

который породил следующий результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

3. Сохранить NULL значения при отмене

Считайте, что у вас есть таблица ниже

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   |    
|   3  |   NULL      |   NULL       | 
x------x-------------x--------------x

Когда вы используете UNPIVOT принести FROMDATE А ТАКЖЕ TODATE в одну колонку, это исключит NULL значения по умолчанию.

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

который генерирует следующий результат. Обратите внимание, что мы пропустили запись Id число 3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  x------x-------------x

В таких случаях APPLY можно использовать (либо CROSS APPLY или же OUTER APPLY, который является взаимозаменяемым).

SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

который формирует следующий результат и сохраняет Id где его значение 3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  |  3   |     NULL    |
  x------x-------------x

Answer 2

В вашем примере запросов результаты действительно совпадают.

Но OUTER APPLY может сделать больше: для каждой внешней строки вы можете создать произвольный набор внутренних результатов. Например, вы можете присоединиться к TOP 1 ORDER BY ... строка. LEFT JOIN не могу этого сделать.

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

OUTER APPLY строго сильнее, чем LEFT JOIN, Это легко увидеть, потому что каждый LEFT JOIN можно переписать на OUTER APPLY так же, как вы сделали Хотя его синтаксис более многословен.