Why isn't my condition in the ON clause working?

Question

I have two tables, for which I want to left join only rows in main_sys where curr_flag = 1 to integrated_sys. These queries are returning different rows, and I can't figure out why.

SEE REXTESTER FOR QUERY RESULTS: http://rextester.com/YYR54058

QUERY 1 - curr_flag filter is in WHERE clause
select 
    * 
from 
    main_sys m
    left join integrated_sys i  on (m.master_id_sysa = i.master_id_sysa 
                                    or m.master_id_sysb = i.master_id_sysb)
where
    m.curr_flag = 1


QUERY 2 - curr_flag filter is in ON clause
select 
    * 
from 
    main_sys m
    left join integrated_sys i  on (   (
                                       m.master_id_sysa = i.master_id_sysa 
                                       or m.master_id_sysb = i.master_id_sysb
                                       ) 
                                   and m.curr_flag = 1
                                   )

Table A: main_sys contains historical records for locations.

| hist_id | master_id_sysA | master_id_sysB | loc_desc    | curr_flag | eff_start_dt | eff_end_dt |
|---------|----------------|----------------|-------------|-----------|--------------|------------|
| 14009   | 1234           | 1234           | Detroit, MI | 1         | 7/2/2017     | 1/1/9999   |
| 14010   | 1234           | 1234           | Detroit, MI | 0         | 1/6/2017     | 7/1/2017   |
| 14011   | 1234           | 1234           | Detroit, MI | 0         | 9/2/2016     | 1/5/2017   |
| 14012   | 1234           | 1234           | Detroit, MI | 0         | 7/23/2016    | 9/1/2016   |
| 14013   | 1234           | 1234           | Detroit, MI | 0         | 5/31/2015    | 7/22/2016  |
| 90088   | 6655           | 6655           | Dover, DE   | 1         | 6/2/2015     | 6/21/2015  |
| 90087   | 6655           | 6655           | Dover, DE   | 0         | 6/1/2015     | 6/1/2015   |
| 90086   | 6655           | 6655           | Dover, DE   | 0         | 5/31/2015    | 5/31/2015  |
| 14413   | 8877           | NULL           | NULL        | 1         | 9/2/2017     | 12/31/9999 |
| 14412   | 8877           | 877            | Austin, TX  | 0         | 8/3/2017     | 9/1/2017   |
| 14411   | 8877           | NULL           | NULL        | 0         | 6/19/2017    | 8/2/2017   |
| 14410   | 8877           | 877            | NULL        | 0         | 2/18/2017    | 6/18/2017  |
| 14409   | 8877           | 877            | Austin, TX  | 0         | 2/16/2017    | 2/17/2017  |
| 14145   | 9595           | 9595           | Boston, MA  | 1         | 9/9/2006     | 10/10/2014 |
| 39014   | 9987           | 9987           | Atlanta, GA | 1         | 6/5/2017     | 1/1/9999   |
| 39013   | 9987           | 9987           | Atlanta, GA | 0         | 11/1/2016    | 6/4/2017   |
| 39012   | 9987           | 9987           | Atlanta, GA | 0         | 9/23/2016    | 10/31/2016 |
| 39011   | 9987           | 9987           | Atlanta, GA | 0         | 7/6/2016     | 9/22/2016  |
| 39010   | 9987           | 9987           | Atlanta, GA | 0         | 1/6/2016     | 7/5/2016   |

Table B - integrated_sys table has the current record of each location.

| loc_name       | master_id_sysA | master_id_sysB |
|----------------|----------------|----------------|
| Detroit, MI    | 1234           | 1234           |
| Atlanta, GA    | 9987           | 9987           |
| Dover, DE      | 6655           | 6655           |
| Boston, MA     | NULL           | 9595           |
| Tempe, AZ      | NULL           | 55             |
| Seattle, OR    | NULL           | 95             |
| Des Moines, IO | NULL           | 1478           |
| Bismarck, SD   | NULL           | 1515           |

This question explains the situation, but I still don't know why the ON clause query is returning rows where curr_flag is 0: Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?

Radim Bača · Accepted Answer · 2017-10-03T13:28:08.217

1

If the m.curr_flag = 1 is part of a LEFT JOIN condition then it is not used to select rows from the left table, only from the right table. LEFT JOIN simply keep all input rows from the left table despite any condition behind ON. Therefore, if you need to filter data of the left table then you should write such condition behind WHERE clause (your first query).

edited Oct 03 '17 at 13:28

answered Oct 03 '17 at 13:21

Radim Bača

10,646
1
19
33

sagi · Answer 2 · 2017-10-03T13:31:52.747

1

Because they are logically different.

Query 1 - Select all record from left table where curr_flag = 1 , keep all the records from the right table when the ON() clause is true -> m.master_id_sysa = i.master_id_sysa or m.master_id_sysb = i.master_id_sysb

Query 2 - Select all record from left table , keep all the records from the right table when the ON() clause is true -> (m.master_id_sysa = i.master_id_sysa or m.master_id_sysb = i.master_id_sysb) AND m.curr_flag = 1

EDIT: For clarification - the ON() clause of a left join does not filter records from the LEFT table, it filters only the RIGHT table.

edited Oct 03 '17 at 13:31

answered Oct 03 '17 at 13:21

sagi

40,026
6
59
84

Can you explain what you mean for Qry 2 - "keep all the records from the right table when the ON() clause is true"? Shouldn't only the rows where curr_flag = 1 be returned, or does the ON() clause not limit result sets? – psrpsrpsr Oct 03 '17 at 13:26

Why isn't my condition in the ON clause working?

2 Answers2