SQL Joins in Oracle

In this post let us look at different joins in Oracle SQL, as this is ANSI standard the same join should hold good for other databases too.
The following are the joins we are going to look at:

1. Equijoin
2. Outer Joins
2.1. Left Outer Join
2.2. Right Outer Join
2.3. Full Outer Join 
3. Self Join

Sample tables required for this exercise:
CREATE TABLE customer(customer_id varchar2(10), customer_name varchar2(20),
CONSTRAINT CID_PK PRIMARY KEY (customer_id));

CREATE TABLE contact(customer_id varchar2(10), phone varchar2(12),
CONSTRAINT cid_fk FOREIGN KEY (customer_id) REFERENCES customer(customer_id));

--Values for customer table
INSERT INTO customer (customer_id, customer_name) VALUES ('123', 'Jones')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('124', 'Smith')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('125', 'Martin')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('126', 'Blake')
/

--Values for contact
INSERT INTO contact (customer_id, phone) VALUES ('123', '123-456-7890')
/
INSERT INTO contact (customer_id, phone) VALUES ('124', '123-456-0000')
/
INSERT INTO contact (customer_id, phone) VALUES (NULL, '222-456-7890')
/
INSERT INTO contact (customer_id, phone) VALUES (NULL, '333-456-7890')
/

The two customers Martin & Blake doesn't have any phone numbers assigned
The two phone numbers 222-456-7890 & 333-456-7890 has not been assigned to any customers

1. Equijoin:
Return those customers who have a phone numbers assigned
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust, contact ct WHERE cust.customer_id = ct.customer_id;

2. Outer Joins:
2.1. Left Outer Join:
Retrieve all Customers irrespective of whether they have a phone number assigned,
i.e. Customers who have a phone number assigned and those who have not a phone number assigned.
All the records from the table on the Left and only matching records from the table on the Right.
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust LEFT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;
   
Retrieving the same result with Right Outer Join:
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM contact ct RIGHT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;   
   
2.2. Right Outer Join:
Retrieve all Phone numbers irrespective of whether the phone number is assigned to a customer or not:
All the records from the table on the Right (i.e. contact table) and only matching records from the table on the Left (i.e. Customer table).
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust RIGHT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

Retrieving the same result with Left Outer Join:
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM contact ct LEFT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;   
   
2.3. Full Outer join:
Retrieves all the Customers (whether a phone number is assigned or not) as well as all the Phone numbers (whether the phone number is assigned to a Customer or not)
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust FULL OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

3. Self Join:
In the employee table the Manager is also an record in the same table i.e. manager_id is one of the emp_id.
For an employee if there is no manager then the manager_id is left blank.

create table employee(emp_id number, name varchar2(25), manager_id number)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(100,'Jones',null)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(101,'Smith',100)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(102,'Martin',100)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(103,'Blake',null)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(104,'Adams',103)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(105,'James',103)
/

The Self join query retrieve the Employee information together with Manager name.
SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1, employee t2
where t1.manager_id = t2.emp_id;

What if I were to retrieve all employees who have a Manager assigned or not, we need to use the Outer Join
Here is the Query:
SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1 LEFT OUTER JOIN employee t2
ON t1.manager_id = t2.emp_id;

Here is the sample Output:
SQL> CREATE TABLE customer(customer_id varchar2(10), customer_name varchar2(20),
  2  CONSTRAINT CID_PK PRIMARY KEY (customer_id));

Table created.

SQL> CREATE TABLE contact(customer_id varchar2(10), phone varchar2(12),
  2  CONSTRAINT cid_fk FOREIGN KEY (customer_id) REFERENCES customer(customer_id));

Table created.

SQL> --Values for customer table
SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('123', 'Jones')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('124', 'Smith')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('125', 'Martin')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('126', 'Blake')
  2  /

1 row created.

SQL> --Values for contact
SQL> INSERT INTO contact (customer_id, phone) VALUES ('123', '123-456-7890')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES ('124', '123-456-0000')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES (NULL, '222-456-7890')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES (NULL, '333-456-7890')
  2  /

1 row created.

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust, contact ct WHERE cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust LEFT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
125        Martin                                                              
126        Blake                                                               

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM contact ct RIGHT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
125        Martin                                                              
126        Blake                                                               

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust RIGHT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM contact ct LEFT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust FULL OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   
125        Martin                                                              
126        Blake                                                               

SQL> create table employee(emp_id number, name varchar2(25), manager_id number)
  2  /

Table created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(100,'Jones',null)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(101,'Smith',100)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(102,'Martin',100)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(103,'Blake',null)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(104,'Adams',103)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(105,'James',103)
  2  /

1 row created.

SQL> SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1, employee t2
  2  where t1.manager_id = t2.emp_id;

    EMP_ID NAME                      MANAGER_NAME                              
---------- ------------------------- -------------------------                 
       102 Martin                    Jones                                     
       101 Smith                     Jones                                     
       105 James                     Blake                                     
       104 Adams                     Blake                                     

SQL> SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1 LEFT OUTER JOIN employee t2
  2  ON t1.manager_id = t2.emp_id;

    EMP_ID NAME                      MANAGER_NAME                              
---------- ------------------------- -------------------------                 
       102 Martin                    Jones                                     
       101 Smith                     Jones                                     
       105 James                     Blake                                     
       104 Adams                     Blake                                     
       103 Blake                                                               
       100 Jones                                                               

6 rows selected.