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!

ampereFollow

/ampere111

This 7^th post will be about Set Operations. The topics we're covering today are...

1. Meaning of UNION and UNION ALL
2. Meaning of EXCEPT and INTERSECT
3. Union Compatible
4. Using APPLY operator 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

-- 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

• EXCEPT returns distinct rows from the left input that are NOT output by the right input query

• INTERSECT retirns 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

• APPLY operator 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 Apply operator are combined

  - Columns is the set om the Left input followed by the list columns returned by the Right input

• CROSS APPLY acts as INNER JOIN
  • return only rows from the Outer table the produce a result set from the table-valued function
• OUTTER APPLY acts as OUTER 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