Using EXCEPT clause in PostgreSQL

Question

I am trying to use the EXCEPT clause to retrieve data from table. I want to get all the rows from table1 except the one's that exist in table2. As far I understand, the following would not work:

CREATE TABLE table1(pk_id int, fk_id_tbl2 int);
CREATE TABLE table2(pk_id int);

Select fk_id_tbl2
FROM table1
Except
Select pk_id
FROM table2

The only way I can use EXCEPT seems to be to select from the same tables or select columns that have the same column name from different tables.

Can someone please explain how best to use the explain clause?

Answer 1

Your query seems perfectly valid:

SELECT fk_id_tbl2 AS some_name
FROM   table1
EXCEPT  -- you may want to use EXCEPT ALL
SELECT pk_id
FROM   table2;

Column names are irrelevant to the query. Only data types must match. The output column name of your query is fk_id_tbl2, just because it's the column name in the first SELECT. You can use any alias.

What's often overlooked: the subtle differences between EXCEPT (which folds duplicates) and EXCEPT ALL - which keeps all individual unmatched rows. More explanation and other ways to do the same, some of them much more flexible:

• Select rows which are not present in other table

Details for EXCEPT in the manual.