joins information - 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

joins information

Author

Topic

sqldba75
Starting Member

1 Post

Posted - 2007-11-02 : 10:34:55

Hi,

I am new to sql programming.please let me know what is difference between the inner join,outer join.which one is the fast?why?

Thanks,
dba75

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 11:06:37

sql server books online, would be a great place to learn about the different joins. here is a link in case you did not install it locally, YET
http://msdn2.microsoft.com/en-us/library/ms191517.aspx
without specifics, the inner join returns the fewest rows and would therefore be the fastest

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-02 : 11:17:21

2nd opinion - same as the first

One of the best things you can do to get started with sql server is start getting familiar with their "help". It can be installed with Sql Server and it is also available online: http://msdn2.microsoft.com/en-us/library/ms130214.aspx

between the reference and tutorials you can learn at all levels. FROM topic is one place that defines the join types.
http://msdn2.microsoft.com/en-us/library/ms177634.aspx

as to your second question:
Speed would not be the factor in determining which join type to use as they accomplish different things.

Be One with the Optimizer
TG

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-03 : 07:29:16

joins areof two types as u asked inner and outer
inner join are used to fetch data common to both tables
like

select a.col1,b.col2 from table first a inner join table second b where a.<col_common to both >=b.<col common to both tables>
only those records will be fetch those are having common values for the col. taken for join

first
col1 col2 col3
1 q w
2 w e
3 w e
4 w e
second

col1 col2 col3
1 a s
2 b s

result for the inner join is
col1 col2
1 a
2 b

in outer join its not same as in inner join
here data will be taken completely from one of two tables and
common data from the second table
like
select a.col1,b.col2 from table first a left outer join table second b where a.<col_common to both >=b.<col common to both tables>

this fetch all entries corresponding to col1 from table first and only matching entries from table second
like
col1 col2
1 q
2 w
3 w
4 w
1 a
2 b

similarly right outer join will fetch all data from table second and matching data from table first

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-03 : 08:39:14

Inner joins only brings in records from both joined tables where all records match (based on the joining columns)

Outer joins bring in all records from the "outer" table and only records from the other table where there are matching records on the joining columns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-03 : 08:50:32

quote:
Originally posted by arorarahul.0688

joins areof two types as u asked inner and outer
inner join are used to fetch data common to both tables
like

select a.col1,b.col2 from table first a inner join table second b ]~~where~~on a.<col_common to both >=b.<col common to both tables>
only those records will be fetch those are having common values for the col. taken for join

first
col1 col2 col3
1 q w
2 w e
3 w e
4 w e
second

col1 col2 col3
1 a s
2 b s

result for the inner join is
col1 col2
1 a
2 b

in outer join its not same as in inner join
here data will be taken completely from one of two tables and
common data from the second table
like
select a.col1,b.col2 from table first a left outer join table second b ~~where~~on a.<col_common to both >=b.<col common to both tables>

this fetch all entries corresponding to col1 from table first and only matching entries from table second
like
col1 col2
1 q
2 w
3 w
4 w
1 a
2 b

similarly right outer join will fetch all data from table second and matching data from table first

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-06 : 08:16:45

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

joins areof two types as u asked inner and outer
inner join are used to fetch data common to both tables
like

select a.col1,b.col2 from table first a inner join table second b ]~~where~~on a.<col_common to both >=b.<col common to both tables>
only those records will be fetch those are having common values for the col. taken for join

first
col1 col2 col3
1 q w
2 w e
3 w e
4 w e
second

col1 col2 col3
1 a s
2 b s

result for the inner join is
col1 col2
1 a
2 b

in outer join its not same as in inner join
here data will be taken completely from one of two tables and
common data from the second table
like
select a.col1,b.col2 from table first a left outer join table second b ~~where~~on a.<col_common to both >=b.<col common to both tables>

this fetch all entries corresponding to col1 from table first and only matching entries from table second
like
col1 col2
1 q
2 w
3 w
4 w
1 a
2 b

similarly right outer join will fetch all data from table second and matching data from table first

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Madhivanan

Failing to plan is Planning to fail

thanks for correction

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-06 : 08:32:43

How is it that people get DBA jobs in this industry and they have never heard of Google? How is that possible? (I am being sincere and serious, not sarcastic ....)

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 08:36:48

quote:
Originally posted by jsmith8858

How is it that people get DBA jobs in this industry and they have never heard of Google? How is that possible? (I am being sincere and serious, not sarcastic ....)

- Jeff
http://weblogs.sqlteam.com/JeffS

May be the OP a fresher?

I have seen the experienced DBAs also struggling to write queries

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources