Solved

SQL Select | Join tables where data not in second table

Posted on 2009-07-07
2
414 Views
Last Modified: 2012-05-07
SQL 2005
To simplify what I want to do...
I have table1 and table2.  I want to select the records in table1 that are not in table2.  I know I can do this by the following:
select t1.name, t1.address from t1 where t1.id not in (select t2.id from t2)

But is there a faster way?  The only field in t2 is "id".
0
Comment
Question by:ClowWater
2 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 24795786
select t1.name, t1.address from t1
where NOT EXISTS (SELECT  1 FROM  t2 where t2.id = t1.id )

and sometimes LEFT JOINS are faster, but it depends
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 150 total points
ID: 24795795
You can do it this if you are looking for a different way of doing this:
Select t.*
from  Table1 t LEFT OUTER JOIN Table2 c
           ON t.ID = c.ID
Where c.ID IS NULL
P.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now