?
Solved

t-sql complement

Posted on 2016-11-08
8
Medium Priority
?
72 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 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 51

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 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 51

Accepted Solution

by:
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)

Open in new window

0
 
LVL 51

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 332 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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