Q
Problem solve Get help with specific problems with your technologies, process and projects.

Outer join types

Can you explain the following: a) one way outer join and b) two way outer join? Please clarify the difference between these two.

I'm going to assume that you do not know what an outer join is. If you already know what an outer join is, then pardon the following explanation...

Let's start by looking at two sample tables.

ORA9I SQL> select * from users;

USERNAME      USER_ID CREATED
---------- ---------- ---------
OUTLN              11 27-JUN-01
DBSNMP             17 27-JUN-01
PEASLAND           18 24-AUG-01

ORA9I SQL> select username,profile from db_users;

USERNAME   PROFILE
---------- ----------
SYS        DEFAULT
SYSTEM     DEFAULT
DBSNMP     DEFAULT
PEASLAND   DEFAULT

I've just created these test tables for this example. Notice the 'users' table has three users and the db_users table has four users. The DBSNMP and PEASLAND users are common to both. The OUTLN user only exists in the USERS table and the SYS and SYSTEM users only exist in the DB_USERS table. If I join these two tables on the USERNAME column, then only those rows which are in *both* tables will be returned. For example:

ORA9I SQL> select u.username,u.user_id,d.profile
  2  from users u, db_users d
  3  where u.username=d.username;

USERNAME      USER_ID PROFILE
---------- ---------- ----------
DBSNMP             17 DEFAULT
PEASLAND           18 DEFAULT

What if I wanted to include a user from the USERS table even if they don't exist in the DB_USERS table? I then have to use the OUTER JOIN operator, denoted by the '(+)' symbol. See the following query:

ORA9I SQL> select u.username,u.user_id,d.profile
  2  from users u, db_users d
  3  where u.username=d.username (+);

USERNAME      USER_ID PROFILE
---------- ---------- ----------
DBSNMP             17 DEFAULT
OUTLN              11
PEASLAND           18 DEFAULT

The OUTLN user is returned this time even though it had no match in the DB_USERS table! This is an outer join.

Now to answer your question. A one-way outer join is an outer join between two tables as I've already shown you. A two-way outer join includes yet one more table. The following query is an example of a two-way outer join:

SQL> select o.owner,u.user_id,d.profile,o.object_type,count(*) as num_types
  2  from objects o, users u, db_users d
  3  where o.owner=u.username(+)
  4    and o.owner=d.username(+)
  5    and o.object_type in ('TABLE','INDEX')
  6* group by o.owner,u.user_id,d.profile,o.object_type;

OWNER         USER_ID PROFILE    OBJECT_TYPE         NUM_TYPES
---------- ---------- ---------- ------------------ ----------
OUTLN              11            INDEX                       3
OUTLN              11            TABLE                       3
PEASLAND           18 DEFAULT    TABLE                       2
SYS                   DEFAULT    INDEX                     249
SYS                   DEFAULT    TABLE                     264
SYSTEM                DEFAULT    INDEX                     144
SYSTEM                DEFAULT    TABLE                     108

7 rows selected.

See how two tables (USERS and DB_USERS) had the outer join operator applied?

For More Information

  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in November 2001

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close