How to select data using join operation ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to select data using join operation ?

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-04 : 01:23:44

hi all,

hi pls see my samples tables and required output below..

dept table
deptid deptname
100 maths
101 physics
102 pscycology

emp table
empid empname deptid
1000 john 100
1001 samuel 101
1002 albert 100
1003 joseph 100
1004 ann 101

required output:
102 pscycology

I need the query using joining tables and not using IN/NOT IN keywords.

ok tanx in advance

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-04 : 01:56:57

is this homework? after 142 posts here?

use a left join:


select d.deptid, deptname
from dept d 
left join emp e on d.deptid = e.deptid 
where e.deptid is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 02:05:11

Also


select d.deptid, deptname
from dept d 
WHERE NOT EXISTS(SELECT 1 FROM emp  
where deptid = d.deptid)

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-04 : 02:05:34

hi nathan,
yes its a homework..

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-04 : 02:08:11

hi visakh,
second table emp may have records of 1 lakh or above..
so wats ur opinion about performance of query?
shall i use ur query or nathan's query ?

ok tanx..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 02:16:07

quote:
Originally posted by soorajtnpki

hi visakh,
second table emp may have records of 1 lakh or above..
so wats ur opinion about performance of query?
shall i use ur query or nathan's query ?

ok tanx..

test it and see how much time each takes.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-04 : 02:20:06

and examine exec plan of each to learn why one takes longer than the other.

learning is fun.

Nathan Skerl

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-04 : 02:38:32

yup
but not everybody knows that fact........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources