top of page

Oracle Joins

When you normalize data and create tables inside the database, the data is scattered into different tables. In order to create meaningful data, we must query data by joining multiple tables. 



This is where Oracle joins comes into the picture. Using Oracle join syntax, we join two or more tables to create reports.


A join will always happen only on key columns!

A join is a query that combines rows from two or more tables. For joining, you must have

  • Two or more tables

  • A join condition



Setup tables


For our join example, we will be using sample tables. Create below tables and insert rows before proceeding with Oracle join types.


Create first table Books

CREATE TABLE books (
  book_id   NUMBER(4) CONSTRAINT books_pk PRIMARY KEY,
  title VARCHAR2(30),
  category VARCHAR2(10)
);

Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (1,'Wolf of wallsteet','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (2,'Start with why','Inspire');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (3,'The one thing','Inspire');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (4,'Real Steel','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (5,'Safe house','Novel');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (6,'Leader eat last','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (7,'10X rule','Sales');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (8,'Sell or be sold','Sales');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (9,'Obama',NULL);

Notice the last record we are inserting. For test purpose, I have made the book category NULL.

Create second table Members

CREATE TABLE members (
  member_id   NUMBER(2) CONSTRAINT member_pk PRIMARY KEY,
  member_name VARCHAR2(14),
  book_id NUMBER(4) CONSTRAINT members_books_id_fk REFERENCES books(book_id)
);

Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (11,'John',3);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (22,'Max',5);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (33,'Sam',6);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (44,'Leo',7);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (55,'Kish',9);

These tables are for demonstration purpose and you create create it in any database schema.



Types of joins


There are many types of joins inside Oracle but most of it fall under below main categories

  • INNER JOIN

    • NATURAL JOIN

  • OUTER JOIN

    • LEFT OUTER JOIN

    • RIGHT OUTER JOIN

    • FULL OUTER JOIN

  • CROSS JOIN



Join syntax


There are two ways in join tables inside a join conditions

  • Using traditional SQL syntax

  • Using JOIN types inside your query

Many DBA/developers use a combination of both traditional and SQL JOIN method inside a query. You must be familiar with both types of syntax or else it will hard to understand many SQL statements.


There are multiple ways of joining tables!

In all the below examples, I will be joining both tables using traditional method and also SQL JOIN method for your reference.



INNER JOIN


It's a simple join between two or more tables which returns rows that satisfy a join condition

oracle joins - inner join

For our example purpose, we have below two tables

oracle joins - oracle join table
oracle-join-table-1 (1)
oracle joins - inner join table

We are trying to return the MEMBER_NAME and the CATEGORY of book borrowed. In this case we will join both tables using the BOOK_ID column


TRADITIONAL METHOD


In a traditional join method, we specify all join tables under FROM clause and join column under WHERE clause

SELECT m.member_name, b.category
FROM members m, books b
WHERE m.book_id=b.book_id;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish

Carefully watch the join condition. It says, return the rows where book_id column values are matching from members and books tables

If you observe the output, we have only the rows from both table where MEMBERS.BOOK_ID is sames as BOOKS.BOOK_ID. Note the CATEGORY column is null for member KISH.


SQL JOIN METHOD


In this method we use SQL JOIN syntax to join two tables. We use JOIN…ON clause to join the tables

SELECT m.member_name, b.category
FROM members m JOIN books b
ON m.book_id=b.book_id;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish

If you observe above tables, the key column name (BOOK_ID) is same in both MEMBERS table and BOOKS table. When the key column name is same, you can use JOIN….USING clause also

SELECT m.member_name, b.category
FROM members m JOIN books b
USING (book_id);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish


NATURAL JOIN


In a perfect relational database, Oracle is aware of how tables are joined and what are the key columns. When you joining two tables and do not want to specify the joining column, use NATURAL….JOIN. Oracle will join tables automatically for you on the key columns

SELECT m.member_name, b.category
FROM members m NATURAL JOIN books b;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish


OUTER JOIN


It returns rows that satisfy the join condition and also some/all  the rows that do not satisfy the condition.


There are three types of OUTER JOIN

  • LEFT OUTER JOIN

  • RIGHT OUTER JOIN

  • FULL OUTER JOIN



LEFT OUTER JOIN


It returns all the rows that satisfy the join condition and also returns all the rows from the table on the left side of the JOIN keyword. Note, if no matching rows are found, it will return a NULL value

oracle joins - left outer join

SQL JOIN METHOD


In SQL join method, we simply use LEFT OUTER JOIN keyword to join the tables

SELECT m.member_name, b.category
FROM members m LEFT OUTER JOIN books b
ON m.book_id=b.book_id;

OR

SELECT m.member_name, b.category
FROM members m LEFT OUTER JOIN books b
USING (book_id);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish

Observe that we got all the rows as output from the MEMBERS table because its on the left side of the JOIN keyword. We also have output from the BOOKS table (which is on the right side of the JOIN keyword).


In the above output all records from MEMBERS table are present in the output as all satisfy the join condition. As we do not have any record in the MEMBERS table that do not satisfy the JOIN condition, hence no null values.


TRADITIONAL METHOD


In traditional method of joining, we use “(+)” on the side of table B. Many people get confused abut using “(+)” on the side of table A as it is a LEFT OUTER JOIN

SELECT m.member_name, b.category
FROM members m, books b
WHERE m.book_id = b.book_id (+);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish


RIGHT OUTER JOIN


It returns all the rows that satisfy the join condition and also returns all the rows from the table on the right side of the JOIN keyword. Note, if no matching rows are found, it will return a NULL value

oracle joins - right outer join

SQL JOIN METHOD


In SQL join method, we simply use RIGHT OUTER JOIN keyword to join the tables

SELECT m.member_name, b.category
FROM members m RIGHT OUTER JOIN books b
ON m.book_id=b.book_id;

OR

SELECT m.member_name, b.category
FROM members m RIGHT OUTER JOIN books b
USING (book_id);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish
               Business
               Inspire
               Sales
               Business

Observe the output above, we have all the records from table BOOKS (which is on the right side of the JOIN keyword) and only the matching records from the MEMBERS table (which is on the left side of the JOIN keyword).


For all the non matching values from MEMBERS table, NULL value is returned.


TRADITIONAL METHOD


In traditional method of joining, we use “(+)” on the side of table A. Many people get confused abut using “(+)” on the side of table B as it is a RIGHT OUTER JOIN

SELECT m.member_name, b.category
FROM members m, books b
WHERE m.book_id (+) = b.book_id;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish
               Business
               Inspire
               Sales
               Business


FULL OUTER JOIN


This type of join condition returns all the records that satisfy the join condition and also all the records that do not satisfy the join condition

oracle joins - full outer join

SQL JOIN METHOD


We use FULL OUTER JOIN keyword to join two or more tables

SELECT m.member_name, b.category
FROM members m FULL OUTER JOIN books b
ON m.book_id=b.book_id;

OR

SELECT m.member_name, b.category
FROM members m FULL OUTER JOIN books b
USING (book_id);

MEMBER_NAME    CATEGORY
-------------- ----------
               Business
               Inspire
John           Inspire
               Business
Max            Novel
Sam            Business
Leo            Sales
               Sales
Kish

Notice that we have all the records from both MEMBERS table and BOOKS table. And where ever the values are not matching, NULL is returned.



CROSS JOIN


It joins and outputs all the rows from all the tables in the join. If source table has 4 rows and target table has 3 rows, total output rows will be (4X3=12).

This is rarely used in real time

oracle joins - cross join
SELECT m.member_name, b.category
FROM members m CROSS JOIN books b;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Business
John           Inspire
John           Inspire
John           Business
John           Novel
John           Business
John           Sales
John           Sales
John
Max            Business
Max            Inspire
Max            Inspire
Max            Business
Max            Novel
Max            Business
Max            Sales
Max            Sales
Max
Sam            Business
Sam            Inspire
Sam            Inspire
Sam            Business
Sam            Novel
Sam            Business
Sam            Sales
Sam            Sales
Sam
Leo            Business
Leo            Inspire
Leo            Inspire
Leo            Business
Leo            Novel
Leo            Business
Leo            Sales
Leo            Sales
Leo
Kish           Business
Kish           Inspire
Kish           Inspire
Kish           Business
Kish           Novel
Kish           Business
Kish           Sales
Kish           Sales
Kish

45 rows selected.

If you notice we have 9 records in BOOKS table and 5 records in MEMBERS table. The total output records are 45

-----Traditional method-----
SELECT m.member_name, b.category
FROM members m, books b;

MEMBER_NAME    CATEGORY
-------------- ----------
John           Business
John           Inspire
John           Inspire
John           Business
John           Novel
John           Business
John           Sales
John           Sales
John
Max            Business
Max            Inspire
Max            Inspire
Max            Business
Max            Novel
Max            Business
Max            Sales
Max            Sales
Max
Sam            Business
Sam            Inspire
Sam            Inspire
Sam            Business
Sam            Novel
Sam            Business
Sam            Sales
Sam            Sales
Sam
Leo            Business
Leo            Inspire
Leo            Inspire
Leo            Business
Leo            Novel
Leo            Business
Leo            Sales
Leo            Sales
Leo
Kish           Business
Kish           Inspire
Kish           Inspire
Kish           Business
Kish           Novel
Kish           Business
Kish           Sales
Kish           Sales
Kish

45 rows selected.




Related Posts

Heading 2

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

bottom of page