left join not returning left hand side-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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