Join and retrieve records - 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)

Join and retrieve records

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2012-02-08 : 10:16:56

Hi i have two table with same variables
table1
CREATE TABLE [dbo].[region_lookup1](
[region_id] [int] NOT NULL,
[description] [nvarchar](100) NOT NULL
) ON [PRIMARY]

insert into region_lookup1 select 1, 'region1'
insert into region_lookup1 select 1, 'region2'
insert into region_lookup1 select 1, 'region3'
insert into region_lookup1 select 1, 'region4'
insert into region_lookup1 select 2, 'region1'
insert into region_lookup1 select 2, 'region2'

table2
CREATE TABLE [dbo].[region_lookup2](
[region_id] [int] NOT NULL,
[description] [nvarchar](100) NOT NULL
) ON [PRIMARY]

insert into region_lookup2 select 1, 'region1'
insert into region_lookup2 select 1, 'region2'
insert into region_lookup2 select 1, 'region3'
insert into region_lookup2 select 2, 'region1'

i am trying to join the two tables and retrieve records from table 1 not in table 2 for the same region_id

so i want records
1, region4
2, region2

Any help

Thanks

jayram
Starting Member

47 Posts

Posted - 2012-02-08 : 12:33:58

never mind i got it...

select * from region_lookup1 A where not exists (select * from REgion_lookup2 B where A.region_id = B.region_id and A.description
= b.description)

netraju
Starting Member

4 Posts

Posted - 2012-02-09 : 01:58:41

just a small performance observation. Inside the subquery, make 1 instead of *, this will improve speed on a large data

select
*
from
region_lookup1 A
where
not exists (
select 1 from REgion_lookup2 B where A.region_id = B.region_id and A.description
= b.description
)

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-09 : 11:49:47

quote:
Originally posted by netraju

just a small performance observation. Inside the subquery, make 1 instead of *, this will improve speed on a large data

select
*
from
region_lookup1 A
where
not exists (
select 1 from REgion_lookup2 B where A.region_id = B.region_id and A.description
= b.description
)

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

That is a SQL Server Myth. Ther is no difference between SELECT * and SELECT 1 when used in an EXISTS clasue.

netraju
Starting Member

4 Posts

Posted - 2012-02-09 : 12:22:00

hi lamprey,
i dont think it is a mere myth. check out this link for more. www.sqlservercentral.co/Forums/Topic453737-338-1.aspx

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-09 : 12:44:51

I think the key Quote from Conor (the person who created the blog) is: "However, at runtime the two forms of the query will be identical and will have identical runtimes."

Granted you could make the argument that any meta data lookup (SELECT *) is a performance impact. But, for a single query it isn't (or shouldn't be).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources