0

I have 2 tables. I want to get the records from both the table using FULL OUTER JOIN while filtering out some data from one of the tables.

  1. 14026 - Total number of common records.
  2. 8428 - unique records in table 1
  3. 1512 - unique records in table 2.

The total records that I should get are 23966. However, the following queries return different results.

Query 1 -

SELECT DISTINCT pu.id,
       vm.id
FROM table1 vm
FULL OUTER JOIN (SELECT DISTINCT pu.id 
                 FROM table2 pu 
                 WHERE pu.column1 = 'filter1'
                 AND pu.column2 = 'filter2') pu ON vm.id = pu.id;

Output - 23966 rows (expected).

Query 2 -

SELECT DISTINCT pu.id,
       vm.id
FROM table1 vm
FULL OUTER JOIN table2 pu ON vm.id = pu.id AND pu.column1 = 'filter1' AND pu.column2 = 'filter2';

Output - 48804 rows.

Query 3 -

SELECT DISTINCT pu.id,
       vm.id
FROM table1 vm
FULL OUTER JOIN table2 pu ON vm.id = pu.id 
WHERE (pu.column1 = 'filter1' AND pu.column2 = 'filter2')
OR (pu.column1 IS NULL AND pu.column2 IS NULL);

Output - 21830.

According to my understanding at least the third query should also give me the same result as query 1. Can someone explain me how SQL Server is treating these 3 queries?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JHS
  • 7,761
  • 2
  • 29
  • 53
  • 4
    Your `Where` clause in the 3rd query is changing the `Outer Join` to an `Inner Join`, thus limiting the data. Those conditions should be in the `Or` clause. – Siyual Aug 26 '14 at 14:07
  • @Siyual - I thought including `IS NULL` in the `WHERE` clause would not filter out the data that is coming from `table1` but not present in `table2`. – JHS Aug 26 '14 at 14:13
  • That is true, but you also have `WHERE (pu.column1 = 'filter1' AND pu.column2 = 'filter2')` in the `Where` clause. In the first query, these are effectively in the `Outer Join` `On` clause. – Siyual Aug 26 '14 at 14:14
  • 1
    `WHERE` clause is after `full outer join` - from what I understand first join the tables then filter the results using `where` clause. – Pragati Sureka Aug 26 '14 at 14:18
  • @Siyual - When I do an `INNER JOIN` I get a total of 14026 records as mentioned in the question. – JHS Aug 26 '14 at 14:24
  • @JHS I don't see anything in the question mentioning an `Inner Join`? – Siyual Aug 26 '14 at 14:27
  • 1
    try to create a simple model, here is a start http://sqlfiddle.com/#!3/c259a1/4 – Paul Maxwell Aug 26 '14 at 14:29

0 Answers0