what is the difference between these two queries

I have written two queries to check the differences between two tables, both shown below. Query 2 shows me the correct results.

In each table there is one record that is not in the other. So I wanted a query which would show both these records, which Query 2 does. It show me the 90 rows where the tables match plus another 2 rows, one where a record is in tblIH but not in tblTempN and another record which is in tblTempN but not in tblIH.

Whereas Query 1 shows me only the 90 records where the tables match and one extra row where the record is in tblIH but not in tblTempN - it does NOT however show me the record in tblTempN which is not in tblIH - why? I thought using a full outer join would show me all records from both tables? I don't really understand the difference between the two queries as they seem the same to me?

Query 1

select coalesce(h.Sedol, nav.Sedol) Sedol,  
       coalesce(nav.Name, h.Name) Name, 
       isnull(h.Nominal, 0) - isnull(nav.Nominal, 0) NomDiff
  from tblIH h full outer join tblTempN nav 
    on h.Sedol = nav.Sedol and h.Code = nav.Code
 where h.FundCode = 'ABC' and h.DatePrice = '2015-03-20'

Query 2

;with hld as
(
    select Sedol, Name, FX, Nominal from tblIH
    where DatePrice = '2015-03-20' and FundCode = 'ABC'
), nav as
(
    select Sedol, Name, Nominal from tblTempN
    where DateAcc = '2015-03-20' and FundCode = 'ABC'
)
select coalesce(hld.Sedol, nav.Sedol) Sedol,  
       coalesce(nav.Name, hld.Name) Name, 
       isnull(hld.Nominal, 0) - isnull(nav.Nominal, 0) NomDiff 
  from hld full outer join nav
    on hld.Sedol = nav.Sedol

Answers


In full outer join if you don't have satisfied condition field values from that table fetched as null

I suppose you missed to write conditions nav.FundCode = 'ABC' and nav.DatePrice = '2015-03-20'

but apart from this you are missing one more fundamental that where clause will be applicable on the result from that full outer join.

So actually you are getting 90+1+1 out of full outer join but your where condition is filtering one record from this result because for one desired record h.FundCode and h.DatePrice value is NULL.

You can use NVL function while checking for these condition.


Need Your Help

Why does DBCC SHRINKFILE work inconsistently in a database job?

sql-server sql-server-2005 sql-agent-job log-files

DBCC SHRINKFILE always works when I run it manually on a log file, even when I get the following message: