Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference between CORRELATED and UNCORRELATED subqueries in DB2
The subquery is a nested query. When this subquery is executed only once and the result of this subquery is used to extract the data in the main query, then this type of subquery is known as UNCORRELATED subquery. On the other hand, when a subquery refers to the main query for each execution, then the subquery is known as CORRELATED subquery.
For example, if we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below UNCORRELATED subquery.
Example
SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)
There is a DB2 table ORDER_CHANNEL which stores the channels through which orders can be placed like ONLINE, AGENT, AFFILIATE, etc.
If we want to extract the order with the maximum amount for each channel, then we will use below CORRELATED subquery.
Example
SELECT ORDER_ID, ORDER_CHANNEL, ORDER_TOTAL FROM ORDERS T1 WHERE ORDER_TOTAL IN (SELECT MAX (T2.ORDER_TOTAL) FROM ORDERS T2 WHERE T1.ORDER_CHANNEL_ID = T2.CHANNEL_ID GROUP BY T2.CHANNEL_ID)
2K+ Views
