PostgreSQL – How to Select Rows From One Table That Do Not Exist in Another Table
To select rows from one table that do not exist in another table in PostgreSQL, you can use the NOT EXISTS clause.
SELECT * FROM table1 WHERE NOT EXISTS ( SELECT 1 FROM table2 WHERE table1.column_name = table2.column_name );
where table1 and table2 are the actual table names, and column_name is the column you want to compare.
In the below example we have 2 tables, products and orders. Products table has 5 products as shown below:
Order table has order information. Each order has a product_id associated with it.
To identify the products that have not received any orders, we can use the below query:
SELECT * FROM products WHERE NOT EXISTS ( SELECT 1 FROM orders WHERE products.id = orders.product_id );
Above query will retrieve the product_id’s from Products table that do not exist in Orders table.