DEV Community

imrinzzzz
imrinzzzz

Posted on

Basic DB Using SQL Server (Part 7) - Set Operations in SQL

#sql

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!

ampere111 image

ampere

/ampere111

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

alt text

-- Q0 Find Customer Numbers and Names -- SELECT CustomerNum, CustomerName FROM Customer -- Q1 UNION -- SELECT CustomerNum, CustomerName FROM Customer UNION SELECT CustomerNum, CustomerName FROM Customer
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

UNION ALL combines the two or more row sets and keeps duplicates

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode
  • Since Q4 and Q0 have inclusive relationship, these two queries will have the same result
    • Q0 includes the result of Q4 Alt Text 
-- 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;
Enter fullscreen mode Exit fullscreen mode
  • 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

Alt Text

-- 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;
Enter fullscreen mode Exit fullscreen mode

Alt Text

  • 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

Alt Text

  • INTERSECT retirns distinct rows that are output by both the left and right input queries operator.

Alt Text

-- Q15  SELECT CustomerNum FROM Orders -- Q16 SELECT CustomerNum FROM Customer
Enter fullscreen mode Exit fullscreen mode

Alt Text

-- 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
Enter fullscreen mode Exit fullscreen mode
  • 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

Alt Text

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

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

-- 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
Enter fullscreen mode Exit fullscreen mode 
-- 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
Enter fullscreen mode Exit fullscreen mode

Alt Text

-- 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
Enter fullscreen mode Exit fullscreen mode 
-- 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
Enter fullscreen mode Exit fullscreen mode

Alt Text

Top comments (0)