How to compare two tables in postgres

Question

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

Answer 1

Well, the easiest way to understand - but not necessarily the fastest - maybe something like this. (But you could 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 one statement to give you a visual comparison. A FULL OUTER JOIN returns all the values ​​in both columns with the corresponding values ​​in the same row and NULL , where in one column there is no value indicated in the other column.

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