Select records in table1 where table1.field1 doesn't exist in table2.field1

Posted on 2011-03-24
Medium Priority
Last Modified: 2012-05-11
Hey there,

I've got two tables, table1 and table2.  Both tables have identical fields except that table1 has an added date field named date (datetime).

I'd like to find all of the records in table1 where table1.field1 is not in (doesn't exist in) table2.field1.

Not sure if this is helpful but, field1 contains duplicate values in both tables.

I'd like to further filter my results by selecting only records from table1 where date='20110323'.

Thanks for any help you can give.  
Question by:ttist25
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
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35210654

select *
from table2
where not exists (select 1
                             from table2
                            where table2.field1 = table1.field1)
and date='20110323'
LVL 24

Expert Comment

ID: 35210666
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) where date ='20110323'
LVL 24

Accepted Solution

jimyX earned 1000 total points
ID: 35210680
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) and T1.date ='20110323'
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

LVL 40

Expert Comment

ID: 35210693
select * from table1
where field1 in
(select field1 from table1
select field1 from table2)

Assisted Solution

morgulo earned 1000 total points
ID: 35210723
Table2.field1 has to be not null. If Table2.field1 is nullable you should use:
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2 where field1 is not null) and T1.date ='20110323'

Author Closing Comment

ID: 35211159
Thanks everyone.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

801 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