left join not returning left hand side

Question

SELECT U.UserID, U.FirstName, U.LastNAME,'2022-10-07' as TheDate, ES.DateWorking 
frOM Users U LEFT JOIN EventsStaff ES  on U.UserID = ES.UserID 
WHERE ES.DateWorking = '2022-10-07'

I can't see what is wrong with the above. There isn't an entry in EventsStaff for ES.DateWorking = '2022-10-07' so I would have expected a row to be returned with ES.DateWorking set to Null as it is a left join but I am not getting anything at all. Thanks

You querying WHERE ES.DateWorking = '2022-10-07', if there is no entry for ES.DateWorking = '2022-10-07' then it will not return anything? Please also put your code in a code block. — micah, Jan 14 '22 at 20:12
Does this answer your question? [LEFT JOIN does not return all the records from the left side table](https://stackoverflow.com/questions/25544986/left-join-does-not-return-all-the-records-from-the-left-side-table) — steven7mwesigwa, Jan 14 '22 at 20:27
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jan 14 '22 at 23:13

score 2 · Answer 1 · answered Jan 14 '22 at 21:00

2

The WHERE applies to all the rows the come back from the JOIN. You want

SELECT
    U.UserID,
    U.FirstName,
    U.LastNAME,
    '2022-10-07' as TheDate,
    ES.DateWorking 
FROM Users U
    LEFT JOIN EventsStaff ES ON
        U.UserID = ES.UserID
        AND
        ES.DateWorking = '2022-10-07'

Also please note how structuring statements makes it easier to see what's going on.

answered Jan 14 '22 at 21:00

Andy Lester

91,102
13
100
152

Yep that makes sense - thanks – P Whittaker Jan 15 '22 at 08:53

score 1 · Answer 2 · answered Jan 14 '22 at 20:23

That date condition should be part of the join, not the filter. See line #12:

SQL> with
  2  users (userid, firstname) as
  3    (select 100, 'Little' from dual union all
  4     select 200, 'Foot'   from dual
  5    ),
  6  eventsstaff (userid, dateworking) as
  7    (select 100, date '2022-10-08' from dual union all
  8     select 200, date '2021-12-25' from dual
  9    )
 10  select u.userid, u.firstname, es.dateworking
 11  from users u left join eventsstaff es on u.userid = es.userid
 12                                       and es.dateworking = date '2022-10-07';

    USERID FIRSTN DATEWORKING
---------- ------ -------------------
       100 Little
       200 Foot

If date you mentioned (2022-10-07) existed in the table (see line #7), then you'd get it in the result:

SQL> with
  2  users (userid, firstname) as
  3    (select 100, 'Little' from dual union all
  4     select 200, 'Foot'   from dual
  5    ),
  6  eventsstaff (userid, dateworking) as
  7    (select 100, date '2022-10-07' from dual union all
  8     select 200, date '2021-12-25' from dual
  9    )
 10  select u.userid, u.firstname, es.dateworking
 11  from users u left join eventsstaff es on u.userid = es.userid
 12                                       and es.dateworking = date '2022-10-07';

    USERID FIRSTN DATEWORKING
---------- ------ -------------------
       100 Little 07.10.2022
       200 Foot

SQL

score 1 · Answer 3 · edited Jan 15 '22 at 00:17

1

It should have been

SELECT    U.UserID, 
          U.FirstName, 
          U.LastNAME,
          '2022-10-07' as TheDate, 
          ES.DateWorking 
FROM      Users U 
LEFT JOIN EventsStaff ES  
  ON      U.UserID = ES.UserID 
  AND     ES.DateWorking = '2022-10-07'

not where

edited Jan 15 '22 at 00:17

Jeremy Caney

7,102
69
48
77

answered Jan 14 '22 at 20:42

P Whittaker

89
1
8

left join not returning left hand side

3 Answers3