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

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!
illucidAsked:
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.

catchmeifuwantCommented:
0
baonguyen1Commented:
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

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
aitheneCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lucasjellemaCommented:
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
helpneedCommented:
hi

i think lucasjellema  said is ok

regards

0
guru_on_demandCommented:
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
irfanlmkrSr. Software DeveloperCommented:
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
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.