Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

INNER JOIN / OUTER JOIN. what are these and why use them?

Posted on 2003-11-11
7
Medium Priority
?
168,572 Views
Last Modified: 2011-09-20
1).
Im looking at an example here:

SELECT P.PublisherCode, P.PublisherName, B.BookCode, B.BookTitle
FROM Publisher P LEFT INNER JOIN Book B
ON P.PublisherCode = B.PublisherCode

how this is different to:

SELECT P.PublisherCode, P.PublisherName, B.BookCode, B.BookTitle
FROM Publisher P, Book B
WHERE P.PublisherCode = B.PublisherCode


2).
Can someone explain to me the difference between INNER/OUTER and LEFT/RIGHT JOIN's

Thanks!
0
Comment
Question by:illucid
7 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9720768
0
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 375 total points
ID: 9721019
1. As we have Left , Right and Full Outer Join I think the fisrt question should read:

SELECT P.PublisherCode, P.PublisherName, B.BookCode, B.BookTitle
FROM Publisher P LEFT OUTER JOIN Book B
ON P.PublisherCode = B.PublisherCode.

It shows PublisherCode, PublisherName values of the Publisher table that match PublisherCode in Book table and for unmatched rows from the Publisher table it will display Null.

The second is just a join condition

2. Refer to catchmeifuwant answer
0
 

Expert Comment

by:aithene
ID: 10460217
INNER/OUTER:

   emp                                  dept

id_emp, name_emp, id_dept               id_dept, name_dept

1           Jones           10                        10        IT
2          Holms            10                       20        Marketing
3          Jordan           20                       30        Accountance
4         Rogers           -- (yet)

SELECT name_emp, name_dept
FROM emp e FULL OUTER JOIN dept d
WHERE e.id_dept = d.id_dept

name_emp              name_dept

Jones                       IT
Holms                       IT
Jordan                     Marketing
Rogers                    --
--                           Accountance


OUTER - you have not matched rows in one of the tables, but JOIN works
INNER - rows have their matched rows in the joining table
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

by:lucasjellema
ID: 12782529
I use the following summary for the outer joins:

EMP LEFT OUTER JOIN DEPT: take all records from the LEFT side table (EMP) and try to join them with records in DEPT. Any records in EMP not yet joined are also displayed with null entries for any DEPT columns
EMP RIGHT OUTER JOIN DEPT: take all records from the RIGHT side table (DEPT) and try to join them with records in EMP. Any records in DEPT not yet joined are also displayed with null entries for any EMP columns
EMP FULL OUTER JOIN DEPT: take all records from the left side table (EMP) and try to join them with records in DEPT. Any records in EMP not yet joined are also displayed with null entries for any DEPT columns. Any records in DEPT not yet joined are also displayed with null entries for any EMP columns.



0
 
LVL 5

Expert Comment

by:helpneed
ID: 13642354
hi

i think lucasjellema  said is ok

regards

0
 
LVL 3

Expert Comment

by:guru_on_demand
ID: 14354647
lucasjellema
I have been searching for such a succinct yet complete answer and you have managed to do it so well. Thank you so much
g_on_d
0
 

Expert Comment

by:irfanlmkr
ID: 20761633
An inner join's ON condition retrieves only those records that satisfy the join condition. An outer join does the same thing but with the addition of returning records for one table in which there were no matching records in the other table.
There are three types of outer joins: left outer, right outer, and full outer. All outer joins retrieve records from both tables, just as an inner join does. However, an outer join retrieves all of the records from one of the tables. A column in the result is NULL if the corresponding input table did not contain a matching record.

The left outer join retrieves records from both tables, retrieving all the records from the left table and any records from the right table where the condition values match. If there are no matching values in from the right table, the join still retrieves all the records from the left table. Any columns from the right table that are unmatchedare left NULL.

The right outer join is similar to the left outer join in that it retrieves all the records from one side of the relationship, but this time it's the right table. Only records where the condition values match are retrieved from the left.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question