Listing Data Only in table1 and Not in table2 in PostgreSQL

A guide on how to list data only present in `table1` and not in `table2` in PostgreSQL, using the `LEFT JOIN` method and filtering.

In this article, we will explore how to use PostgreSQL to list data that exists only in table1 and not in table2. We will use LEFT JOIN along with a WHERE condition to perform the necessary queries.

SQL code

-- Assuming we have two tables: 'table1' and 'table2'

-- Data in the 'table1' table
CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO table1 (name) VALUES
('Alice'),
('Bob'),
('Charlie');

-- Data in the 'table2' table
CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO table2 (name) VALUES
('Alice'),
('David');

-- Query to list data only in table1
SELECT table1.id, table1.name
FROM table1
LEFT JOIN table2 ON table1.name = table2.name
WHERE table2.name IS NULL;

Detailed explanation

• Create table1 table: Use CREATE TABLE to create the table1 with fields id and name.
• Insert data into table1: Use INSERT INTO to add records into table1.
• Create table2 table: Similarly, use CREATE TABLE to create the table2.
• Insert data into table2: Use INSERT INTO to add records into table2.
• SELECT query:
  • LEFT JOIN: Joins table1 with table2 based on the name field. This allows us to retrieve all records from table1 and only the matching records from table2.
  • WHERE table2.name IS NULL: Filters the records that do not exist in table2, thus retrieving only those records from table1 that have no matches in table2.

System Requirements:

• PostgreSQL 9.6 or later

How to install PostgreSQL:

You can install PostgreSQL from the official PostgreSQL website or use management tools like pgAdmin.

Tips:

• It's advisable to create indexes on fields used in queries to improve query performance.
• Use LEFT JOIN to retrieve all records from the main table while filtering out those not present in the secondary table.

---