MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t1 SELECT 1 id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;
CREATE TABLE t2 SELECT 1 id UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 4;
SELECT * FROM (SELECT * FROM t1) t11 JOIN t2 ON t11.id = t2.id;

id
id

1
1

1
1

2
2

2
2

SELECT * FROM t2 WHERE id IN ( SELECT id FROM t1 );

id

1

2

2