SQL: Set Operators
Set operators allow the results of multiple queries to be combined into a single result set. They include UNION, UNION ALL, INTERSECT, and EXCEPT for MS-SQL server.
UNION
In some cases, you may need to combine data from two or more tables into a result set. A Union clause is used to perform this operation. The tables that you need to combine can be tables with similar data in the same database or different databases.
UNION ALL
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned). Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.
INTERSECT
SQL INTERSECT operator combines two select statements and returns only the dataset that is common in both statements. To put it simply, it acts as a mathematical intersection. In mathematics, the intersection of A and B is the common data present in both A and B.
EXCEPT
The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements. The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.
CASE EXPRESSION
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
Target: List Customer’s last names in Charlotte and Aurora.
What is the difference?
If there is anyone active in both cities, we can get all rows by “Union All”.
Get Baris Gül’s stories in your inbox
Join Medium for free to get updates from this writer.
But on the other hand, we can get unique rows in the same case by “Union”.
Target: Write a query that returns customers whose first name is Thomas or the last name is Thomas.
Target: Write a query that returns brands that have products for both 2018 and 2019.
We want to see also other columns;
- If there is a statement that is in“()” in the table “product. brand”, we can see all columns with the query above.
Target: Write a query that returns customers who have orders for all of 2018, 2019, and 2020.
Explanation: In this example, we wanted first to see the “customer_id” that is active for these three years. But the information we need is “last_name” and “first_name”, therefore we used this condition in another query with “IN”.
Target: Write a query that returns brands that have a 2018 model product but not a 2019 model product.
Here some brands exist in “2018” but not “2019”.
Target: Generate a new column containing the mean of the values in the “Order _Status” column.
1= Pending, 2=Procesing, 3= Rejected, 4=Completed.
Target: Create a new column containing the labels of the customer’s email service providers.
