Composite Queries in Oracle

Swadhin Ray
CERTIFIED EXPERT
Published:
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
What are composite queries in Oracle ?

When multiple queries are joined together using any set operators within SQL then they are called composite queries. UNION, INTERSECT, MINUS, UNION ALL are some of the operators used to get certain desired results.

Syntax of composite queries:
 
SELECT <<COLUMN NAME>>
                      FROM <<TABLE NAME>>
                      [ WHERE ] [ GROUP BY … ] 
                      { set operator }
                      SELECT <<COLUMN NAME>>
                      FROM <<TABLE NAME>>
                      [ WHERE … ] [ GROUP BY … ] 
                      [ ORDER BY … ]

Open in new window


We can use multiple set operators and add more SQL queries to satisfy any business logic but we have to make sure that the ORDER BY clause is always at the end of the final query.
 
The syntax should be something like below:
 
SELECT <<COLUMN NAME>>
                      FROM <<TABLE NAME>>
                      [ WHERE … ] [ GROUP BY … ]  
                      { set operator }
                      SELECT <<COLUMN NAME>>
                      FROM <<TABLE NAME>>
                      [ WHERE … ] [ GROUP BY … ]  
                      { set operator }
                      SELECT <<COLUMN NAME>>
                      FROM <<TABLE NAME>>
                      [ WHERE … ] [ GROUP BY … ]  
                      [ ORDER BY … ]

Open in new window


To know more about the syntax you can refer to SQL Language Reference book from Oracle's on-line documentation, Chapter 19 “SQL Statements: SAVEPOINT to UPDATE”. Select the section “SELECT” or try searching for “Recursive Subquery Factoring “. In this section you will find all the Set operators used for composite SQL queries.

UNION ALL: This returns the union of everything and includes the duplicate rows too.
UNION: This operator will remove the duplicate if both queries have the same set of rows.
INTERSECT: Will fetch all the rows present in both the queries like the in operator.  
MINUS: This will fetch all the rows that are present in one query and remove the others from it; this is simple subtraction.

To demonstrate the composite queries look at these examples.
Example 1:
 
Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as *****
                      
                      SQL> SELECT 'A', 'B'
                         FROM DUAL
                       UNION
                       SELECT 'C', 'D' FROM DUAL; 
                       
                      'A' 'B'
                      --- ---
                      A   B
                      C   D

Open in new window


If you run this individually you will see the first query will fetch A and B with column names A and B, and the next query will fetch C and D with column names C and D, but when we run both of them using the UNION set operator, we get all the rows but the column names refer to the first query and not the second one. Even if we define the column names we still get the first query column names and try to match the datatype:
 
SQL> SELECT 'A' as col1, 'B' as col2
                         FROM DUAL
                       UNION
                       SELECT 'C' as col3, 'D' as col4 FROM DUAL;
                      
                      COL1 COL2
                      ---- ----
                      A    B
                      C    D

Open in new window


For example if I am using a number in first query and VARCHAR in second query, what will happen? If the datatype does not match then we will see the error as below:
 
SQL> SELECT 'A' as col1, 1 as col2
                        FROM DUAL
                      UNION
                      SELECT 'C' as col3, 'D' as col4 FROM DUAL;
                      
                      SELECT 'A' as col1 ,1 as col2  FROM DUAL UNION SELECT 'C' as col3,'D' as col4 FROM DUAL
                      
                      ORA-01790: expression must have same datatype as corresponding expression

Open in new window


There is another factor we need to consider while writing composite SQL queries:  “ORDER BY“. This should always be at the end of a composite query as like below:
 
SQL> SELECT 'A' as col1, 'B' as col2
                        FROM DUAL
                      UNION
                      SELECT 'C' as col3, 'D' as col4
                        FROM DUAL
                      UNION
                      SELECT 'E' as col5, 'F' as col6 from DUAL order by 1;
                      
                      COL1 COL2
                      ---- ----
                      A    B
                      C    D
                      E    F

Open in new window


If we reorder the positions of the values from third query to the second, we still have ORDER BY 1 set (alphabetical order):
 
SQL> SELECT 'A' as col1, 'B' as col2
                        FROM DUAL
                      UNION
                      SELECT 'E' as col3, 'F' as col4
                        FROM DUAL
                      UNION
                      SELECT 'C' as col5, 'D' as col6 from DUAL order by 1;
                      
                      COL1 COL2
                      ---- ----
                      A    B
                      C    D
                      E    F

Open in new window


This shows the error we will get if we put the ORDER BY condition in the second or first query and remove it from the last query:
 
  SQL> SELECT 'A' as col1, 'B' as col2
                         FROM DUAL
                       UNION
                       SELECT 'C' as col3, 'D' as col4
                         FROM DUAL
                        order by 1
                       UNION
                       SELECT 'E' as col5, 'F' as col6 from DUAL;
                      
                      SELECT 'A' as col1, 'B' as col2
                         FROM DUAL
                       UNION
                       SELECT 'C' as col3, 'D' as col4
                         FROM DUAL
                        order by 1
                       UNION
                       SELECT 'E' as col5, 'F' as col6 from DUAL
                      
                      ORA-00933: SQL command not properly ended
                      
                      SQL> SELECT 'A' as col1, 'B' as col2
                        FROM DUAL
                       order by 1
                      UNION
                      SELECT 'C' as col3, 'D' as col4
                        FROM DUAL
                      UNION
                      SELECT 'E' as col5, 'F' as col6 from DUAL;
                      
                      SELECT 'A' as col1, 'B' as col2
                        FROM DUAL
                       order by 1
                      UNION
                      SELECT 'C' as col3, 'D' as col4
                        FROM DUAL
                      UNION
                      SELECT 'E' as col5, 'F' as col6 from DUAL
                      
                      ORA-00933: SQL command not properly ended
                      
                      SQL>

Open in new window


We can see that if we put the ORDER BY in above query we get the “ORA-00933” error. But if we remove the ORDER BY condition and move it to the end we will not see any error in the composite query:
 
SQL> SELECT 'A' as col1, 'B' as col2
                        2    FROM DUAL
                        3  UNION
                        4  SELECT 'C' as col3, 'D' as col4
                        5    FROM DUAL
                        6  UNION
                        7  SELECT 'E' as col5, 'F' as col6 from DUAL order by 1
                        8  ;
                      
                      COL1 COL2
                      ---- ----
                      A    B
                      C    D
                      E    F

Open in new window


Thank you for reading my article. Please feel free to leave me some feedback or to suggest any future topics. Please 'Vote this article as helpful' if you liked on the bug green button at the bottom of this article.

Looking forward to hear from you - Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )
1
2,990 Views
Swadhin Ray
CERTIFIED EXPERT

Comments (1)

alsatham hussainOracle developer

Commented:
i can easily understand.

Thanks

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.