From the course: Oracle Database 19c: Advanced SQL

Set operators and their order - Oracle Database Tutorial

From the course: Oracle Database 19c: Advanced SQL

Start my 1-month free trial

Set operators and their order

- [Instructor] I'm excited to show you how logical set operators intersect with Oracle database in a select statement, pun intended. I'll list them all and provide the rules of how to use them in your select statements. Here are two separate select statements. The first one selects from employees_comm which only has four columns and six rows. The select from the other table, employees, has 107 rows and many more columns than employees_comm. The employees_comm table has almost the same employees as in the employees table, but there is at least one Jane Dough that is not in, in employees. Both queries are fine. The results are what I want. But what if I want to have just one select statement that returns both? Use set operators. In the diagram are the four set operators, you can use in Oracle database. Union, union all, intersect and minus. In the Venn diagrams, the yellow area is the result of the set operation. Union combines two results sets and removes duplicates, leaving behind at most, the total of the number of rows of both results sets. Union all is similar, but doesn't remove duplicates. The total number of rows is always the sum of the number of rows in both results sets. Intersect takes two results sets and always returns only the rows in common between the two results sets. Finally, minus returns all the rows from the first result set, but doesn't return whatever rows are in the second result set. Going back to the example with the two queries, what set operators should be used to combine these two results sets? Well, it depends. I could use any of the set operators depending on which results I return from each query and whether I want to remove duplicates or not. The syntax for set operators is straightforward. Multiple select statements joined by any of the set operators. The diagram shows a subset of the select clause. Using a set operator is as simple as joining two subqueries, which is two select statements. The select statement in the example is a simple union between two select statements, two subqueries. There are some general rules that apply to all queries containing set operators. First, the number of columns in each results set, regardless of the set operator, must be the same and their data types compatible. Results from multiple set operators are processed in the order presented, but you can use parentheses to alter the order. For union and union all, the ordering doesn't matter, but it sure does, with multiple intersect and minus operators. You can have an order by when you combine the results sets with set operators, but you can have only one and it has to be at the end of the last subquery being combined. In the example, the union operator combines the results of two select statements and sorts them by employee_id. But wait, there's more, I've been talking about duplicates, all of the set operators remove duplicates except for union all which just concatenates the rows from both results sets and does not sort the results. You're more likely to use an order by, with two select statements combined with union all. Speaking of sorting, by default, all of the set operators sort the results set, ascending in column order. Again, the exception is union all where the order in which the rows are returned is indeterminate. Finally, column names from the first query are used in the resulting output. In the example, the result of the intersect operation is from two tables whose second column name is different. The results set uses the column name from the first subquery. Here are the highlights of set operations. You combine two or more subqueries with one of four set operators, union, union all, intersect and minus. Have as many set operators and subqueries from multiple individuals, select statements as you want. Just remember that order can matter, so use parentheses, if there is any doubt or ambiguity. Finally, if you need to order the results, other than the default ordering from union, intersect or minus, use an order by at the very end.

Contents