ORACLE
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
Create Tables
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

OUTER JOIN



Oracle - Outer Join


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_IDSUPPLIER_NAMEORDER_NUMBER
1Reebok Shoes101
2USPA Shirts102
50Flipkart-
50Apple-
3Kingfisher-
21Microsoft-
21gaziabad-
30Google-
28Paris-
22USPA 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_NUMBERCITYSUPPLIER_NAME
101HyderabadReebok Shoes
102MumbaiUSPA 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;  

Output :

SUPPLIER_IDSUPPLIER_NAMEORDER_NUMBER
1Bata Shoes101
2USPA Shirts102
50Flipkart-
50Apple-
3Kingfisher-
21Microsoft-
21gaziabad-
30Google-
28Paris-
22USPA Shirts-


What Is Oracle

What Is Oracle

posted on 2019-11-29 01:11:21 - ORACLE Tutorials


TRIGGER

ORACLE - Trigger

posted on 2019-11-28 22:14:22 - ORACLE Tutorials


CURSOR

ORACLE - Cursor

posted on 2019-11-28 22:13:54 - ORACLE Tutorials


Prompt Examples

ChatGPT Prompt Examples

posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


Use Cases

Chat GPT Key Use Cases

posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


Prompt Frameworks

Prompt Frameworks

posted on 2023-06-21 19:33:06 - ChatGPT Tutorials