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

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.



helpneedCommented:
hi

i think lucasjellema  said is ok

regards

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
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.
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.