Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-comparing-tables/ below:

PostgreSQL - Comparing Tables - GeeksforGeeks

Last Updated : 15 Jul, 2025

Comparing tables in PostgreSQL is a common task when you need to identify differences between datasets. This can be especially useful when you are merging tables, validating data, or performing quality checks. In this article, we'll explore two of the most commonly used techniques for comparing tables in PostgreSQL:

The EXCEPT operator returns the rows from the first table that are not in the second table. It's a straightforward way to find differences between two tables.

First, let’s create table two tables named 'foo' and 'bar', and insert some sample data for the demonstration.

CREATE TABLE foo (
    ID INT PRIMARY KEY,
    NAME VARCHAR (50)
);
INSERT INTO foo (ID, NAME)
VALUES
    (1, 'a'),
    (2, 'b');
CREATE TABLE bar (
    ID INT PRIMARY KEY,
    NAME VARCHAR (50)
);
INSERT INTO bar (ID, NAME)
VALUES
    (1, 'a'),
    (2, 'b');

The 'foo' table has the same structure and data as the 'bar' table.

Next, we update one row in the 'bar' table.

UPDATE bar
SET name = 'c'
WHERE
    id = 2;

Now our tables look like below:

Then, to find the rows in the 'foo' table but not in the bar table, we use the following query.

Query:

SELECT
    ID,
    NAME,
    'not in bar' AS note
FROM
    foo
EXCEPT
    SELECT
        ID,
        NAME,
        'not in bar' AS note
    FROM
        bar;

Output:

We used EXCEPT operator that returns the rows in the 'foo' table but not in the 'bar' table. We can apply the same technique to find the rows that are in the 'bar' table but not in the 'foo' table.

SELECT
    ID,
    NAME,
    'not in foo' AS note
FROM
    bar
EXCEPT
    SELECT
        ID,
        NAME,
        'not in foo' AS note
    FROM
        foo;

Output:

The FULL OUTER JOIN approach is another powerful technique for comparing tables. It returns all rows when there is a match in either table, and fills with NULL where there is no match.

SELECT
    id,
    name
FROM
    foo
FULL OUTER JOIN bar USING (id, name)
WHERE
    foo.id IS NULL
OR bar.id IS NULL;

Output:

This query returns the rows that exist in one table but not the other.

To find the number of rows that are in the foo table but not bar table and vice versa, we use the COUNT function as follows:

SELECT
    COUNT (*)
FROM
    foo
FULL OUTER JOIN bar USING (id, name)
WHERE
    foo.id IS NULL
OR bar.id IS NULL;

Output:

This gives you a quick overview of how many rows are mismatched between the tables.

Both the EXCEPT-UNION and FULL OUTER JOIN techniques are effective for comparing tables in PostgreSQL, each with its advantages:

• EXCEPT-UNION: Best when you need clear, distinct differences between two datasets.
• FULL OUTER JOIN: Useful for detailed comparisons, especially when working with complex datasets.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4