How to index in SQL Postgres - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to index in SQL Postgres

Author

Topic

Jamalo
Starting Member

6 Posts

Posted - 2010-08-06 : 08:18:00

I have two tables, Table A and Table B. I want to compare column X in Table A with Column Y in Table B to get the values that are not matching. Colum X and Y have more then one milion entries each. I do run this queiry:

SELECT *
FROM "TABLE_A" t
WHERE not
t."X" in (select distinct "Y" from "TABLE_B");

I do get the results, but it take more than 36 hours to get this result.
Is there any other simple and qucik way to get the results?

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 08:32:43

Possibly EXISTS will be quicker than IN, or maybe JOIN will be:


SELECT *
FROM [TABLE_A] t
WHERE NOT EXISTS
(select * from [TABLE_B] AS B WHERE B.[Y] = t.[X])

SELECT *
FROM [TABLE_A] t
    LEFT OUTER JOIN [TABLE_B] AS B 
        ON B.[Y] = t.[X]
WHERE B.[Y] IS NULL

-- Find missing from EITHER table:
SELECT t.[X], B.[Y]
FROM [TABLE_A] t
    FULL OUTER JOIN [TABLE_B] AS B 
        ON B.[Y] = t.[X]
WHERE (t.[X] IS NULL OR B.[Y] IS NULL)

Jamalo
Starting Member

6 Posts

Posted - 2010-08-06 : 08:52:43

THANK YOU very much Kristen,

That worked soooooooooooooo FAST!!
Tahnks again :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources