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 |