Когда юзать 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.

CROSS JOIN or INNER JOIN?

INNER JOIN позволяет не выводить весь список возможных соединений таблиц, а выводить  сразу по условию - только те, которые соответствуют условию в ON
SELECT *
FROM   ORDERS O INNER JOIN [ORDER DETAILS] OD
ON O.ORDERID=OD.ORDERID

Например

Алгоритм для использования подзапросов SUBQUERRY

Чтобы решить ту или иную задачу c использованием подзапросов SUBQUERY,  необходимо подумать, отталкиваясь от меньшего к большему, задавая себе вопросы и отвечая на них.

Последовательность вопросов и действий:

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

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

В зависимости от ответа, сразу пишем запрос к той таблице, из которой можно получить результат. Например, если то, что нам нужно находится в TABLE1 и это NAME1, то пишем

SELECT NAME1
FROM TABLE1

Вопрос 2:
Из какой таблицы я могу получить недостающие данные?
Поняв ее имя, сразу открываем скобки в основном запросе и помещаем в них подзапрос к недостающим данным. Если эти данные LACK_DATA в таблице TABLE2, то внутренний подзапрос будет выглядеть так (синим):

SELECT NAME1,
               (
               SELECT LACK_DATA 
               FROM  TABLE2
               )
FROM TABLE1

Вопрос 3:
Как недостающие данные и данные из основого запроса связаны между собой? Пусть они связаны по столбцу ORDER_ID. Тогда добавляем условие WHERE во внутренний запрос (синим):
SELECT NAME1,
               (
               SELECT LACK_DATA
               FROM  TABLE2
               WHERE TABLE2.ORDER_ID=TABLE1.ORDER_ID
               )
FROM TABLE1


Рассмотрим пример: Сколько штук каждого товара мы продали по БД на диаграмме ниже?

Вопрос 1: Что ищем и где это есть? Ищем имена товаров ProductName и они есть в таблице Products. Пишем:

SELECT ProductName
FROM Products

Вопрос 2: Что нам не хватает и в какой таблице мы можем найти эти недостающие данные?
Нам нужно кол-во QUANTITY каждого продукта. И это есть в таблице [Order Details]
Открываем скобки и пишем подзапрос (синим)
SELECT ProductName,
            (SELECT COUNT(Quantity)
             FROM [Order Details]
             ) 
FROM Products

Вопрос 3: Каким образом связаны таблицы Products и [Order Details]? Ответ: По полю ProductID. Добавляем во внутренний запрос условие (синим)

SELECT ProductName,
            (SELECT COUNT(Quantity)
             FROM [Order Details]
             WHERE [Order Details].ProductID=Products.ProductID 
              )
FROM Products

Готово! На выходе мы имеем список и кол-во проданных товаров. 

Далее можно отсортировать по кол-ву. Удобнее внутреннему запросу дать имя, для его использования в ORDER BY

SELECT ProductName,
            (SELECT COUNT(Quantity)
             FROM [Order Details]
             WHERE [Order Details].ProductID=Products.ProductID
            ) AS QTY
FROM Products
ORDER BY QTY DESC

Если нужно вывести только имя самого продаваемого товара, то в SELECT можно добавить TOP и подзапрос переместить в Order BY.
Т.о. запрос будет выглядить следующим образом:


SELECT TOP 1 ProductName
FROM Products
ORDER BY
            (SELECT COUNT(Quantity)
             FROM [Order Details]
            WHERE [Order Details].ProductID=Products.ProductID 
            )       
DESC

Алгоритм для использования объединения таблиц CROSS JOIN

Далее для демонстрации CROSS JOIN рассмотрим задачи на основе базы Northwind, загрузить ее образец можно с http://msdn.microsoft.com/ru-ru/library/ms143221(v=sql.105).aspx

Диаграмма БД:

Задача 1.
Сколько городов обслужил каждый продавец?

1) Осмыслить структуры базы и объединить все таблицы, упомянутые в задаче

SELECT *
FROM Employees E CROSS JOIN Orders O

Получим малополезный массив кол-во строк первой таблицы * кол-во строк второй таблицы

2) Профильтровать так, чтобы остались только осмысленные записи, т.е. те, которые имеют смысл в реальности.

SELECT *
FROM Employees E CROSS JOIN Orders O
WHERE E.EmployeeID=O.EmployeeID

3) Спрашиваем себя, что мы получили в результате. В данном примере это список всех заказов ORDERS.
Это нам нужно для того, чтобы позже

4) Всегда GROUP BY
А чего я хочу получить по задаче? Я хочу список продавцов, значит в GROUP BY пишем продавцов

SELECT E.FirstName+' '+E.LastName, count(DISTINCT ShipCity)
FROM Employees E CROSS JOIN Orders O
WHERE E.EmployeeID=O.EmployeeID
GROUP BY E.FirstName+' '+E.LastName
ORDER BY count(DISTINCT ShipCity) DESC


Задача 2.
Как зовут покуптелей, делавших в 1996-м заказы у нескольких продавцов?

SELECT C.ContactName, COUNT (DISTINCT EMPLOYEEID)
FROM CUSTOMERS C CROSS JOIN Orders O
WHERE C.CustomerID=O.CustomerID
AND YEAR (ORDERDATE)=1996
GROUP BY C.ContactName
HAVING COUNT (DISTINCT EMPLOYEEID) > 1

COUNT (DISTINCT EMPLOYEEID) из SELECT можно убрать


 Задачи с тремя и более таблицами и JOIN-ами можно решать по такому же алгоритму:

Oracle again?

Today I've completed two Oracle courses on 11g and passed the 1Z0-050 exam... to upgrade my Oracle 10g DBA OCP to 11g. I wonder if I will have a chance to work with Oracle. It would be nice to be honest.

Google Adsense

Сто лет сюда не заходил...Тут появилась какая-то херня Monetize... Это Google Adsense. Добавил только что :) и вон че вылезло :)))




let's see when I'll earn first bagz! :)

How to extend swap size in RHEL

[root@rhel_srv ~]# dd if=/dev/zero of=/swapfile bs=1024k count=16384
[root@rhel_srv ~]# mkswap /swapfile
Setting up swapspace version 1, size = 17179865 kB
[root@rhel_srv ~]# swapon /swapfile
[root@rhel_srv ~]# cat /proc/swaps
Filename Type Size Used Priority
/dev/mapper/vg.01-lv_swap partition 33554424 144 -1
/swapfile file 16777208 0 -2
Add to /etc/fstab
[root@rhel_srv ~]# vi /etc/fstab
/swapfile swap swap deafaults 0 0