How to compare two tables in postgres

Question

I want compare two column values which come from two different queries. Can anyone suggest a query which compares two columns in Postgres?

Solution

Well, the easiest to understand--but not necessarily the fastest--is probably something like this. (But you might mean something else by "compare".)

-- Values in column1 that aren't in column2.
SELECT column1 FROM query1 
WHERE column1 NOT IN (SELECT column2 FROM query2);

-- Values in column2 that aren't in column1.
SELECT column2 FROM query2 
WHERE column2 NOT IN (SELECT column1 FROM query1);

-- Values common to both column1 and column2
SELECT q1.column1 FROM query1 q1
INNER JOIN query2 q2 ON (q1.column1 = q2.column2);

You can also do this in a single statement to give you a visual comparison. A FULL OUTER JOIN returns all the values in both columns, with matching values in the same row, and NULL where one column is missing a value that's in the other column.

SELECT q1.column1, q2.column2 FROM query1 q1
FULL OUTER JOIN query2 q2 ON (q1.column1 = q2.column2);

Comments

[jnivasreddy]

this is fine but i mean something by compare can't we compare two columns

[Mike Sherrill 'Cat Recall']

@jnivasreddy: Probably, but we need you to post some sample data, including inputs and the output you expect, to make it clear just what you're looking for. You can edit your original question. Type or copy and past sample data at the end. Highlight the sample data, and click '{}' to preserve its formatting.