x
Solved

# t-sql complement

Posted on 2016-11-08
Medium Priority
110 Views
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?
0
Question by:maqskywalker

LVL 21

Assisted Solution

Randy Poole earned 332 total points
ID: 41879037
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE NOT t2.name IS NULL
0

LVL 54

Expert Comment

ID: 41879043
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)
``````
0

LVL 23

Assisted Solution

Steve Wales earned 332 total points
ID: 41879045
Another way:

SELECT t1.name
FROM table1 t1
JOIN table2 t2 ON t2.name = t1.name

A join inherently returns only those rows that exist in both tables.
0

LVL 54

Accepted Solution

Vitor Montalvão earned 1004 total points
ID: 41879047
Correction:
``````SELECT t1.name
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
FROM table2 t2
WHERE t2.name = t1.name)
``````
0

LVL 54

Expert Comment

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

LVL 66

Expert Comment

ID: 41879050
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.
1

LVL 38

Assisted Solution

Pawan Kumar earned 332 total points
ID: 41879067
Try..You can also use Except..

``````SELECT t1.name
FROM table1 t1

EXCEPT

SELECT t2.name
FROM table2 t2
``````
0

LVL 1

Author Closing Comment

ID: 41879079
thanks.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.