SQL Left Join On Date Filter

Question

I am trying to figure out why these two SQL snippets return different results.

SQL A

SELECT date, id 
FROM Table A 
LEFT JOIN Table B ON A.id = B.id 
                  AND a.date = date_add('day', -1, CURRENT_DATE);

SQL B

SELECT date, id 
FROM Table A 
LEFT JOIN Table B ON A.id = B.id 
WHERE a.date = date_add('day', -1, CURRENT_DATE);

SQL A returns all the dates from table A whereas SQL B returns only yesterday.

Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — philipxy, Nov 12 '21 at 21:15
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] — philipxy, Nov 12 '21 at 21:16
@marc_s It's not certain that by "SQL" they meant "query" & not "DBMS". (So I left it unchanged when I edited.) Also "(code) snippet" is now a SO technical term so I'd suggest only using it for that & not to mean code fragment. — philipxy, Nov 12 '21 at 21:38

score 0 · Answer 1 · answered Nov 12 '21 at 21:13

It is happening because in second query you are filtering results in WHERE clause.

SQL A : Since it's left join, you will get all the results from Table A and you will see NULL values for Table B whenever you don't find a match.

SQL B : You will get all the results same as SQL A, but then you have WHERE condition WHERE a.date = date_add('day',-1,CURRENT_DATE); which only keeps yesterday's records.

Your results are changing on the basis of where are you putting your DATE condition. SQL A has in JOIN itself and SQL B has it in WHERE.

SQL Left Join On Date Filter

1 Answers1