Basic DB Using SQL Server (Part 7) - Set Operations in SQL
Tags: sql
Published: 2020-05-14 15:50:13

#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! 
{% user ampere111 %} 

This 7<sup>th</sup> post will be about **Set Operations**. The topics we're covering today are...

1. [Meaning of `UNION` and `UNION ALL`](#union)
2. [Meaning of `EXCEPT` and `INTERSECT`](#except)
3. [Union Compatible](#compat)
4. [Using `APPLY` operator in MS-SQL Server](#apply)

<!-- Brief Explanation about Set Operations -->

<a href = https://www.sqlshack.com/sql-union-overview-usage-and-examples/> More information about SET operation </a>

<a name="union"></a>
## 1) Meaning of `UNION` and `UNION ALL`
![alt text](https://dev-to-uploads.s3.amazonaws.com/i/41xhf1jzx94icwyu9xja.png)
``` sql
-- 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

<a href = https://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union> Difference between `UNION` and `JOIN` </a>
- `JOIN` makes a certesian product and subset it
---

``` sql
-- 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**

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qv6qxxronood0kl27ci5.png)

---
### Order of UNION ALL matters

```sql
-- 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
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/4f7k6kvhny7gu5efwosv.png)

---

### 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
```sql
-- 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
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/86wue1zl3lelabl8nreu.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zw1zsdnpl0fy5edqj9pw.png)

```sql
-- 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;
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mrs8oyqxpydcptuhvp39.png)

- CusNum 408 from Q4 include in Q12
  - UNION ALL: 408 appears twice
  - UNION: 408 appears once

<a name="except"></a>
## 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](https://dev-to-uploads.s3.amazonaws.com/i/rop0dpuyg2dy0vh39zip.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/klrkx9sbu0s8tn9rzinz.png)

```sql
-- Q15 
SELECT CustomerNum FROM Orders

-- Q16
SELECT CustomerNum FROM Customer
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/u85cwyhusy3pizjwd0ad.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/1g0jxbwwr3wibj7pci1c.png)

<a name="compat"></a>
## 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

<a name="apply"></a>
## 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](https://dev-to-uploads.s3.amazonaws.com/i/hr7jatyrs601ofsgxiuj.png)

### CROSS APPLY (INNER JOIN)
```sql
 -- 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
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/u6wahuypdd78jere54z8.png)

### OUTTER APPLY (OUTTER JOIN)
```sql
-- 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
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8hjd5fj5svcdwbpxhtm7.png)

#### Note: `CROSS APPLY` / `OUTER APPLY` is NOT always equivalent to `INNER JOIN` / `OUTTER JOIN` 

```sql
-- 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
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gvy81doot3vu6560idcv.png)

```sql
-- 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
```
```sql
-- 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
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/cvj26t0nbblnaqd35bzl.png)

```sql
-- 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
```
```sql
-- 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
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/3lem898euqsf6sivgeyd.png)

<!-- Add Exercise and Homework -->