An outer join is similar to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 standard.
Left Outer Join :
Left Outer Join returns all rows from the left (first) table specified in the ON condition and only those rows from the right (second) table where the join condition is met.
Syntax :
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Example :
In this example, we are performing left outer join on the already created tables "suppliers" and "orders".
The following example would return all records from table "suppliers" and only those records from table "orders" where the join fields are equal.
Execute this query :
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_number
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
Output :
SUPPLIER_ID
SUPPLIER_NAME
ORDER_NUMBER
1
Reebok Shoes
101
2
USPA Shirts
102
50
Flipkart
-
50
Apple
-
3
Kingfisher
-
21
Microsoft
-
21
gaziabad
-
30
Google
-
28
Paris
-
22
USPA Shirts
-
Right Outer Join :
The Right Outer Join returns all rows from the right-hand table specified in the ON condition and only those rows from the other table where the join condition is met.
Syntax :
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Example :
In this example, we are performing right outer join on the already created tables "suppliers" and "orders".
The following example would return all rows from the orders table and only those rows from the suppliers table where the join condition is met.
Execute this query :
SELECT orders.order_number, orders.city, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
Output :
ORDER_NUMBER
CITY
SUPPLIER_NAME
101
Hyderabad
Reebok Shoes
102
Mumbai
USPA Shirts
Full Outer Join :
The Full Outer Join returns all rows from the left hand table and right hand table. It places NULL where the join condition is not met.
Syntax :
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
Example
In this example, we are performing full outer join on the already created tables "suppliers" and "orders".
The following example will return all rows from the "suppliers" table and all rows from the "orders" table and whenever the join condition is not met, it places the NULL value.
Execute this query :
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_number
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;