Solved

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

Posted on 2003-11-11
7
168,558 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 125 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Space Delimited Sql File 4 70
Oracle 12c patching 1 37
Oracle and DateTime math 6 15
Fastest way to replace data in Oracle 5 9
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now