sql server programming: CREATE TABLE #firsttable
(
EmpID INT,
Name VARCHAR(20),
Address VARCHAR(20)
)

INSERT INTO #firsttable VALUES (1, 'name1', 'address1')
INSERT INTO #firsttable VALUES (2, 'name2', 'address2')
INSERT INTO #firsttable VALUES (3, 'name3', 'address3')
INSERT INTO #firsttable VALUES (4, 'name4', 'address4')
INSERT INTO #firsttable VALUES (5, 'name5', 'address5')
INSERT INTO #firsttable VALUES (6, 'name6', 'address6')
INSERT INTO #firsttable VALUES (7, 'name7', 'address7')
INSERT INTO #firsttable VALUES (8, 'name8', 'address8')

CREATE TABLE #secondtable
(
EmpID INT,
Name VARCHAR(20),
Address VARCHAR(20)
)

INSERT INTO #secondtable VALUES (1, 'name1', 'address1')
INSERT INTO #secondtable VALUES (2, 'name2', 'address2')
INSERT INTO #secondtable VALUES (3, 'name3', 'address3')
INSERT INTO #secondtable VALUES (4, 'name4', 'address4')
INSERT INTO #secondtable VALUES (5, 'name5', 'address5')
INSERT INTO #secondtable VALUES (6, 'name6', 'address6')

SELECT * FROM #firsttable
SELECT * FROM #secondtable

SELECT *
FROM #firstTable A
WHERE NOT EXISTS
(SELECT *
FROM #secondtable
WHERE empid = A.empid
AND name = A.name
AND address = A.address)

---
Rohtash Kapoor.
http://www.sqlmantra.com

[quoted text, click to view]

You can use NOT EXISTS, NOT IN or LEFT JOIN. Personally, I prefer NOT
EXISTS:

SELECT *
From Table1 AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Table2 AS t2
WHERE t2.ID = t1.ID
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Looking to find all records in one table that are not in another table. What
is the most efficient way?

Dave,

create table Dave1 (c1 int NOT NULL)
create table Dave2 (c2 int NOT NULL)
go

insert into Dave1 values (1)
insert into Dave1 values (2)
insert into Dave1 values (3)
insert into Dave1 values (4)
insert into Dave1 values (5)

insert into Dave2 values (2)
insert into Dave2 values (4)

select d1.c1
from Dave1 as d1
where not exists (select *
from Dave2 as d2
where d2.c2 = d1.c1)

select d1.c1
from Dave1 as d1
left outer join Dave2 as d2
on (d1.c1 = d2.c2)
where d2.c2 is null

Chief Tenaya


[quoted text, click to view]

If I want to limit the choices to a smaller set, say a date range for
instance, I must put the same range in both the main and subquery....right?

[quoted text, click to view]

[quoted text, click to view]
subquery....right?

If you want to filter on outer (Table1) criteria, you can do something like
the example below:

SELECT *
FROM Table1 AS t1
WHERE
t1.SomeDate BETWEEN '20040401' AND '20040420' AND
NOT EXISTS
(
SELECT *
FROM Table2 AS t2
WHERE t2.ID = t1.ID
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]