1z0-071 Oracle Database SQL – USING THE SET OPERATORS

1. UNION – COMBINING ROWS FROM TWO QUERIES WITHOUT DUPLICATES

Using Set operators, we can combine the results of multiple queries into a single result set. For example, select Call one from Tab One will give this result. Select call three from tab two will give this result. Now I can combine both into a single result set by using Set operations. Union is one of the Set operators. Let us explore it. Now, if I do, select call one from tab one. Union select. Call three from tab two. Then it will give a result like this. What happens is it takes the first result, sorts it. Then it takes the second result, sorts it, then it joins them. While joining them, it removes the duplicates.

Also note that it displays the whole result under call One. Because call one is from the first query. The column names from the first query will represent the results. Let us check it out. Select call one from tab one. Call three from Tab two. Select call one from tab one. Union call three from Tab 21234.If you notice, there is one two from Tab One and Two three four from Tab Two. When we use the Union Set operator, it sorts both the results and removes the duplicate, which is an extra two in this result, select Call One. Call two from tab one. Select call three. Call four from tab two. Now let us use the union operator.

You would see one A, two B from table one from table Two, and there is no duplicate value to be removed. You will also notice that everything has been brought under the column names specified in the first query. Column One, column Two. Now let us insert arrow to be in tab Two. Let me commit. Select call one. Call two from tab one. One year, two b. Notice this. Two B. Select call three. Call four from tab two. We have one more to be here. So now let us see what happens to this two B row. We see only one two in the union output. That’s because duplicates are removed.

2. UNION ALL – COMBINING ROWS FROM TWO QUERIES WITH DUPLICATES

Set operator is union all. And union. All is similar to union. It joins the results of multiple queries, but it doesn’t sort the queries. Also, it doesn’t remove the duplicates. So an Union All operation would give a result like this. You can see the value two appearing twice. So in situations where we are not worried about duplicates appearing in the results, we can use Union all, which would be faster than Union. Since the union operator will spend extra time in sorting and removing the duplicates, union all is generally faster than Union operator. Let us check it out.

Select call one from tab one. The values are one and two. Select call three from tab two. Values are 2234. We have one and two on the tab one. So if I do union, it will remove the duplicates 1234. However, if I do Union all, it will not remove the duplicates one and two from tab 12234 or from tab two. Select call one. Call two from tab one. Select call three. Call four from tab two. And an union operation removes the duplicates. And a Union All operation brings the duplicate values, which is two b two.

3. INTERSECT – FETCHING THE COMMON ROWS FROM THE RESULTS

Set operator is intersect, and intersect operator returns just the rows that are common in both the queries results. For example, select call one from tab one brings one and two. Select call three from tab two brings 2234. What is common between both the results? Two. So this query with intersect operator will just bring a value of two in the total result. Please note that it removes the duplicates also. Now let us check it out. Select call one from tab one. Select call III from tab two and intersect brings the value two, which is common in both the results, and it also removes the duplicates and it will also sort the result. Select call one. Call two from tab one. Select call three. Call four from tab two and intersect brings two, which is common in both the results.

4. MINUS – FIRST RESULT SET MINUS SECOND RESULT SET

Set. Operator is minus. A minus operator returns just the rows in the first queries result set which are not there in the second queries result set. For example, select call one from tab one brings one and two. Select call three from tab two brings 2234. What is there in the first result set that is also there in the second result set? Two. So in the first result set, which is one and two, the minus operator will remove two and will just bring one. Now, let us just flip the queries here. Select call three from tab two is the first query.

Its result is 2234 the second query is select call one from tab one its result is one two so what is there in the first result set that is also there in the second result set? Two. So from the first result set, which is 2234, the minus operator will remove two. The final result will be three and four.Also note the column name in the result set. As I said previously, it will always bring the column names that we specify in the first query. Since call three has been specified in the first query which is select call three from tab two. The result also displays call three. Now let us check it out.

Select call one from tab one. Select call three from tab two. Minus operator removes two, which is common between first result set and the second result set and just brings the rest of the first result set, which is one. I am flipping the queries, it brings three and four. Also note the column name. Select call one. Call two from tab one. Select call three. Call four from tab tab two A minus will just bring one a the name is call one. Call two. I am flipping the queries, it brings two D, three E, four F. The name is call three and call four.