Oracle set operators union, union all, intersect, and minus

Oracle set operators union, union all, intersect, and minus go straight to the topic: if we need to display the results of the two select statements as a whole, we need to use the union or union all keyword. Union (or union) is used to display multiple results together. The difference between union and union all is that union will automatically compress the repeated results in multiple result sets, while union all will display all the results, whether they are repeated or not. Www.2cto.com 1. union: Perform union operations on two result sets, excluding duplicate rows and sorting by default rules. union All: Perform union operations on two result sets, including duplicate rows without sorting. intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules; 4. minus: performs the Difference Operation on two result sets, excluding duplicate rows, and sorts the default rules at the same time. You can specify the Order by clause in the last result set to change the sorting method. Example: 1. union: [SQL] SELECT * FROM emp WHERE sal <1500 UNION SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000 ORDER BY 1; www.2cto.com 2. union all: [SQL] SELECT * FROM emp WHERE sal <1500 UNION ALL SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000 ORDER BY 1;
3. intersect: [SQL] SELECT deptno FROM dept intersect select distinct deptno FROM emp; [SQL] DEPTNO ------ 10 20 30 www.2cto.com 4. minus: [SQL] SELECT deptno FROM dept minus select distinct deptno FROM emp; [SQL] DEPTNO ------ 40