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

# 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
2 Solutions

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

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

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

Commented:
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

Thank you!  :-)

~Aqua
0

Commented:
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

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