inner and outer joins

gudii9
gudii9 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018
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/
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I didn't refresh before posting.  The diagram in the link Bill posted is the one I was looking for!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial