Oracle SQL JOINs

Oracle SQL JOIN clause helps to combine rows or records from two or more tables on the basis of related column values across those tables.

So, that means there are certain columns in common between those tables. Those columns establish a relationship between those tables.

SQL JOINS are so important to understand. The reason is you will be working in some application which might be having 10 or 20 related tables. You might have to join 6 tables at a time to fetch some data.

I know what you are thinking. 6 tables !! 😮 How would I do that? Trust me, it is damn easy. You will be proficient in the basics of SQL JOINS by the end of this course. 😉

This is a fair long post containing 1700+ words. So, please bookmark it, so that you can go through it till you complete.

How does Oracle SQL JOIN happen between two database tables?

Let us say there are two tables present in the database. One is stream_t and the other one is student_t.

The common column in both the tables is stream_id. In this example, you see that the rows with student_id 9004, 9006 has stream_id 101. Now, the stream name for stream id 101 data is in the stream_t table.

We basically perform a JOIN between two tables on the stream_id column. I have added a filter condition to pull out only 101 stream id records to explain. The query looks like the following.

select student_id
     , student_name
     , std.stream_id
     , stream_name
  from student_t std
  join stream_t stm on std.stream_id = stm.stream_id
 where std.stream_id = 101;

Data Model Diagram Of stream_t And student_t Tables

• As you can see the student_t table has three columns. Those are student_id, student_name, stream_id. The stream_t table has two columns stream_id and stream_name.
• The student_id column is the primary key of the student_t table.
• Likewise, stream_id is the primary key of the stream_t table.
• Now, this is very important. 🙂 The student_t table has a foreign key as the stream_id column. So, the student_t table can only contain one of the stream_id of the stream_t table.
• So, student_t can have duplicate stream_id  across its rows, but the stream_t table can have unique ids per row. We will talk about the primary key, the foreign key in details later.

Oracle SQL JOINs Types

Here are different types of SQL JOINs :

• JOIN or INNER JOIN returns only those rows which have matching rows present in both the tables.
• The LEFT JOIN or LEFT OUTER JOIN returns all the rows from the left table and the corresponding rows from the right table.
• Just like LEFT JOIN, the RIGHT JOIN or the RIGHT OUTER JOIN returns all the rows from the right table and the corresponding rows from the left table.
• Lastly, the FULL JOIN or the FULL OUTER JOIN returns all the rows when there is a match in either left or right table.

Correlation of Oracle JOINS with Set Theory

I know most of us hate math but we like set theory. You must have gone through A ∩ B, A ∪ B, A – B, B – A.

JOIN TYPE	SET OPERATION
JOIN or INNER JOIN	A ∩ B
LEFT JOIN or LEFT OUTER JOIN	A – B
RIGHT JOIN or RIGHT OUTER JOIN	B – A
FULL JOIN or FULL OUTER JOIN	A ∪ B

SET Diagram Analogy Of Oracle JOINS

In the next section, we will talk about each individual type of joins with syntax and examples.

Oracle SQL JOIN or INNER JOIN

Oracle SQL INNER JOIN tells the database to fetch only those rows which have related rows in both the tables.

Let us say, we have two tables table1 and table2. Now, the inner join between these on the basis of common column4 will selects the records which have rows of column4 value present in both the tables.

Oracle SQL INNER JOIN Syntax

select column(s)  --in comma separated value
  from table1
 inner join table2 on table1.column = table2.column;

Oracle SQL INNER JOIN Set Diagram

Oracle SQL INNER JOIN Practical Examples

Now, let us try to do an INNER JOIN on the two tables stream_t and student_t. The idea here is, we want to display all the rows of the student_t with student_id, student_name, stream_name.

The query to fetch the data is,

select student_id
     , student_name
     , stream_name
  from student_t std inner join stream_t stm 
    on std.stream_id = stm.stream_id;

The result of the query is shown in the below.

Oracle SQL INNER JOIN More Example

Let us consider Oracle Orders and Customers table in the OE sample schema. Wondering how to install sample schemas in Oracle 12c? Check this out.

