• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

Join Explanation

Can someone please explain what exactly the following joins do exactly and the syntax to use them?  I'm very familiar with nested joins using

WHERE tbl.property in (SELECT ..

But I've been taking criticism from guys at work about using joins as they are supposed to be much simpler and more efficient.

inner join
outer join
left and right of the above.. and if you know of any more frequently used joins if you can throw in the syntax and result I would greatly appreciate it.. (points will be awarded to the explination of the joins, if you can only explain part of the above question points will be split accordingly with how many joins you explained)

Thank you!
~Aqua
0
Andrew Beers
Asked:
Andrew Beers
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi aqua9880,

Inner join : will give you the common items from both the tables ( Similar to Set operation intersection )
Left outer join : Common from both + the remaining on the left table
Right outer join : similar to the above

Aneesh R!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Syntax
--------

Select A.*
FROM TableA A
INNER JOIN TableB B
ON A.SomeKey = B.SomeKey


Select A.*
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.SomeKey = B.SomeKey

0
 
Andrew BeersTechnology LeadAuthor Commented:
Somebody said something about a cross join being used in isolated issues.. What is this?  And can left and right be applied to inner joins?

Thank you!

~Aqua~
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Atlanta_MikeCommented:
A cross join will return every row from table a and every row from table B

Example:

I want to add all of the user roles to all of the users....

insert into UserRoles (RoleID, UserID)
SELECT r.roleid, u.userid
FROM users u
CROSS JOIN roles r
WHERE r.type = 'u'

or something like that.

Of course there are toher factors you have to look at such as making sure your values are unique...

insert into UserRoles (RoleID, UserID)
SELECT r.roleid, u.userid
FROM users u
CROSS JOIN roles r
WHERE r.type = 'u'
and not exists(select * from UserRoles WHERE UserID = u.UserID and RoleID = r.RoleID)

etc..
0
 
Andrew BeersTechnology LeadAuthor Commented:
Thank you!  :-)

~Aqua
0
 
LowfatspreadCommented:
you should avoid using IN with subqueries  (especially if youyr going to get > 5/6 elements returned..)

and IN  is an implied INNER JOIN  to the rest of your data in the from clause...


so it is better to write it as

Select A.*
  from yourtable as A
Inner Join othertable as B
   on A.property= b.property

note you may still (have to ) wirte it as


Select A.*
  from yourtable as A
Inner Join (select property from othertable
                    where ...  
                   or use a distinct or group by to ensure on a distinct result set is returned
                 ) as B
   on A.property= b.property

 
the other PROPER way of specifying an IN
is to write it as an EXISTS test

Select A.*
  from yourtable as A
Where Exists  (select property from othertable as B
                    where a.property=b.property  
                 )

an (LEFT / RIGHT) outer join would probably be used in a NOT IN case
but NOTE EXISTS would be better...

you can also have a full outer join
which produces a rows for every combination of the two "tables" it relates

hth

 
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now