<

Go Premium for a chance to win a PS4. Enter to Win

x

Composite Queries in Oracle

Published on
4,145 Points
1,045 Views
1 Endorsement
Last Modified:
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
Comment
Author:Swadhin Ray
1 Comment
 

Expert Comment

by:alsatham hussain
i can easily understand.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month