<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Composite Queries in Oracle

Published on
4,030 Points
930 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Expert Comment

by:alsatham hussain
i can easily understand.

Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This video shows how to recover a database from a user managed backup
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month