WHERE NOT EXISTS

Question

Post reply

WHERE NOT EXISTS

Johann Montfort

Hall of Fame

Points: 3123
More actions
November 24, 2008 at 4:23 pm

#203308

Dear All,
I have created a simple example with 2 tables, TableA and TableB.
CREATE TABLE [dbo].[TableA](
[fileId] [int] NOT NULL,
[langId] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TableB](
[fileId] [int] NOT NULL,
[langId] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO TableA (fileID, langId) VALUES (106, 7)
INSERT INTO TableA (fileID, langId) VALUES (106, 9)
INSERT INTO TableA (fileID, langId) VALUES (106, 11)
INSERT INTO TableA (fileID, langId) VALUES (106, 13)
INSERT INTO TableA (fileID, langId) VALUES (108, 7)
INSERT INTO TableA (fileID, langId) VALUES (108, 9)
INSERT INTO TableA (fileID, langId) VALUES (108, 11)
INSERT INTO TableA (fileID, langId) VALUES (108, 13)
INSERT INTO TableA (fileID, langId) VALUES (112, 7)
INSERT INTO TableA (fileID, langId) VALUES (112, 9)
INSERT INTO TableA (fileID, langId) VALUES (112, 11)
INSERT INTO TableA (fileID, langId) VALUES (112, 13)
INSERT INTO TableA (fileID, langId) VALUES (118, 7)
INSERT INTO TableA (fileID, langId) VALUES (118, 9)
INSERT INTO TableA (fileID, langId) VALUES (118, 11)
INSERT INTO TableA (fileID, langId) VALUES (118, 13)
INSERT INTO TableB (fileID, langId) VALUES (106, 7)
INSERT INTO TableB (fileID, langId) VALUES (106, 9)
INSERT INTO TableB (fileID, langId) VALUES (106, 11)
INSERT INTO TableB (fileID, langId) VALUES (106, 13)
INSERT INTO TableB (fileID, langId) VALUES (108, 7)
INSERT INTO TableB (fileID, langId) VALUES (108, 9)
Basically I want to retrieve the records in Table A which does not exist in Table B.
I tried the following
SELECT fileId, langid
FROM TableA ta
WHERE NOT EXISTS
(SELECT fileID, langID FROM TableB tb)
However this is not returning any results.
Can you tell me what I am doing wrong?
Thanks in advance for your help and time.
Johann

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Check out the following code:

CREATE TABLE [dbo].[TableA](

[fileID] [int] NOT NULL,

[langId] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[TableB](

[fileID] [int] NOT NULL,

[langId] [int] NOT NULL

) ON [PRIMARY]

INSERT INTO TableA (fileID, langId) VALUES (106, 7)

INSERT INTO TableA (fileID, langId) VALUES (106, 9)

INSERT INTO TableA (fileID, langId) VALUES (106, 11)

INSERT INTO TableA (fileID, langId) VALUES (106, 13)

INSERT INTO TableA (fileID, langId) VALUES (108, 7)

INSERT INTO TableA (fileID, langId) VALUES (108, 9)

INSERT INTO TableA (fileID, langId) VALUES (108, 11)

INSERT INTO TableA (fileID, langId) VALUES (108, 13)

INSERT INTO TableA (fileID, langId) VALUES (112, 7)

INSERT INTO TableA (fileID, langId) VALUES (112, 9)

INSERT INTO TableA (fileID, langId) VALUES (112, 11)

INSERT INTO TableA (fileID, langId) VALUES (112, 13)

INSERT INTO TableA (fileID, langId) VALUES (118, 7)

INSERT INTO TableA (fileID, langId) VALUES (118, 9)

INSERT INTO TableA (fileID, langId) VALUES (118, 11)

INSERT INTO TableA (fileID, langId) VALUES (118, 13)

INSERT INTO TableB (fileID, langId) VALUES (106, 7)

INSERT INTO TableB (fileID, langId) VALUES (106, 9)

INSERT INTO TableB (fileID, langId) VALUES (106, 11)

INSERT INTO TableB (fileID, langId) VALUES (106, 13)

INSERT INTO TableB (fileID, langId) VALUES (108, 7)

INSERT INTO TableB (fileID, langId) VALUES (108, 9)

select * from dbo.TableA

select * from dbo.TableB

select * from dbo.TableA

except

select * from dbo.TableB

drop table dbo.TableA

drop table dbo.TableB

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 2

Excellent

That worked perfectly.

Learned something new today 🙂

Thanks a lot Carpal!

hisorna SSC Journeyman Points: 86 More actions · Answer 3

You can also left join the second table with the first one (on all the fields) and then select the records that has null values in the second table fields. (See following code)

CREATE TABLE [dbo].[TableA](

[fileID] [int] NOT NULL,

[langId] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[TableB](

[fileID] [int] NOT NULL,

[langId] [int] NOT NULL

) ON [PRIMARY]

INSERT INTO TableA (fileID, langId) VALUES (106, 7)

INSERT INTO TableA (fileID, langId) VALUES (106, 9)

INSERT INTO TableA (fileID, langId) VALUES (106, 11)

INSERT INTO TableA (fileID, langId) VALUES (106, 13)

INSERT INTO TableA (fileID, langId) VALUES (108, 7)

INSERT INTO TableA (fileID, langId) VALUES (108, 9)

INSERT INTO TableA (fileID, langId) VALUES (108, 11)

INSERT INTO TableA (fileID, langId) VALUES (108, 13)

INSERT INTO TableA (fileID, langId) VALUES (112, 7)

INSERT INTO TableA (fileID, langId) VALUES (112, 9)

INSERT INTO TableA (fileID, langId) VALUES (112, 11)

INSERT INTO TableA (fileID, langId) VALUES (112, 13)

INSERT INTO TableA (fileID, langId) VALUES (118, 7)

INSERT INTO TableA (fileID, langId) VALUES (118, 9)

INSERT INTO TableA (fileID, langId) VALUES (118, 11)

INSERT INTO TableA (fileID, langId) VALUES (118, 13)

INSERT INTO TableB (fileID, langId) VALUES (106, 7)

INSERT INTO TableB (fileID, langId) VALUES (106, 9)

INSERT INTO TableB (fileID, langId) VALUES (106, 11)

INSERT INTO TableB (fileID, langId) VALUES (106, 13)

INSERT INTO TableB (fileID, langId) VALUES (108, 7)

INSERT INTO TableB (fileID, langId) VALUES (108, 9)

select * from dbo.TableA

select * from dbo.TableB

select TableA.* from dbo.TableA

left join dbo.TableB

on TableA.fileID = tableB.fileID

and TableA.langID = TableB.langID

where TableB.fileID is null

drop table dbo.TableA

drop table dbo.TableB

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Johann Montfort (11/24/2008)
I tried the following
SELECT fileId, langid
FROM TableA ta
WHERE NOT EXISTS
(SELECT fileID, langID FROM TableB tb)
However this is not returning any results. Can you tell me what I am doing wrong?

You already have 2 solutions, but this question remained unanswered... What you were doing wrong is, that your NOT EXISTS had no correlation to tableA. Uncorrelated condition does not help to select which rows to display, it works more as IF condition.

To put your query into words: "If tableB is empty, select all rows from tableA."

Thats not what you wanted. It would be possible to add WHERE condition to your NOT EXISTS statement, that would make it work - but the previously posted solutions are IMHO better.

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 5

Thanks for your replies guys

Yeah I should have added a WHERE clause.

I like Carpal's answer the most, since I have learned something new.

Thanks a lot guys!

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Grats on Carpal Lynn!

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]