Join returns no results In a trigger, I have:

SELECT TOP 1 t.Id
FROM deleted d
INNER JOIN Table t 
ON t.Id = d.Id

SELECT TOP 1 t.Id
FROM Table t
WHERE t.Id = (SELECT TOP 1 d.Id FROM deleted d)


Table is a related table that references the trigger's table. Trigger is after Delete

Why does the first return no result but the second does?
As Aaron Bertrand has pointed out on his comment, the only reason you has no records could be related to the order. But IMHO first query should return one record unless there are no coincidences on the related table.




CREATE TABLE T
(
    ID INT, 
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO T VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D');

CREATE TABLE TREF
(
    ID INT, 
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO TREF VALUES (1,'A'),(2,'B'),(5,'C'),(6,'D');

CREATE TABLE T1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RES NVARCHAR(MAX)
);
GO



8 rows affected






CREATE TRIGGER TRG_T_DELETE
ON T
AFTER DELETE
AS
BEGIN

    INSERT INTO T1
    SELECT 'COUNT: ' + CAST(COUNT(*) AS VARCHAR(10)) FROM deleted;

    INSERT INTO T1
    SELECT 'deleted: ' + (SELECT * FROM deleted WHERE ID = d.ID FOR XML RAW) 
    FROM   deleted d;

    INSERT INTO T1
    SELECT TOP 1
           'join: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW) 
    FROM   deleted d
    JOIN   TREF T
    ON     T.ID = D.ID;


    INSERT INTO T1
    SELECT TOP 1 'IN: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW) 
    FROM   TREF T
    WHERE  T.ID = (SELECT TOP 1 ID FROM deleted ORDER BY ID DESC)    
END
GO



✓






DELETE FROM T WHERE ID &gt;= 2;
SELECT * FROM T1;
GO



ID | RES                          
-: | :----------------------------
 1 | COUNT: 3                     
 2 | deleted: &lt;row ID=&quot;2&quot; F1=&quot;B&quot;/&gt;
 3 | deleted: &lt;row ID=&quot;3&quot; F1=&quot;C&quot;/&gt;
 4 | deleted: &lt;row ID=&quot;4&quot; F1=&quot;D&quot;/&gt;
 5 | join: &lt;row ID=&quot;2&quot; F1=&quot;B&quot;/&gt;   



dbfiddle here