SET Operators In Oracle SQL



You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If an SQL statement  contains multiple set operators, then Oracle database evaluates them from the left to right unless parentheses explicitly specify another order.


The following list briefly describes the four set operations supported by Oracle SQL:



UNION ALL : Combines the results of two SELECT statements into one result set.
UNION : Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS : Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

INTERSECT : Returns only those rows that are returned by each of two SELECT statements.

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECT statements can be combined into a compound query by a set operation only if they satisfy the following two conditions:

  1. The result sets of both the queries must have the same number of columns.  
  2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

Rules and Restrictions on Set Operations


  • Column names for the result set are derived from the first SELECT.
  • If we want to use ORDER BY in a query involving set operations, we must place the ORDER BY at the end of the entire statement. The ORDER BY clause can appear only once at the end of the compound query.
  • Component queries are executed from top to bottom. If we want to alter the sequence of execution, use parentheses appropriately.
  • Set operations are not permitted on columns of type BLOB, CLOB, BFILE, and VARRAY, nor are set operations permitted on nested table columns.
  • Since UNION, INTERSECT, and MINUS operators involve sort operations, they are not allowed on LONG columns. However, UNION ALL is allowed on LONG columns.

COMMENTS

Name

Account Payables,1,Advance Table In OAF,1,AME,1,AOL Concepts,19,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,62,Interview Q/A,68,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,50,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle 10g,1,Oracle 11g,1,Oracle Access Manager,1,Oracle ADF,1,Oracle Alerts,2,Oracle AP,12,Oracle Apex,1,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Approvals Management,1,Oracle Apps,4,Oracle Apps DBA,2,Oracle Apps Interviews,36,Oracle Apps Tips,9,Oracle Apps Tutorials,98,Oracle AR,18,Oracle Assets,2,Oracle BAM,1,Oracle BI Publisher,1,Oracle BPEL,1,Oracle BPM,1,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle SOA,1,Oracle System Administrator,4,Oracle Workflow,4,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,52,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,4,
ltr
item
Oracle Apps Guy: SET Operators In Oracle SQL
SET Operators In Oracle SQL
Oracle Apps Guy
https://www.oracleappsguy.com/2012/05/set-operators-in-oracle-sql.html
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/2012/05/set-operators-in-oracle-sql.html
true
4186973960539901882
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share. STEP 2: Click the link you shared to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy