How to structure Left join and exclude matching ID. WHERE IS NULL clause not working

Question

I'm wanting to select rows from the leftTable that do not have matchings in the rightTable but get the error 'incorrect syntax at WHERE'. It seems like a simple join but I can't get it to work with "WHERE r.id IS NULL".

Am I correct in understanding that a LEFT JOIN / WHERE rightTable.id IS NULL should produce a list of leftTable items that do not have a matching in the rightTable?

  SELECT l.*
  INTO #temp_length
  FROM leftTable l
  LEFT JOIN rightTable r
        ON l.id = r.id
        WHERE r.id IS NULL
        AND l.start <= r.start
        AND l.end > r.start
        WHERE l.type IN ('A', 'B', 'C')
        AND l.id IN (SELECT id FROM lookupTable WHERE d_type = 'Z')
        AND l.disabled = 'N'
        AND r.latest = 'Y';

This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] — philipxy, Mar 27 '19 at 03:02
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. An inner join on or where that requires a right table column to be not null after a left join on removes any rows extended by nulls, ie leaves only inner join on rows, ie "turns outer join into inner join". You have that. — philipxy, Mar 27 '19 at 03:02
Possible duplicate of [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, Mar 27 '19 at 03:03
That code doesn't even parse. There can only be one where per select. (Regardless of that particular problem:) Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS That includes DBMS tag. — philipxy, Mar 27 '19 at 03:05

score 0 · Answer 1 · answered Mar 27 '19 at 02:32

Try below - you've include one where clause whereas you used two and also move your others conditions except r.id IS NULL in ON clause

SELECT l.*
  INTO #temp_length
  FROM leftTable l
  LEFT JOIN rightTable r
        ON l.id = r.id AND l.start <= r.start
        AND l.end > r.start and l.type IN ('A', 'B', 'C') AND l.id IN (SELECT id FROM lookupTable WHERE d_type = 'Z')
        AND l.disabled = 'N'
        AND r.latest = 'Y'
        WHERE r.id IS NULL

How to structure Left join and exclude matching ID. WHERE IS NULL clause not working

1 Answers1