AND or INTERSECT? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

AND or INTERSECT?

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-07-16 : 17:53:12

I don't know if this is due to poor database design or poor querying, but:

select m.id 
from table m 
where m.course in 
	('05','06','07','08','09','10')
and m.type = 'AuditOnly'
and m.gender = 'M'

returns zero, but:


select m.id
from table m
where m.course in 
	('05','06','07','08','09','10')

intersect

select m.id
from table m
where m.type = 'AuditOnly'

intersect 

select m.id
from table m
where m.gender = 'M'

returns a few hundred. I have a feeling this has something to do with scalar (intersect) values vs. nonscalar (and) rows, but otherwise I am stumped.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-17 : 02:31:34

intersect returns any distinct value is returned by your three queries on left or right side of the operand.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-17 : 02:40:47

simple case will help you understand the power of intersect


create table tb_inter
(id int
,name varchar(10)
,id2 int)

insert into tb_inter
select 10, 'AA', 12 union all
select 10, 'AA', 12 union all
select 10, 'AA1', 12 union all
select 11, 'AA1', 12 union all
select 11, 'AA2', 13 union all
select 11, 'AA2', 13



select * from tb_inter
where id = 10 
  and id2 = 12


select * from tb_inter
where id  = 10
intersect
select * from tb_inter
where id2 = 12


select id, id2 from tb_inter
where id  = 10
intersect
select id, id2 from tb_inter
where id2 = 12

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-17 : 03:47:08

[code]course type gender
04 x m
05 AuditOnly f[/code]None of these records will satisfy first query.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources