both FULL OUTER JOIN and WHERE to get true result?

I wrote this script. if we comment the two part in WHERE clause, some rows ignored in result. can somebody tell me why?

In the first query it works true as FULL OUTER JOIN, but in the second query, the query results as INNER JOIN and return only one row, despite of using the WHERE clause in which the UNKNOWN condition resolves.

DECLARE @ALLDATA TABLE 
( 
[Id]        INT,
[T3Id]      INT,
[StockId]   INT,
[Serial]    INT,
[ISDel]     INT
)
INSERT INTO @ALLDATA ([Id],[T3Id],[StockId],[Serial],[ISDel])
Select 5 AS [Id],11 AS [T3Id],5 AS [StockId],0 AS [Serial],1 AS [ISDel]
UNION ALL
Select 1 AS [Id],11 AS [T3Id],5 AS [StockId],0 AS [Serial],1 AS [ISDel]
UNION ALL
Select 1 AS [Id],11 AS [T3Id],5 AS [StockId],1 AS [Serial],-1 AS [ISDel]
UNION ALL
Select 2 AS [Id],11 AS [T3Id],5 AS [StockId],2 AS [Serial],-1 AS [ISDel]
UNION ALL
Select 3 AS [Id],11 AS [T3Id],5 AS [StockId],3 AS [Serial],-1 AS [ISDel]
UNION ALL
Select 4 AS [Id],11 AS [T3Id],5 AS [StockId],4 AS [Serial],-1 AS [ISDel]

SELECT 'WITH CONDITIONS:'

SELECT 
 ISNULL(DataIns.[Id],DataDEL.[Id])      AS [Id]
,ISNULL(DataDEL.[StockId],-1)           AS [OLDStockId]
,ISNULL(DataDEL.[Serial],0)             AS [OLDSerial]
,ISNULL(DataIns.[StockId],-1)           AS [NEWStockId]
,ISNULL(DataIns.[Serial],0)             AS [NEWSerial]
,ISNULL(DataIns.[T3Id],DataDEL.[T3Id])  AS [T3Id]
,DataIns.[ISDel]                        AS [ISDel1]
,DataDEL.[ISDel]                        AS [ISDel2]
FROM @ALLDATA       AS DataIns
FULL OUTER JOIN @ALLDATA AS DataDEL 
ON 
DataDEL.Id = DataIns.Id 
AND  
DataDEL.ISDel = 1 
AND 
DataIns.ISDel = -1
WHERE ISNULL(DataDEL.[ISDel],1) = 1 AND ISNULL(DataIns.[ISDel],-1) = -1 

SELECT 'WITHOUT CONDITIONS:'

SELECT 
 ISNULL(DataIns.[Id],DataDEL.[Id])      AS [Id]
,ISNULL(DataDEL.[StockId],-1)           AS [OLDStockId]
,ISNULL(DataDEL.[Serial],0)             AS [OLDSerial]
,ISNULL(DataIns.[StockId],-1)           AS [NEWStockId]
,ISNULL(DataIns.[Serial],0)             AS [NEWSerial]
,ISNULL(DataIns.[T3Id],DataDEL.[T3Id])  AS [T3Id]
,DataIns.[ISDel]                        AS [ISDel1]
,DataDEL.[ISDel]                        AS [ISDel2]
FROM @ALLDATA       AS DataIns
FULL OUTER JOIN @ALLDATA AS DataDEL 
ON 
DataDEL.I开发者_开发技巧d = DataIns.Id 
--AND 
--  DataDEL.ISDel = 1 
--AND 
--  DataIns.ISDel = -1
WHERE ISNULL(DataDEL.[ISDel],1) = 1 AND ISNULL(DataIns.[ISDel],-1) = -1 

Desired Output:

1    5  0    5  1   11    -1       1
2   -1  0    5  2   11    -1    NULL
3   -1  0    5  3   11    -1    NULL
4   -1  0    5  4   11    -1    NULL
5    5  0   -1  0   11  NULL       1

---

Can somebody tell me why?

Because it's the way FULL OUTER JOIN works.

When you comment out the additional conditions in the ON clause, you in fact are getting an INNER JOIN (since id will always produce at least one match in a self-join).

Your first query does not make sense to me as well (or you have some really complicated requirements).

I believe you wanted this:

SELECT   ISNULL(DataIns.[Id],DataDEL.[Id])      AS [Id]
        ,ISNULL(DataDEL.[StockId],-1)           AS [OLDStockId]
        ,ISNULL(DataDEL.[Serial],0)             AS [OLDSerial]
        ,ISNULL(DataIns.[StockId],-1)           AS [NEWStockId]
        ,ISNULL(DataIns.[Serial],0)             AS [NEWSerial]
        ,ISNULL(DataIns.[T3Id],DataDEL.[T3Id])  AS [T3Id]
        ,DataIns.[ISDel]                        AS [ISDel1]
        ,DataDEL.[ISDel]                        AS [ISDel2]
FROM    (
        SELECT  *
        FROM    @alldata
        WHERE   isdel = 1
        ) dataDel
FULL JOIN
        (
        SELECT  *
        FROM    @alldata
        WHERE   isdel = -1
        ) dataIns
ON      dataDel.id = dataIns.id