sql table comparisons – postgres

I have two tables with some columns being the same:

TABLE A

| Col1 | Col2 | Col3 |
+------+------+------+
|  1   |  aa  | ccc  |
|  2   | null | ccc  |
| null |  bb  | null |

---

TABLE B

|Col1  | Col2  | Col3| Col4 |
+------+-------+-----+------+
|  1   |  aa  | ccc  | aaaa |
|  2   | null | ccc  | cccc |
| null |  bb  | null | sss  |
|  4   |  bb  | null | ddd  |

I’d like to return the following:

|Col1  | Col2  | Col3| Col4 |
+------+-------+-----+------+
|  4   |  bb  | null | ddd  |

How do I check what rows from table B are in table A and also return Col4 (from table B) where they match in the query. I was using EXCEPT which worked great but now I need to have the outputs of Col4 in the returned query results.

Thanks.

Answer

Something like this?

SELECT  Col1, Col2, Col3, Col4
FROM    TableB
WHERE   NOT EXISTS (
  SELECT  1
  FROM    TableA
  WHERE   TableA.Col1 IS NOT DISTINCT FROM TableB.Col1
  AND     TableA.Col2 IS NOT DISTINCT FROM TableB.Col2
  AND     TableA.Col3 IS NOT DISTINCT FROM TableB.Col3
)

(Using IS NOT DISTINCT FROM to say that columns with null are equal to each other.)