Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

t-sql complement

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

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 23

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 53

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 53

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 38

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

572 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