SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id INT PRIMARY KEY);
INSERT INTO tbl (id) SELECT * FROM generate_series(0, 100000);
100001 rows affected

SET SHOWPLAN_TEXT ON;
SELECT * FROM tbl WHERE id = 69420;

StmtText

SELECT * FROM tbl WHERE id = 69420;

StmtText

  |--Clustered Index Seek(OBJECT:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[PK__tbl__3213E83F2F75367A]), SEEK:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[id]=[@1]) ORDERED FORWARD)

SELECT * FROM tbl  WHERE id IN 
  ( SELECT id FROM tbl WHERE id = 69420);

StmtText

SELECT * FROM tbl  WHERE id IN 
  ( SELECT id FROM tbl WHERE id = 69420);

StmtText

  |--Nested Loops(Left Semi Join)

       |--Clustered Index Seek(OBJECT:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[PK__tbl__3213E83F2F75367A]), SEEK:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[id]=(69420)) ORDERED FORWARD)

       |--Clustered Index Seek(OBJECT:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[PK__tbl__3213E83F2F75367A]), SEEK:([fiddle_78e0f0653891426f9d981cef0a4dfad1].[dbo].[tbl].[id]=(69420)) ORDERED FORWARD)