So the data model diagram for the same looks like the following.

select order_id
     , order_date
     , concat(concat(c.cust_first_name, ' '), c.cust_last_name) name
  from orders o
  join customers c on o.customer_id = c.customer_id
 where c.cust_first_name like 'S%'
 order by name desc;

We need to join as customer_id would not have made any sense in a report where we want to see order_ids by name.

Oracle JOIN Setup Tables And Data

Let us create 2 tables and insert some data to explain LEFT JOIN, RIGHT JOIN, and FULL JOIN just like INNER JOIN.

The setup script for the same would be as follows,

create table student_t (
    stud_id     number(10)
  , stud_name   varchar2(300)
  , book_id     number(10)
);

--create book_t table
create table book_t (
    bid     number(10)
  , bname   varchar2(300)
);

--insert data into student_t
insert   into student_t values (
    1000
  , 'Rozalin Mishra'
  , 12345
);

insert   into student_t values (
    1001
  , 'Sameer Mishra'
  , 23456
);

insert   into student_t values (
    1002
  , 'Anustup Panda'
  , 34567
);

insert   into student_t values (
    1003
  , 'Sipun Nayak'
  , 45678
);

insert   into student_t values (
    1004
  , 'Preeti Guria'
  , 56789
);

--insert data into book_t
insert   into book_t values (
    12345
  , 'ABCD'
);

insert   into book_t values (
    23456
  , 'XYZ'
);

insert   into book_t values (
    34567
  , 'NOMAN'
);

insert   into book_t values (
    99999
  , 'GHOST'
);

--commit the transactions
commit;

Result:
Table STUDENT_T created.
Table book_t created.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
commit complete.

Please do not worry about the create table, insert, commit. We will talk about those in detail later. Just know that we created and inserted some data to student_t and book_t.

The two tables now look like the following.

Oracle SQL LEFT JOIN or LEFT OUTER JOIN

In Oracle LEFT JOIN, all the rows from the left table and respective rows from the right table are part of the result.

Oracle SQL LEFT JOIN or LEFT OUTER JOIN Syntax

select column(s)  --in comma separated value
  from table1
 left join table2 on table1.column = table2.column;

Oracle SQL LEFT JOIN or LEFT OUTER JOIN Set Diagram

Oracle SQL LEFT JOIN or LEFT OUTER JOIN Practical Example

Let us say we want to see all the student id, name along with their respective book names. We should view the names of the students who have not even taken a book.

The query for the same would be a left join. We have to put the student_t on the left side of the left join.

select stud_id
     , stud_name
     , bname as book_name
  from student_t s
left join book_t b on s.book_id = b.bid;

The result would be as follows. Please note that the result has NULL for the able book_t.bname for the rows 4th and 5th.

The book_id not present in the book_t table are having book_name NULL. Because, it’s a left join, all the rows from the left table got into the result.

Now, let us take a look at the Oracle SQL RIGHT JOIN or RIGHT OUTER JOIN.

Oracle SQL RIGHT JOIN or RIGHT OUTER JOIN

In Oracle RIGHT JOIN, all the rows from the right table and respective rows from the left table are part of the result.

Oracle SQL RIGHT JOIN or RIGHT OUTER JOIN Syntax

select column(s)  --in comma separated value
  from table1
 right join table2 on table1.column = table2.column;

Oracle SQL RIGHT JOIN or RIGHT OUTER JOIN Set Diagram

Oracle SQL RIGHT JOIN or RIGHT OUTER JOIN Practical Example

Let us say we want to see all the book details along with their respective student names who have taken that book. We should have the books which nobody has issued.

The query for the same would be a right join. We have to put the book_t on the right side of the right join.

select stud_id
     , stud_name
     , bname as book_name
  from student_t s
right join book_t b on s.book_id = b.bid;

The result would be as follows. Please note that the result has NULL for the table student_t for the rows 4th.

The figure for the same would be as follows.

As it is the RIGHT JOIN, Oracle pulls all data rows from the right book_t table. It only pulls the respective data from the left student_t and fills the rest rows as zeros in the result.

