CODEWITHSUNDEEP
oracle-databasejoin
user2536967
user2536967

Reputation: 25

Oracle left outer join return 0 rows with data in left dataset

I’m trying to get a list of hard-coded events and join in a table with actual events, so I can actually see if they were performed.

Here are my query:

SELECT a.Verification FROM (SELECT 'Verify ID changed' as Verification  FROM DUAL
UNION ALL
SELECT 'Verify manual Valve closed' as Verification FROM DUAL
UNION ALL
SELECT 'Verify manual Valve open' as Verification FROM DUAL
UNION ALL
SELECT 'Visually verify Things' as Verification FROM DUAL
) a
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC
WHERE c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

When I read the documentation for LEFT Outer join, then queues should always return the data from the left table, so why is my query returning 0 rows (there is nothing in the JOBEVENTS events table but left hard-coded values are always there).

Am i misunderstanding how LEFT OUTER JOIN works?

Upvotes: 0

Views: 201

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35613

Make the filter on the left joined table part of the join, not the where clause.

SELECT a.Verification, c.*
FROM (
      SELECT 'Verify ID changed' as Verification  FROM DUAL UNION ALL
      SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL
      SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL
      SELECT 'Visually verify Things' as Verification FROM DUAL
      ) a
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC
       AND c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'
;

Alternatively, you can to add to your where clause so that NULLS may be returned from the left joined table. e.g.:

SELECT a.Verification, c.*
FROM (
      SELECT 'Verify ID changed' as Verification  FROM DUAL UNION ALL
      SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL
      SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL
      SELECT 'Visually verify Things' as Verification FROM DUAL
      ) a
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC
WHERE ( c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'
      OR c.EVENTDESC IS NULL
      )
;

Always take care with outer joined tables when referenced in the where clause that you permit NULLS from that table, otherwise you create the equivalent conditions of an inner join (& sometimes called "implied inner join").

Upvotes: 1

Related Questions