PostgreSQL: how to select all records from one table that do not exist in another table?

Problem: I have two tables one is Employee, and another is Address, and I want to fetch all those records from Employee, which doesn't exist in the Address table.

--Employee CREATE TABLE employee (        emp_id int GENERATED ALWAYS AS IDENTITY,        name text ); --Address CREATE TABLE address(        addr_id int GENERATED ALWAYS AS IDENTITY,        emp_id int,        address1 text ); INSERT INTO employee(name) VALUES('Dilip'),('Harish'),('Manish'),('Akanksha'),('Avinash'); INSERT INTO address(emp_id,address1) VALUES(1,'Noida'),(2,'Delhi'),(3,'Gurugram');

There are several techniques to fetch the records from the employee table which does not exist in another table; I will try to explain some technical PostgreSQL queries.

Using LEFT JOIN in PostgreSQL

  

SELECT name   FROM employee emp   LEFT JOIN address adr ON emp.emp_id = adr.emp_id   WHERE adr.emp_id IS NULL;

  

Analysis query: Here I am applying LEFT JOIN that returns the left side table (employee) full records and right side table(address) as null if emp_id not matched, so I took advantage of this and applied WHERE clause on the right side table(address).

Using NOT EXISTS in PostgreSQL

For the above problem NOT EXISTS is a suitable solution because it is fastest in PostgreSQL than other solutions.

SELECT name FROM employee emp   WHERE NOT EXISTS        (               SELECT emp_id                      FROM address                      WHERE emp_id = emp.emp_id        );

Using NOT IN in PostgreSQL

SELECT name   FROM employee WHERE emp_id NOT IN (   SELECT DISTINCT emp_id     FROM address);