Difference in query

Database
Enthusiast

Difference in query

Hi All,

Mentioned below are 2 queries .Can anyone have a look & tell me whether the output of both the queries would be same or not.

Query 1

SELECT
S.ACCT_EVENT_TYPE_CD
,S.CUST_ACCT_EVENT_ID
,S.EVENT_USED_BY_CD
,S.EVENT_SEQUENCE_CD
,S.DW_AS_OF_DT
,S.CUST_ACCT_EVENT_CD
,S.EXT_EVENT_REF_CD
,S.EVENT_START_DT
,S.EVENT_END_DT
,S.AUDIT_USER_ID
,S.AUDIT_UPDT_TS
,S.DW_ACTN_IN
,S.DW_DATA_CHG_TS
,S.DW_LOAD_TS
FROM
IDW_STAGE.CUST_ACCT_EVENT_S S
LEFT OUTER JOIN
(SELECT ACCT_ID,
CUST_ID,
EVNT_USE_BY_CD,
EVNT_SEQ_CD,
MAX(DW_EFF_DT) DW_EFF_DT
FROM IDW_DATA.CUST_ACCT_EVENT_T
GROUP BY 1,2,3,4) T
ON
((T.ACCT_ID=0 AND T.CUST_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='C') OR
(T.CUST_ID=0 AND T.ACCT_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='A') )
AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CD
AND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CD
WHERE
S.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')
AND
S.DW_ACTN_IN in ('I','U')

============================================================================

Query 2

SELECT
S.ACCT_EVENT_TYPE_CD
,S.CUST_ACCT_EVENT_ID
,S.EVENT_USED_BY_CD
,S.EVENT_SEQUENCE_CD
,S.DW_AS_OF_DT
,S.CUST_ACCT_EVENT_CD
,S.EXT_EVENT_REF_CD
,S.EVENT_START_DT
,S.EVENT_END_DT
,S.AUDIT_USER_ID
,S.AUDIT_UPDT_TS
,S.DW_ACTN_IN
,S.DW_DATA_CHG_TS
,S.DW_LOAD_TS
FROM
IDW_STAGE.CUST_ACCT_EVENT_S S
LEFT OUTER JOIN
(SELECT ACCT_ID,CUST_ID,EVNT_USE_BY_CD,EVNT_SEQ_CD,MAX(DW_EFF_DT) DW_EFF_DT
FROM IDW_DATA.CUST_ACCT_EVENT_T
GROUP BY 1,2,3,4) T
ON
T.ACCT_ID=0 AND T.CUST_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='C'
AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CD
AND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CD
WHERE
S.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')
AND
S.DW_ACTN_IN in ('I','U')
UNION
SELECT
S.ACCT_EVENT_TYPE_CD
,S.CUST_ACCT_EVENT_ID
,S.EVENT_USED_BY_CD
,S.EVENT_SEQUENCE_CD
,S.DW_AS_OF_DT
,S.CUST_ACCT_EVENT_CD
,S.EXT_EVENT_REF_CD
,S.EVENT_START_DT
,S.EVENT_END_DT
,S.AUDIT_USER_ID
,S.AUDIT_UPDT_TS
,S.DW_ACTN_IN
,S.DW_DATA_CHG_TS
,S.DW_LOAD_TS
FROM
IDW_STAGE.CUST_ACCT_EVENT_S S
LEFT OUTER JOIN
(SELECT ACCT_ID,CUST_ID,EVNT_USE_BY_CD,EVNT_SEQ_CD,MAX(DW_EFF_DT) DW_EFF_DT
FROM IDW_DATA.CUST_ACCT_EVENT_T
GROUP BY 1,2,3,4) T
ON
T.CUST_ID=0 AND T.ACCT_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='A'
AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CD
AND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CD
WHERE
S.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')
AND
S.DW_ACTN_IN in ('I','U')
=========================================================

Is there going to be some difference in query due to Left Outer join.Probably the output would have been same I there would have been Inner join.

5 REPLIES
Enthusiast

Re: Difference in query

I hope there will be a difference in the number of rows returned.
Enthusiast

Re: Difference in query

Thanxs for the reply.
I think u meant the output would be different.
If so i would really appreciate if u can tell me the reason.is it because of left outer join which will act different in both the queries.

i
Enthusiast

Re: Difference in query

