Select Data Only in Table1 That Is Not in Table2 in SQL

This article guides you on how to query data that exists only in `table1` and not in `table2` in SQL. We will explore different methods such as `LEFT JOIN`, `NOT EXISTS`, and `NOT IN`.

In SQL, to retrieve data that exists in one table but not in another, you can use methods like LEFT JOIN, NOT EXISTS, or NOT IN. Below are some ways to achieve this.

Method 1: Using LEFT JOIN

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

Method 2: Using NOT EXISTS

SELECT *
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.id = t2.id
);

Method 3: Using NOT IN

SELECT *
FROM table1
WHERE id NOT IN (
    SELECT id FROM table2
);

Detailed explanation

Method 1 - LEFT JOIN:

• LEFT JOIN: Combines table1 with table2 based on the condition, returning all data from table1 and corresponding data from table2.
• WHERE t2.id IS NULL: Filters out rows from table1 that have no corresponding entries in table2.

Method 2 - NOT EXISTS:

• NOT EXISTS: Checks if there are any rows in table2 that match the id from table1. If none exist, that row will be selected.

Method 3 - NOT IN:

• NOT IN: Selects all rows from table1 where the id does not exist in the list of ids from table2.

System Requirements:

• SQL Database (MySQL, PostgreSQL, SQL Server, Oracle, etc.)

How to install the libraries needed to run the SQL code above:

No additional libraries need to be installed, just a SQL database to run the queries.

Tips:

• Choose the method that fits your specific situation, as each has its advantages and disadvantages. For example, LEFT JOIN can be easier to understand but might be slower when working with large tables.

---