select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select statement

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-29 : 06:52:24

Dear All,

I need help regarding this

I have a 2 tables A and B both schema is same.
I have some few rows in both the tables.

I need to select the rows from A only which are not exist in table B

Thanks
GAngadhar

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-29 : 07:02:57

SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B)

-------------------------
R...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 07:04:10

So many posts for exactly this...... try searching

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 07:07:37

And rajdaksha is 100% wrong. Consider



DECLARE @tablea TABLE (
	[ID] INT IDENTITY(1,1)
	, [name] VARCHAR(255)
	)
DECLARE @tableb TABLE (
	[ID] INT IDENTITY(1,1)
	, [name] VARCHAR(255)
	)

INSERT @tableA ([name]) 
      SELECT 'Fred Flintstone'
UNION SELECT 'Wilma Flintstone'
UNION SELECT 'Daffy Duck'

INSERT @tableB ([name])
      SELECT 'Fred Flintstone'
UNION SELECT 'Wilma Flintstone'

SELECT * FROM @tableA A WHERE NOT EXISTS (SELECT * FROM @tableB B)

One of the proper ways is



DECLARE @tablea TABLE (
	[ID] INT IDENTITY(1,1)
	, [name] VARCHAR(255)
	)
DECLARE @tableb TABLE (
	[ID] INT IDENTITY(1,1)
	, [name] VARCHAR(255)
	)

INSERT @tableA ([name]) 
      SELECT 'Fred Flintstone'
UNION SELECT 'Wilma Flintstone'
UNION SELECT 'Daffy Duck'

INSERT @tableB ([name])
      SELECT 'Fred Flintstone'
UNION SELECT 'Wilma Flintstone'

SELECT *
FROM
	@tableA a
WHERE
	NOT EXISTS (
		SELECT 1
		FROM
			@tableB b
		WHERE
			b.[name] = a.[name]
		)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-29 : 07:24:33

Hi

another way..


SELECT	A.*
FROM	@TABLEA A
LEFT JOIN @TABLEB B
ON A.[NAME] = B.[NAME]
WHERE B.[NAME] IS NULL

Thanks Transact Charlie

-------------------------
R...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 07:41:14

that works. Peso Ran a thread a while back showing the various methods and their performances.

The NOT EXISTS method is definitely the most efficient one.

Here's the link
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2009-12-29 : 08:01:50

[code]
I dont know should my finding be called as a bug.
Try this

--Both of them do not work
1)
select id,name from @tablea
except
select id,name from @tableb
2)
select * from @tablea
except
select * from @tableb

--This one works
select name from @tablea
except
select name from @tableb

[/code]

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 09:00:08

Hi Idera.

No there is no bug. You are just assuming an order to the ID column which isn't right.
If you run this you'll see what's happening.


SELECT * FROM @tablea
SELECT * FROM @tableb

select id,name from @tablea
except
select id,name from @tableb

Because the data is inserted using a SELECT x UNION y method then the result set order is not guaranteed. in this case the INSERT into tablea gives different ID values from the insert into tableb for each name. I think this is because sql server will pick a default order on the name column so Daffy Duck is before Fred Flintstone....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2009-12-29 : 09:10:50

Thanks for this great insight.
If thats the case then Except clause is almost useless.Dont see any use of it.

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 09:23:28

that's one of the disadvantages of a surrogate primary key I guess.

for this case, the NOT EXISTS method is a lot faster anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources