SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id INT);
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

  |--Table Scan(OBJECT:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl]), WHERE:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl].[id]=[@1]))

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)

       |--Table Scan(OBJECT:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl]), WHERE:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl].[id]=(69420)))

       |--Table Scan(OBJECT:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl]), WHERE:([fiddle_460e28fab6d74b2ea298ab011a2116cd].[dbo].[tbl].[id]=(69420)))