Когда юзать CROSS APPLY

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

При этом учитывайте, что CROSS APPLY аналогичен INNER JOIN, а OUTER APPLY - LEFT JOIN

вывести для каждого продавца три его последних заказа за 1998 год. 

Подготовим подзапрос для определения 3 последних заказов продавца 1

SELECT TOP (3) *

FROM Orders

WHERE Year (OrderDate) = 1998

AND EmployeeID = 1

ORDER BY OrderDate DESC

Сделаем к этому подзапросу CROSS APPLY

SELECT *

FROM Employees E CROSS APPLY (

                                               SELECT TOP (3) *

                                               FROM Orders

                                               WHERE Year (OrderDate) = 1998

                                               AND EmployeeID =E.EmployeeID

                                               ORDER BY OrderDate DESC

                                               ) MyList

ORDER BY E.EmployeeID

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

SELECT FirstName+ ' ' + LastName, MyList.*
-- FROM Employees E CROSS APPLY
FROM Employees E OUTER APPLY -- Выберем OUTER APPLY, чтобы увидеть еще и нулевые записи NULL для тех, кто не отправлял вообще ничего в свой город
                            (
                            SELECT TOP (3) WITH TIES OrderID
                            FROM Orders
                            WHERE EmployeeID = E.EmployeeID
                            AND ShipCITY=E.City
                            ORDER BY OrderDate ASC
                            ) MyList
Order by FirstName+ ' ' + LastName

Одна из опасных ошибок использования внешнего JOIN-а

Если вы не уверены в правильности использования JOIN-ов, не поленитесь решить ее с использованием подзапросами

-- Сколько денег принес каждый товар в мае 1998-го,

-- Способ Join-ами с использованием скобок для избежания ошибок

-- Используем как LEFT так и INNER JOIN

SELECT ProductName, ROUND (SUM (OD.UnitPrice * OD.Quantity * (1-OD.Discount)),2) AS Total

FROM   Products P LEFT JOIN

             (

             [Order Details] OD

             INNER JOIN Orders O

             On OD.OrderID=O.OrderID

             AND YEAR (OrderDate) = 1998

             AND MONTH (OrderDate) = 5

             )

             ON P.ProductID=OD.ProductID

Group by ProductName

Order by Total DESC

----Проверяем результаты использую подзапросы

SELECT ProductName,

             (

             SELECT IsNull  (ROUND (SUM (UnitPrice * Quantity * (1-Discount)),2) ,0)

                    FROM   [Order Details]

                    WHERE  OrderId in

                                  (

                                  SELECT OrderID

                                  FROM Orders

                                  WHERE YEAR (OrderDate) = 1998

                                  AND Month (OrderDate) = 5

                                  )                                

             AND ProductID=Products.ProductID

             ) AS Total

FROM   Products    

Order by Total desc

-- 1. Как зовут продавцов, которые оформили заказов в Берлин больше, чем в Париж

-- 2. Как зовут продавцов, которые оформили заказов в Берлин не меньше, чем в Париж

-- Джойнами

SELECT FirstName + ' ' + LastName, Count (DISTINCT O1.OrderID), Count (Distinct O2.OrderID)

FROM Employees E LEFT JOIN Orders O1

ON     E.EmployeeID=O1.EmployeeID

AND O1.ShipCity='Berlin'

LEFT JOIN Orders O2

ON     E.EmployeeID=O2.EmployeeID

AND O2.ShipCity='Paris'

GROUP BY FirstName + ' ' + LastName

HAVING Count (DISTINCT O1.OrderID) > Count (Distinct O2.OrderID) -- или >= для второй задачи

-- HAVING Count (DISTINCT O1.OrderID) >= Count (Distinct O2.OrderID)

-- Подзапросами

SELECT FirstName + ' ' + LastName

FROM Employees

WHERE

(SELECT Count(ShipCity)

             FROM Orders

             WHERE EmployeeID=Employees.EmployeeID

             AND ShipCity='Berlin'

             )

             > -- или >= для второй задачи

             (SELECT Count(ShipCity)

             FROM Orders

             WHERE EmployeeID=Employees.EmployeeID

             AND ShipCity='Paris'

             )

            

Опасности при использовании JOIN

Если попробовать решить одну и ту же задачу двумя способами - подзапросами и джойнами, то можно обнаружить ошибку. См. скрин ниже.
При запросе подзапросами мы получаем на две записи больше, чем при запросе джойнами  с нулевыми значениями  - записи желтым.
Все дело в том, что не все покупатели из Customers нашлись в Orders.
Казалось бы решение в том, чтобы заменить INNER JOIN на LEFT JOIN, RIGHT JOIN или FULL JOIN в зависимости от задачи, но это не спасает ...




Ошибки
1) Думайте, когда использовать INNER JOIN, LEFT JOIN, FULL JOIN, т.е. возможны ли ситуации, когда не все покупатели имеются в таблице Orders.
2) Если решили использовать LEFT  JOIN, то не использовать COUNT(*) , т.к. в этом случае будут считаться все записи объединения, не имеющие смысла. Используйте COUNT по какому-то явному столбцу, например, COUNT (ORDERID)

3) Самая опасная ошибка в том, что можно допустить ошибки при объединении. Надо внимательно относиться к тому, где применять какой JOIN.