T-SQL – Combining sets

Joins
Matches Set A and Set B using specified columns. You can use: inner, left, right, outer joins.

SELECT E.firstname, E.lastname, O.shippostalcode FROM HR.Employees AS E
RIGHT JOIN Sales.Orders O ON E.empid = O.empid
ORDER BY E.lastname, E.firstname, O.shippostalcode

Not correlated query. Select the cheapest product.

SELECT * FROM Production.Products
WHERE unitprice =
(SELECT MIN(unitprice) FROM Production.Products)

Correlated query. Select the cheapest products in groups.

SELECT productname, categoryid, unitprice
FROM Production.Products AS P1
WHERE unitprice =
(
SELECT MIN(unitprice)
FROM Production.Products AS P2
WHERE P2.categoryid = P1.categoryid
)

Correlated query. Select customers who made an order at given date.

SELECT * FROM Sales.Customers AS C
WHERE EXISTS
(
SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid AND O.orderdate = '20070212'
)

Table expressions
Derived table. Select two cheapest products from each category.

SELECT categoryid, productid, productname, unitprice
FROM
	(SELECT
	ROW_NUMBER() OVER(PARTITION BY categoryid
	ORDER BY unitprice, productid) AS rownum,
	categoryid, productid, productname, unitprice
	FROM Production.Products) AS D
WHERE rownum <= 2;

Common Table Expression. Result the same as above. Advantages of CTE:
a) you can create many CTEs separating code by comma;
b) you can use the first CTE in the second one;
c) code is more readable.

WITH D AS
(
SELECT
	ROW_NUMBER() OVER(PARTITION BY categoryid
	ORDER BY unitprice, productid) AS rownum,
	categoryid, productid, productname, unitprice
	FROM Production.Products
)
SELECT categoryid, productid, productname, unitprice
FROM D
WHERE rownum <= 2

View

CREATE VIEW Sales.RankedProducts
AS
SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products;

SELECT * FROM Sales.RankedProducts

Inline-Table-Valued Function. Similiar to view, but you can pass parameters.

CREATE FUNCTION Sales.PricedProducts(@minprice as INT) 
RETURNS TABLE
AS
RETURN
SELECT * FROM Production.Products
WHERE unitprice > @minprice;

SELECT * FROM Sales.PricedProducts(50);

Apply
Cross Apply – you can do calculation for every row. In cross apply when the right side calculation returns no rows when the left side row won’t be displayed.
Shows how many orders was created for employees. Employees without orders won’t be shown.

SELECT firstname, lastname, OrdersCount FROM HR.Employees AS E
CROSS APPLY
(
SELECT COUNT(*) AS OrdersCount 
FROM SALES.Orders O 
WHERE O.empid = E.empid
) AS A

Outer Apply will do almost the same, but also employees without orders will be displayed.

SELECT firstname, lastname, OrdersCount 
FROM HR.Employees AS E
OUTER APPLY
(
SELECT COUNT(*) AS OrdersCount 
FROM SALES.Orders O 
WHERE O.empid = E.empid
) AS A

Set operators

Intersect => Set A * Set B.
Except => Set A – Set B.
Union all => Set A + Set B.
Union => Set A + Set B (duplicates are removed).

Column names and types are taken from the Set A.
If you need union and you know that sets have no duplicates, use Union All instead. Result will be the same, but query will be faster (no duplicates checking).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s