CODEWITHSUNDEEP
sqloracle-database
Jeepers Creepers
Jeepers Creepers

Reputation: 115

Oracle query not returning null values

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

Upvotes: 2

Views: 412

Answers (3)

Matt
Matt

Reputation: 15061

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'

Upvotes: 1

Robert
Robert

Reputation: 25753

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'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 0

Related Questions