T-SQL - Left Outer Joins - Filters in the where and on on clause

Question

I am trying to compare two tables to find rows in each table that are not in the other. Table 1 has a groupby column to create 2 datasets in table 1.

groupby number ----------- ----------- 1 1 1 2 2 1 2 2 2 4 

Table 2 contains only one column.

 number ----------- 1 3 4 

Thus, table 1 has a value of 1,2,4 in group 2, and table 2 has a value of 1,3,4.

I expect the following result when joining group 2:

 `Table 1 LEFT OUTER Join Table 2` T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 2 2 NULL `Table 2 LEFT OUTER Join Table 1` T1_Groupby T1_Number T2_Number ----------- ----------- ----------- NULL NULL 3 

The only way I can get this to work is if I put a where clause for the first connection:

 PRINT 'Table 1 LEFT OUTER Join Table 2, with WHERE clause' select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number --****************************** WHERE table1.groupby = 2 AND table2.number IS NULL 

and the filter is ON for the second:

 PRINT 'Table 2 LEFT OUTER Join Table 1, with ON clause' select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number AND table1.groupby = 2 --****************************** WHERE table1.number IS NULL 

Can someone come up with a way not to use the filter in the on section, but in the where clause?

In the context of this, I have an intermediate area in the database, and I want to identify new entries and entries that have been deleted. The groupby field is the equivalent of batchid for extraction, and I compare the last extract in the temp table with the batch yesterday stored in the partioneds table, which also has all the previously extracted batches. Code for creating tables 1 and 2:

 create table table1 (number int, groupby int) create table table2 (number int) insert into table1 (number, groupby) values (1, 1) insert into table1 (number, groupby) values (2, 1) insert into table1 (number, groupby) values (1, 2) insert into table2 (number) values (1) insert into table1 (number, groupby) values (2, 2) insert into table2 (number) values (3) insert into table1 (number, groupby) values (4, 2) insert into table2 (number) values (4) 

EDIT:

A little more context - depending on where I put the filter, different results. As stated above, the where clause gives me the correct result in one state and ON in another. I am looking for a consistent way to do this.

Where -

 select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number --****************************** WHERE table1.groupby = 2 AND table2.number IS NULL 

Result:

 T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 2 2 NULL 

On -

 select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number AND table1.groupby = 2 --****************************** WHERE table2.number IS NULL 

Result:

 T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 1 1 NULL 2 2 NULL 1 2 NULL 

Where (table 2 this time) -

 select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number AND table1.groupby = 2 --****************************** WHERE table1.number IS NULL 

Result:

 T1_Groupby T1_Number T2_Number ----------- ----------- ----------- NULL NULL 3 

On -

 select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number --****************************** WHERE table1.number IS NULL AND table1.groupby = 2 

Result:

 T1_Groupby T1_Number T2_Number ----------- ----------- ----------- (0) rows returned 

Answer 1

If you filter the left outer joined table in the WHERE clause, you create an inner join

See also this wiki page: WHERE conditions on LEFT JOIN

Answer 2

with LEFT OUTER JOINS, you must filter in the ON clause or use this:

 WHERE (LeftJoinTable.ID IS NULL OR LeftJoinTable.Col1=YourFilter) 

if you just filter in WHERE:

 WHERE LeftJoinTable.Col1=YourFilter 

you will drop the parent jumper whenever LeftJoinTable.ID (which makes the INNER JOIN join) is gone.

By placing the filter in the ON position, you throw an LEFT JOIN string exception, but you do not exclude the parent connection string, this is how it works.

EDIT . the only way to filter the LEFT OUTER JOIN table is in the ON clause if you don't want to use OR, as shown in the first code example above. There is nothing wrong with filtering the LEFT OUTER JOIN in the ON clause, so you do it.

Answer 3

As you write your query, it’s advisable to include the union in the ON clause, since you specifically want to join the values ​​in group “2” from table 1.

An alternative is prefilter table1 for the group you are interested in, for example

 select t1Group.groupby, t1Group.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join (SELECT * FROM table1 WHERE groupby=2) t1Group on table2.number = t1Group.number WHERE t1Group.number IS NULL 

Answer 4

 SELECT dbo.table1.groupby as [T1_Groupby], dbo.table1.number as [T1_Number], t21.number as [t21_Number] FROM dbo.table1 LEFT OUTER join dbo.table2 t21 ON dbo.table1.number = t21.number LEFT OUTER join dbo.table2 t22 ON dbo.table1.groupby= t22.number WHERE t21.number is null AND t22.number is null 

Answer 5

  select dbo.table1.groupby as [T1_Groupby], dbo.table1.number as [T1_Number], t22.number as [t22_Number] from dbo.table1 right outer join (select dbo.table1.groupby, dbo.table2.number as number from dbo.table1 right OUTER join dbo.table2 on dbo.table1.number = dbo.table2.number where dbo.table1.number is null) t22 on dbo.table1.groupby = t22.number where dbo.table1.groupby is null 

Answer 6

I myself struggled with this - and at the end of the day I had to select the data from the table with the Where clause and put it in the temp table, and then use the Left external join in the Temp table.

 SELECT table1.GroupBy, table1.number INTO #Temp FROM table1 WHere GroupBy = 2 SELECT table2.Groupby, #temp.number From table2 LEFT OUTER JOIN #temp on table2.Groupby = #temp.Groupby