Hello All,
I have below query where need to fetch person details along with his favorite sport and frequency of playing every week, I am only interested in showing 'Soccer' and 'NBA' for everything else we can leave it as blank.
Below query is working as expected, My question is if we can rewrite below SQL by eliminating the need to using outer join on each condition, if that would have any affect on the performance of the query.
WITH t1 AS
(SELECT 'KJ' NAME, 1234 PERSON_ID, 'Detroit' CITY FROM DUAL
UNION ALL
SELECT 'HJ', 3456, 'Cleveland' FROM DUAL
UNION ALL
SELECT 'PJ', 2345, 'Akron' FROM DUAL
UNION ALL
SELECT 'AJ', 4567, 'BOston' FROM DUAL
UNION ALL
SELECT 'CJ', 5678, 'Atlanta' FROM DUAL
),
t2 AS
(SELECT 1234 person_id, 'NBA' Sport, 1 Frequency, 90 Score, 'A' Status FROM DUAL
UNION ALL
SELECT 3456, 'Soccer',3, 75, 'A' FROM DUAL
UNION ALL
SELECT 2345, 'NBA',1, 65,'A' FROM DUAL
UNION ALL
SELECT 2345, 'Soccer', 2, 80, 'I' FROM DUAL
UNION ALL
select 5678, 'Soccer',5,70,'A' FROM DUAL
)
SELECT t1.name, t1.person_id, t1.city, t2.sport, t2.score
FROM t1, t2
WHERE t1.person_id = t2.person_id(+)
AND t2.SPORT(+) IN ('NBA','Soccer')
AND t2.status(+) = 'A';
Expected Output
NAME PERSON_ID CITY SPORT SCORE
KJ 1234 Detroit NBA 90
HJ 3456 Cleveland Soccer 75
PJ 2345 Akron NBA 65
CJ 5678 Atlanta Soccer 70
AJ 4567 BOston
Note: Table t1 has around 50 Million records.
Thanks for your time.