We understood INNER, LEFT, RIGHT JOINs. Now, its time for understanding the last FULL JOIN r FULL OUTER JOIN.

Oracle SQL FULL JOIN or FULL OUTER JOIN

Oracle takes both left and right table data in the FULL JOIN. When one table respective row is missing in another table, Oracle fills that row as NULL.

Oracle SQL FULL JOIN or FULL OUTER JOIN Syntax

select column(s)  --in comma separated value
  from table1
 full join table2 on table1.column = table2.column;

Oracle SQL FULL JOIN or FULL OUTER JOIN Set Diagram

Oracle SQL FULL JOIN or FULL OUTER JOIN Practical Example

Let us say we want to see all the book details along with all the student details whether their respective row is present in the other table or not. In that case, we should go for a full join.

select stud_id
     , stud_name
     , bname as book_name
  from student_t s
full join book_t b on s.book_id = b.bid;

The result would be as follows. Please note that the result has NULL for the table student_t for the rows 6th. Apart from that, we got 4th, 5th rows book_t columns as NULL.

The figure for the same would be as follows.

Oracle SQL JOINS With Table Aliases

Please note that just like Oracle column aliases, we can provide table aliases. This is convenient and useful during JOINS.

Oracle SQL JOINS With Table Aliases Syntax

select tab1.col1
     , tab2.col2
from table1 tab1 
 join table2 tab2 on tab1.colN = tab2.colN;

Oracle SQL JOINS With Table Aliases Restriction

1. You must refer the table by its alias name else Oracle will show an error. 

Oracle SQL JOINS With Table Aliases Example

select first_name || ' ' || last_name
     , dept.department_name
  from employees emp
  join departments dept on emp.department_id = dept.department_id;

In this example, emp is the alias for employees table and dept is for the departments table. We can refer to any column using the aliases now.  So, dept.department_name works now. 

The result of the above query is as follows,

What will happen if we use the original table name instead of aliases?

Oracle will not be able to understand. So, Oracle will give invalid identifier error. Please take a look at the following example.

select first_name || ' ' || last_name
    , dept.department_name
  from employees emp
  join departments dept on employees.department_id = dept.department_id;

When you run the query, Oracle will show the following error.

ORA-00904: "EMPLOYEES"."DEPARTMENT_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 4 Column: 28

This usually happens by mistake. While refactoring we forget to change all the table name references to aliases. 

I have used table aliases in all the above examples. Now let me rewrite the above query without any use of aliases.

select first_name || ' ' || last_name
    , departments.department_name
  from employees
  join departments on employees.department_id = departments.department_id;

Table aliases do not affect the end result. It only helps to avoid writing long table names and makes it simpler. 

Oracle Proprietary JOIN Syntax VS ANSI JOIN Syntax

We have used standard ANSI Joins syntax till now. However, Oracle provides the equivalent of these INNER joins, LEFT JOIN, RIGHT JOIN, FULL JOIN as well.

Oracle Proprietary INNER JOIN Syntax

select column1, column2, column3
from table1, table2
here table1.column4 = table2.column4;

So, here is the query to get all the students id, name and book name. Students has to have a book to appear in the result.

select stud_id "Student ID"
     , stud_name "Student Name"
     , bname "Book Name"
  from student_t s
     , book_t b
 where s.book_id = b.bid;

The above proprietary INNER JOIN is same as the following ANSI JOIN.

select stud_id "Student ID"
     , stud_name "Student Name"
     , bname "Book Name"
  from student_t s
  join book_t b on s.book_id = b.bid;

Proprietary JOIN to ANSI JOIN Conversion

Here are the following things we did to convert the Oracle ANSI JOIN Syntax to Oracle proprietary syntax and vice versa.

• Use comma instead of JOIN keyword.
• Use join condition inside the Oracle WHERE Clause. ON keyword is not needed. The condition is now part of the Oracle WHERE section.

If you like this topic on SQL JOINS, please tell us in the comment section below. See you friend !! 🙂