Solved

t-sql complement

Posted on 2016-11-08
8
61 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 65

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 28

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
Moving away from Access 2003 adp files 4 47
sql trace 4 27
Migrate SQL 2005 DB to SQL 2016 4 28
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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