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?
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'
;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
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.