Solved

t-sql complement

Posted on 2016-11-08
8
67 Views
Last Modified: 2016-11-08
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
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 83 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 50

Expert Comment

by:Vitor Montalvão
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)

Open in new window

0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 83 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 50

Accepted Solution

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

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
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

by:Jim Horn
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 29

Assisted Solution

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

SELECT t1.name
FROM table1 t1

EXCEPT 

SELECT t2.name 
FROM table2 t2

Open in new window

0
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41879079
thanks.
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question