Excluding Records of one Table from the Other Table

Posted: May 16, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Problem Statement: There are two tables and we need to get all the records from Table1 that are not in Table2.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Excluding Records from Table1 that Exist in Table2

Solution: It can be done in two ways, using Left Join OR using Exists condition.

Using Left Join:

SELECT T1.EMP_ID 
        FROM 
            TABLE1 as T1 LEFT JOIN TABLE2 as T2 
                    ON T1.Emp_ID = T2.EMP_ID 
 WHERE T2.Name is Null

Using EXISTS Condition:

SELECT T1.EMP_ID 
           FROM Table1 as T1 
 WHERE NOT  EXISTS (SELECT Emp_id FROM Table2 as T2 Where T1.Emp_id = T2.Emp_id)

Result:

EMP_ID
9
4
Advertisements
Comments

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s