MySQL 8.0

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

CREATE TABLE t1 
SELECT 1 id, 1 val UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 3;

CREATE TABLE t2 
SELECT 1 id, 1 val UNION ALL
SELECT 2, 2 UNION ALL
SELECT 4, 4 UNION ALL
SELECT NULL, 5;

Records: 3  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;


id
val
id
val


1
1
1
1

2
2
2
2

3
3
null
null


SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;


id
val
id
val


3
3
null
null


SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
            AND t2.id IS NULL;

SELECT * FROM t2 WHERE t2.id IS NULL;


id
val
id
val


1
1
null
null

2
2
null
null

3
3
null
null




id
val


null
5


SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.id > 1;


id
val
id
val


2
2
2
2

3
3
null
null


SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
            AND t1.id > 1;

SELECT * FROM t1 WHERE t1.id > 1;


id
val
id
val


1
1
null
null

2
2
2
2

3
3
null
null




id
val


2
2

3
3

id	val	id	val
1	1	null	null
2	2	null	null
3	3	null	null