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);


         

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...