asked on

t-sql complement

I saw this example.

http://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table

--select all records from one table that do not exist in another table?
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

how would I get the complement of the above query?
What I mean is select all records from table t1 that do exist in table2?

SOLUTION

Randy Poole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

You can use something more near to the English language:

SELECT t1.name
 FROM table1 t1
WHERE NOT EXISTS (SELECT 1
        FROM table2 t2
        WHERE t2.name t2.name = t1.name)

Open in new window

SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Oh, just realized now is for EXIST in table2. Just read it in a negative away :(
So better solution is provided by Steve Wales.

Jim Horn

In addition to the above correct answers, go to images.google.com and do a search for 'SQL JOIN', and you'll see all sorts of handy images that are Venn diagrams of two or more tables, and the T-SQL needed to pull them off.

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maqskywalker

ASKER

thanks.