DEV Community

imrinzzzz
imrinzzzz

Posted on

Basic DB Using SQL Server (Part 7) - Set Operations in 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!

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)