Oracle OUTER JOIN
نشر بواسطة : Obay Salah , December 2, 2024
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;
Image representation of left outer join
Example
In this example, we are performing left outer join on the already created tables ?suppliers? and ?order1?.
The following example would return all records from table ?suppliers? and only those records from table ?order1? where the join fields are equal.
Execute this query
SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number FROM suppliers LEFT OUTER JOIN order1 ON suppliers.supplier_id = order1.supplier_id;
Output
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;
Image representation of Right Outer Join
Example
In this example, we are performing right outer join on the already created tables ?suppliers? and ?order1?.
The following example would return all rows from the order1 table and only those rows from the suppliers table where the join condition is met.
Execute this query
SELECT order1.order_number, order1.city, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN order1 ON suppliers.supplier_id = order1.supplier_id;
Output
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;
Image representation of Full Outer Join
Example
In this example, we are performing full outer join on the already created tables ?suppliers? and ?order1?.
The following example will return all rows from the ?suppliers? table and all rows from the ?order1? table and whenever the join condition is not met, it places the NULL value.
Execute this query
SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number FROM suppliers FULL OUTER JOIN order1 ON suppliers.supplier_id = order1.supplier_id;
Output
Comments
لايوجد تعليق حتى الان