inner and outer joins

DIFFERENCES BETWEEN

1. FUL OUTER JOIN
2. LEFT OUTER JOUN
3. RIGHT OUTER JOIN
4. LEFT INNER JOIN
5. RIGHT INNER JOIN
6. FULL INNER JOIN

WHEN TO USE WHICH ONE

please advise
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
I often refer people to this page when they are trying to get an understanding of the various joins, hope it helps you.

As far as when to use, you just have to think about the data / tables you are joining and think about what rows you want in the result set.  That drives the type of JOIN to use.

Visual Representation of SQL Joins - CodeProject


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Best thing ever is to Google:  Join venn diagram

There are many pictures that visualize the result sets.  Pick your favorite.

A quick example:
http://dziuk.info/49-sql-venn-diagram/sql-venn-diagram-equipped-screenshoot-accordingly-hmkkt/
slightwv (䄆 Netminder) Commented:
I didn't refresh before posting.  The diagram in the link Bill posted is the one I was looking for!
PortletPaulEE Topic AdvisorCommented:
An INNER JOIN does not need LEFT or RIGHT or FULL qualifiers

so your list really is:

1. FULL OUTER JOIN
2. LEFT OUTER JOIN
3. RIGHT OUTER JOIN
4. INNER JOIN

The INNER JOIN is the most fundamental of these, understand it first. Then the other joins should make sense as they allow for conditions that an inner join does not permit.

The diagram suggested by BIll is probably the most referred to for this topic, it is a good starting place. However do note that the word "excluding" is used to aid comprehension but it does not get used in the actual SQL syntax of joins.

e.g. the diagram refers to a "Left Excluding JOIN"
This is actually a combination of a LEFT JOIN and a WHERE clause condition to exclude some rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.