Basic DB Using SQL Server (Part 7) - Set Operations in SQL
Hello again :) This was written by my lovely friend! She's new to the community, but she's one of the most incredible people I've ever met!
This 7th post will be about Set Operations. The topics we're covering today are...
- Meaning of
UNIONandUNION ALL - Meaning of
EXCEPTandINTERSECT - Union Compatible
- Using
APPLYoperator in MS-SQL Server
More information about SET operation
1) Meaning of UNION and UNION ALL
-- Q0 Find Customer Numbers and Names --
SELECT CustomerNum, CustomerName FROM Customer
-- Q1 UNION --
SELECT CustomerNum, CustomerName FROM Customer UNION
SELECT CustomerNum, CustomerName FROM Customer
UNION combines the results of two or more queries into a distinct single result set
- include all the rows that belong to all queries
- put lines from queries after each other
- remove the duplicates
Difference between UNION and JOIN
- JOIN makes a certesian product and subset it
-- Q2 UNION ALL --
SELECT CustomerNum, CustomerName FROM Customer UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
-- Q3 UNION ALL with ORDER BY
SELECT CustomerNum, CustomerName FROM Customer UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
ORDER BY CustomerNum
UNION ALL combines the two or more row sets and keeps duplicates
Order of UNION ALL matters
-- Q4 Add Condition
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;
-- Q5 be Q4 UNION ALL Q0
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
-- Q6 be Q0 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500
UNION keeps only "distinct" - order doesn't matter
-
relationship
- inclusive: the result will be the same
- exclusive: the result will be sorted in ascending order
-- Q7 be Q4 UNION Q0
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500
UNION
SELECT CustomerNum, CustomerName FROM Customer
-- Q8 be Q0 UNION Q4
SELECT CustomerNum, CustomerName FROM Customer
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500
- Since Q4 and Q0 have inclusive relationship, these two queries will have the same result
- Q0 includes the result of Q4
- Q0 includes the result of Q4
-- Q9 be Q0 WHERE Balance > 10,000
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000
-- Q10 be Q9 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;
-- Q11 be Q9 UNION Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;
- Since Q9 and Q4 have exclusive relationship, these two queries have different result
- UNION ALL: order matters
- UNION: result will be sorted in ascending order
-- Q12 be Q0 WHERE Balance > 5000;
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000
-- Q13 be Q12 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;
-- Q14
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;
- CusNum 408 from Q4 include in Q12
- UNION ALL: 408 appears twice
- UNION: 408 appears once
2) Meaning of EXCEPT and INTERSECT
-
EXCEPTreturns distinct rows from the left input that are NOT output by the right input query
-
INTERSECTretirns distinct rows that are output by both the left and right input queries operator.
-- Q15
SELECT CustomerNum FROM Orders
-- Q16
SELECT CustomerNum FROM Customer
-- Q17 be Q16 Customer EXCEPT 15 Order
SELECT CustomerNum FROM Customer
EXCEPT
SELECT CustomerNum FROM Orders
-- Q18 be Q16 Customer INTERSECT Q15 Order
SELECT CustomerNum FROM Customer
INTERSECT
SELECT CustomerNum FROM Orders
-- Q19 be Q15 Order EXCEPT Q16 Customer
SELECT CustomerNum FROM Orders
EXCEPT
SELECT CustomerNum FROM Customer
-- Q20 be Q15 Order INTERSECT Q16 Customer
SELECT CustomerNum FROM Orders
INTERSECT
SELECT CustomerNum FROM Customer
- Q17 Customers who does NOT order any product
- Q18 Customers who order a product
- Q19 There is NO Order without Customer
- Q20 Product that is ordered by Customers
3) UNION Compatibility
- Basic rules for combining the result sets of two or more queries that use
UNION,UNION ALL,EXCEPT,INTERSECT> 1) The number and order of the columns must be the SAME in all queries > 2) The data types must be compatible
4) APPLY operator
-
APPLYoperator allows user to invoke a table-valued function for each row returned by an outer table expression (left input) of a query.- Table-valued function: Right input
- Outer table expression: Left input
- The Right input is evaluated for each row from the Left input
-
Final Output
- Rows produced by
Applyoperator are combined
- Columns is the set om the Left input followed by the list columns returned by the Right input
- Rows produced by
-
CROSS APPLYacts asINNER JOIN- return only rows from the Outer table the produce a result set from the table-valued function
-
OUTTER APPLYacts asOUTER JOIN- return both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function
CROSS APPLY (INNER JOIN)
-- Q25 INNER JOIN
SELECT * FROM Customer
INNER JOIN Orders
ON Customer.CustomerNum = Orders.CustomerNum
-- Q26 CROSS APPLY
SELECT * FROM Customer
CROSS APPLY
(
SELECT *
FROM Orders
WHERE Customer.CustomerNum = Orders.CustomerNum
)AS T
SELECT * FROM Customer
SELECT * FROM Orders
OUTTER APPLY (OUTTER JOIN)
-- Q27 LEFT OUTTER JOIN
SELECT * FROM Customer
LEFT OUTER JOIN Orders
ON Customer.CustomerNum = Orders.CustomerNum
-- Q28 OUTER APPLY
SELECT * FROM Customer
OUTER APPLY(
SELECT *
FROM Orders
WHERE Customer.CustomerNum = Orders.CustomerNum
) AS T
Note: CROSS APPLY / OUTER APPLY is NOT always equivalent to INNER JOIN / OUTTER JOIN
-- Q29 CROSS APPLY
SELECT CustomerNum, CustomerName, T.TotalNumberOfOrders FROM Customer
CROSS APPLY
(
SELECT COUNT(*) AS TotalNumberOfOrders
FROM Orders
WHERE Customer.CustomerNum = Orders.CustomerNum
)AS T
-- Q29 LEFT OUTTER JOIN
SELECT c.CustomerNum, CustomerName, COUNT(o.CustomerNum) AS TotalNumberOfOrders FROM Customer c
LEFT OUTER JOIN Orders o
ON c.CustomerNum = o.CustomerNum
GROUP BY C.CustomerNum, CustomerName
-- Q30 CROSS APPLY with INNER JOIN
SELECT c.CustomerNum,c.CustomerName, T.SumPrice
FROM Customer c
CROSS APPLY (
SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
FROM Orders
INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
WHERE Orders.CustomerNum = c.CustomerNum
HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T
-- Q31 OUTER APPLY with INNER JOIN
SELECT c.CustomerNum,c.CustomerName, T.SumPrice
FROM Customer c
OUTER APPLY (
SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
FROM Orders
INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
WHERE Orders.CustomerNum = c.CustomerNum
HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T
-- Q32 CROSS APPLY twice
SELECT c.CustomerNum,c.CustomerName, T1.OrderNum, T2.SumPrice
FROM Customer c
CROSS APPLY
(
SELECT COUNT(o.OrderNum) AS OrderNum
FROM Orders o
WHERE c.CustomerNum = o.CustomerNum
)AS T1
CROSS APPLY (
SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
FROM Orders
INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
WHERE Orders.CustomerNum = c.CustomerNum
HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T2
-- Q33 OUTTER APPLY twice
SELECT c.CustomerNum,c.CustomerName, T1.OrderNum, T2.SumPrice
FROM Customer c
Outer APPLY
(
SELECT COUNT(o.OrderNum) AS OrderNum
FROM Orders o
WHERE c.CustomerNum = o.CustomerNum
)AS T1
Outer APPLY (
SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
FROM Orders
INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
WHERE Orders.CustomerNum = c.CustomerNum
HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T2
Top comments (0)