Oracle query not returning null values

Question

I was expecting the following query to return null rows where there wasn't a matching row in the WEBSITE_URL table when using the left join. So what i want to see is all url types from the URLTYPE table and any matching records in the WEBSITE_URL table.

SELECT
    WU.NC_WEBSITE_ID_FK,
    WU.TC_URL,
    UT.TC_URLTYPE_NAME
  FROM
    WEBSITE_URL WU,
    URLTYPE UT
  WHERE
    WU.NC_URL_TYPE_ID_FK(+) = UT.NC_URLTYPE_ID
  AND
    NC_WEBSITE_ID_FK = '1622'

This is the output:

1622    http://www.test.com                 PRODUCTION (PUBLIC)

1622    http://preprod.test.com             PREPRODUCTION

1622    http://review.test.com              REVIEW

select * from WEBSITE_URL where NC_WEBSITE_ID_FK = '1622' and NC_URL_TYPE_ID_FK not in (select NC_URLTYPE_ID from URLTYPE) — jarlh
– jarlh, Commented May 22, 2015 at 11:02

Robert · Accepted Answer · 2015-05-22 11:16:59Z

1

Try this:

SELECT
    WU.NC_WEBSITE_ID_FK,
    WU.TC_URL,
    UT.TC_URLTYPE_NAME
  FROM
    URLTYPE UT
  LEFT JOIN 
    WEBSITE_URL WU ON WU.NC_URL_TYPE_ID_FK = UT.NC_URLTYPE_ID
               AND WU.NC_WEBSITE_ID_FK = '1622'

edited May 22, 2015 at 11:16

answered May 22, 2015 at 10:57

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2015-05-22 13:50:01Z

1

Use a standard join type (outer or left in this case) rather than just in the old sql syntax

SELECT WU.NC_WEBSITE_ID_FK, WU.TC_URL, UT.TC_URLTYPE_NAME
FROM URLTYPE UT
FULL OUTER JOIN WEBSITE_URL WU ON UT.NC_URLTYPE_ID = WU.NC_URL_TYPE_ID_FK(+)
WHERE NC_WEBSITE_ID_FK = '1622'

edited May 22, 2015 at 13:50

answered May 22, 2015 at 10:58

Matt

15.1k28 gold badges109 silver badges161 bronze badges

1 Comment

mathielo Over a year ago

Please add some context instead of just spitting up some code.

Gordon Linoff · Accepted Answer · 2015-05-22 10:59:55Z

0

First, learn to use explicit join syntax. Few people really understand the old Oracle syntax anymore.

Second, use table aliases on all the columns.

If NC_WEBSITE_ID_FK comes from WEBSITE_URL, then the condition needs to be moved to the ON clause:

SELECT WU.NC_WEBSITE_ID_FK, WU.TC_URL, UT.TC_URLTYPE_NAME
FROM URLTYPE LEFT JOIN
     WEBSITE_URL WU
     ON WU.NC_URL_TYPE_ID_FK = UT.NC_URLTYPE_ID AND
        NC_WEBSITE_ID_FK = '1622';

Also, if NC_WEBSITE_ID_FK is a number, don't put the constant value in single quotes. It is misleading both to people and to the optimizer to mix data types.

answered May 22, 2015 at 10:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Jeepers Creepers Over a year ago

This is close to the output that I am after, the only thing that its not doing is outputting the NC_WEBSITE_ID_FK in the null rows, which i'm not sure is possible.

Gordon Linoff Over a year ago

@JeepersCreepers . . . If there is no match, how would it have a value for that column?

Collectives™ on Stack Overflow

Oracle query not returning null values

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related