are this correct - 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

are this correct

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2010-07-13 : 04:35:21

1) Select * from tbl1 where accid in (select accid from tbl2 where accountid=92)

2) Select * from tbl1 O join tbl2 A on A.accid=O.accid where O. accid=92

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 04:41:09

these 2 statement is not really identical
- in (1) your condition is tbl2.accoutnid = 92 but in (2) it is tbl1.accid = 92

KH
[spoiler]Time is always against us[/spoiler]

Leo_Don
Starting Member

42 Posts

Posted - 2010-07-13 : 04:46:09

sorry the first condition also has accid=92

1) Select * from tbl1 where accid in (select accid from tbl2 where accid=92)

2) Select * from tbl1 O join tbl2 A on A.accid=O.accid where O. accid=92

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 04:54:16

So you want to retrieve data for accid 92 but only if this accid has records in both tables?

No, you're never too old to Yak'n'Roll if you're too young to die.

Leo_Don
Starting Member

42 Posts

Posted - 2010-07-13 : 05:03:50

Yes accid has records in both the table.
is it possible that i can use any of the statements.

connexion
Starting Member

2 Posts

Posted - 2010-07-13 : 05:05:49

Hi Leo_Don,
Both the queries are not identical. According to me in the first query you get the results only from tbl1. Whereas in the second query you get all the columns from tbl1 as well as tbl2.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 05:08:31

quote:
Originally posted by Leo_Don

Yes accid has records in both the table.
is it possible that i can use any of the statements.

Yes you can but you should look then which columns you want to have in your resultset.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 08:57:28

quote:
Originally posted by Leo_Don

Yes accid has records in both the table.
is it possible that i can use any of the statements.

Both query might return different number of records. If in tbl2 there is more than one rows for the same accid

see this query


declare @tbl1 table
(
	accid	int,
	col1	int
)

declare @tbl2 table
(
	accid	int,
	col2	int
)

insert into @tbl1 select 92, 10
insert into @tbl2 select 92, 11
insert into @tbl2 select 92, 12

Select * from @tbl1 where accid in (select accid from @tbl2 where accid=92)

Select * from @tbl1 O join @tbl2 A on A.accid=O.accid where O.accid=92

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 09:00:09

if you are only interested in the record from tbl1, you can also use exists()


select 	* 
from 	@tbl1 t1 
where 	accid = 92 
and 	exists 
	(
		select 	accid 
		from 	@tbl2 t2 
		where 	t2.accid = t1.accid
	)

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 09:17:21

I apprecaite that you have corrected your original from:

Select * from tbl1 where accid in (select accid from tbl2 where accountid=92)

to

Select * from tbl1 where accid in (select accid from tbl2 where accid=92)

but note that in the first example if

(select accid from tbl2 where accountid=92)

returned values for [accid] which included "92" (i.e. which would match the corresponding row in tbl1.accid) and if it ALSO returned one, or more, values of NULL for [accid] THEN the "IN" test would FAIL. This particular case hides a hard-to-find bug!!

Thus I prefer EXISTS or JOIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources