View previous topic :: View next topic
|
Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Hi,
I am executing below given queries using SPUFI and getting the same number !
JOIN Query:
Code: |
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A,
DB01.ORDER_PERS B
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY = B.ORDER_KEY
WITH UR;
|
Is the below one a Sub-Query?
Code: |
SELECT COUNT((A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY IN
(SELECT B.ORDER_KEY FROM DB01.ORDER_PERS B
WHERE A.ORDER_KEY = B.ORDER_KEY)
WITH UR;
|
Is the below one Correlated Sub-Query?
Code: |
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND EXISTS
(SELECT B.ORDER_KEY FROM DB01.ORDER_PERS B
WHERE A.ORDER_KEY = B.ORDER_KEY)
WITH UR;
|
All of them are giving me the correct COUNT only. I would like to know which variety each one belongs to..... Please help.
Thanks. |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Please ignore two braces after COUNT in the 2nd query |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
2nd and 3rd are co-related subqueries one with IN clause and other with EXISTS ... |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
ashimer, Thanks for the info. How a sub-query would look like for the same scenario? |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Code: |
SELECT COUNT(A.ORDER_KEY) FROM DB01.ORDER_TAB A
WHERE A.ORDER_CD = '13'
AND A.ORDER_YR = '2007'
AND A.ORDER_IND = 'Y'
AND A.ORDER_KEY IN
(SELECT ORDER_KEY FROM DB01.ORDER_PERS )
|
|
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Thanks. |
|
Back to top |
|
|
|