- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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.
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.
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)
- Related Articles
- Difference between correlated and non-collreated subqueries in SQL
- Difference between UNION and UNION ALL in DB2
- What is the difference between EXECUTE IMMEDIATE and EXECUTE WITH PREPARE in DB2?
- What are TRIGGERS in DB2? What is the difference between TRIGGERS and STORED PROCEDURES?
- Which one is preferred in between MySQL EXISTS and IN while using in Subqueries?
- What are QMF and SPUFI? What is the difference between them for accessing DB2 tables?
- What is the difference between DB2 JOIN and UNION? Explain with the help of an example
- What are MySQL subqueries and its general categories?
- What are single row and multiple row subqueries?
- Difference Between & and &&
- Purpose and usage of subqueries in DB with the help of an example
- Example and usage of JOINS in DB2
- Difference between and in Ionic framework?
- Difference between "." and "#" selector in CSS