ORACLE SQL Keep Data Show Even On Another Table Column Parameter Not Existed

Question

I want to display data with another table with parameter DESCRIPTIONID.

Now I want to keep data shown even on another table is null/empty(not existed DESCRIPTIONID).

Below query work if both table have an DESCRIPTIONID.

SELECT D.DESCRIPTIONID, D.DESCRIPTION, D.PROFILEID_FK, D.ACTIVE, P.PROFILEID, P.PROFILE_NAME FROM WA_BT_TBL_DESCRIPTION D, WA_BT_TBL_PROFILE P WHERE D.PROFILEID_FK = P.PROFILEID AND D.PROFILEID_FK = 'PF0001' AND D.ACTIVE = 'Y' ORDER BY D.DATEADDED ASC

And here the query I want to improve:

SELECT
DISTINCT
D.DESCRIPTIONID,
D.DESCRIPTION,
Y.TIME
FROM
WA_BT_TBL_DESCRIPTION D,
WA_BT_TBL_DAY Y
WHERE
D.DESCRIPTIONID = Y.DESCRIPTIONID_FK AND
D.PROFILEID_FK = Y.PROFILEID_FK AND
D.PROFILEID_FK = 'PF0001'
ORDER BY Y.TIME ASC

Let's see on SQL Fiddle

Please try both query on SQL Fiddle. As you can see I want "Break Time II" keep show.

Finally I want the query show like below:

DESCRIPTIONID   DESCRIPTION          TIME
DS0003          Break Time I         10:00
DS0001          Operator Clock Time  15:30
DS0002          Working Time I       17:00
DS0024          Break Time II             <-- keep show this row(null/empty)

Tip of today: switch to modern, explicit `JOIN` syntax - easier to write (without errors), easier to read, and easier to convert to outer join if needed. (Hint!) — jarlh, Oct 04 '17 at 06:54

Yossi Vainshtein · Accepted Answer · 2017-10-04T07:04:31.753

2

Use outer join.

In the older and harder to understand syntax (the one you used), like this (note the (+) on the conditions):

SELECT
DISTINCT
  D.DESCRIPTIONID,
  D.DESCRIPTION,
  Y.TIME
FROM
  WA_BT_TBL_DESCRIPTION D,
  WA_BT_TBL_DAY Y
WHERE
  D.DESCRIPTIONID = Y.DESCRIPTIONID_FK(+) AND
  D.PROFILEID_FK = Y.PROFILEID_FK(+) AND
  D.PROFILEID_FK = 'PF0001'
ORDER BY Y.TIME ASC

In the more encouraged one (explicitly writing LEFT JOIN...ON):

SELECT
  DISTINCT
  D.DESCRIPTIONID,
  D.DESCRIPTION,
  Y.TIME
FROM
  WA_BT_TBL_DESCRIPTION D LEFT JOIN
  WA_BT_TBL_DAY Y ON D.DESCRIPTIONID = Y.DESCRIPTIONID_FK AND
  D.PROFILEID_FK = Y.PROFILEID_FK AND
  D.PROFILEID_FK = 'PF0001'

ORDER BY Y.TIME ASC

edited Oct 04 '17 at 07:04

answered Oct 04 '17 at 06:59

Yossi Vainshtein

3,845
4
23
39

I think it would be better to use explicit joins - they are easier to read and understand – dbajtr Oct 04 '17 at 07:02
1

Remove that ancient `(+)` query. It shouldn't be encouraged. – jarlh Oct 04 '17 at 07:35

dbajtr · Answer 2 · 2017-10-04T07:06:52.887

2

Few things, don't you the old style JOIN they are very outdated, You will need to just a LEFT OUTER JOIN. The WHERE clause on your query is limiting the return to results that exists in both tables :

WHERE
D.DESCRIPTIONID = Y.DESCRIPTIONID_FK

This is essentially an INNER JOIN.

Try this:

SELECT 
  D.DESCRIPTIONID, 
  D.DESCRIPTION,
  Y.TIME
FROM 
WA_BT_TBL_DESCRIPTION D
LEFT OUTER JOIN WA_BT_TBL_DAY Y ON D.DESCRIPTIONID = Y.DESCRIPTIONID_FK
WHERE
D.PROFILEID_FK = 'PF0001'
ORDER BY Y.TIME ASC

There is a post HERE which explains the different types of joins really well

edited Oct 04 '17 at 07:06

answered Oct 04 '17 at 07:01

dbajtr

2,024
2
14
22

Thanks for your tips. Noted it – HiDayurie Dave Oct 04 '17 at 07:23

ORACLE SQL Keep Data Show Even On Another Table Column Parameter Not Existed

2 Answers2