Results may vary depending upon your data , your On clause condition and where clause condition and the path chosen by optimizer to process this query.In the below mentioned query which is more or less like the one you have written, the number of rows in each case differed.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test1 left join test2 on test1.a=test2.a or test1.b=test2.b
where test1.c=2;

select * from test1 left join test2 on test1.a=test2.a or test1.b=test2.b
where test1.c=2;

*** Query completed. 6 rows found. 8 columns returned.
*** Total elapsed time was 1 second.

a b c d a b
----------- ----------- ----------- ----------- ----------- ----------- ---
2 2 2 2 2 3
1 1 2 2 1 2
2 2 2 2 2 3
1 1 2 2 1 2
2 2 2 2 1 2
2 2 2 2 1 2

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test1 left join test2 on test1.a=test2.a
where test1.c=2
union
select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

select * from test1 left join test2 on test1.a=test2.a
where test1.c=2
union
select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

*** Query completed. 7 rows found. 8 columns returned.
*** Total elapsed time was 1 second.

a b c d a b
----------- ----------- ----------- ----------- ----------- ----------- ---
1 1 2 2 ? ? <------Extra row
1 1 2 2 1 2
1 1 2 2 1 2
2 2 2 2 1 2
2 2 2 2 1 2
2 2 2 2 2 3
2 2 2 2 2 3

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test1 left join test2 on test1.a=test2.a
where test1.c=2
union all
select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

select * from test1 left join test2 on test1.a=test2.a
where test1.c=2
union all
select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

*** Query completed. 7 rows found. 8 columns returned.
*** Total elapsed time was 1 second.

a b c d a b
----------- ----------- ----------- ----------- ----------- ----------- ---
1 1 2 2 1 2
1 1 2 2 1 2
1 1 2 2 ? ? <----- Extra row
2 2 2 2 2 3
2 2 2 2 2 3
2 2 2 2 1 2
2 2 2 2 1 2

BTEQ -- Enter your DBC/SQL request or BTEQ command:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test1 left join test2 on test1.a=test2.a
where test1.c=2;

select * from test1 left join test2 on test1.a=test2.a
where test1.c=2;

*** Query completed. 4 rows found. 8 columns returned.
*** Total elapsed time was 1 second.

a b c d a b
----------- ----------- ----------- ----------- ----------- ----------- ---
2 2 2 2 2 3
1 1 2 2 1 2
2 2 2 2 2 3
1 1 2 2 1 2

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

select * from test1 left join test2 on test1.b=test2.b
where test1.c=2;

*** Query completed. 3 rows found. 8 columns returned.
*** Total elapsed time was 1 second.

a b c d a b
----------- ----------- ----------- ----------- ----------- ----------- ---
2 2 2 2 1 2
1 1 2 2 ? ?
2 2 2 2 1 2

BTEQ -- Enter your DBC/SQL request or BTEQ command:

Donno if the above query resembles your query, but however the answer set in both the cases is different.extra row is seen in case of union query

Enthusiast

Re: Difference in query

Thanks for the information with the example.
Examples have made the difference in output quite clear.
Reason why I want to modify query1 is because its taking a lot of time to
retrieve records where as the UNION query is fetching the records in very less time.
So is there a way which can be used to avoid the extra row from Union Query
with out hampering the performance.

Index present on the tables are

UNIQUE PRIMARY INDEX PI_CUST_ACCT_EVENT_S (
ACCT_EVENT_TYPE_CD ,
CUST_ACCT_EVENT_ID ,
EVENT_USED_BY_CD ,
EVENT_SEQUENCE_CD ) on table CUST_ACCT_EVENT_S

PRIMARY INDEX NUPI_CUST_ACCT_EVENT_T
( ACCT_ID ,
CUST_ID ,
EVNT_USE_BY_CD ,
EVNT_SEQ_CD ) on table CUST_ACCT_EVENT_T

No Of Records in
CUST_ACCT_EVENT_S is nearly 21000

No Of Records in
CUST_ACCT_EVENT_T is nearly 10 million.
Enthusiast

Re: Difference in query

Dhanchik,

Try to compare the explain plans for each of the query to understand the processing steps in each case.No matter what, I do not think the union query would be the best alternative to your problem query.I beleive the plan for each case would be different. Also, you should understand the join condition and see when and how the where clause condition is applied to your query. How the left outer join is perceived in your case and how many rows are returned in each step.