Difference between revisions of "Outer Joins"
| (3 intermediate revisions by the same user not shown) | |||
| Line 17: | Line 17: | ||
table is deficient when matching rows. (That is, it performs a combination of left and right | table is deficient when matching rows. (That is, it performs a combination of left and right | ||
outer joins.) | outer joins.) | ||
===LEFT OUTER JOIN === | |||
''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null ''' | |||
''' This type of join returns all rows froM the LEFT hand table specified in the ON condition and ONLY those rows from the other table where the joined fields are equal(join condition is met). if there are no match on table 2 then it will have a null value''' | |||
SELECT c.lastname, c.firstname, o.order# | SELECT c.lastname, c.firstname, o.order# | ||
FROM customers c LEFT OUTER JOIN orders o | FROM customers c LEFT OUTER JOIN orders o | ||
USING (customer#) | USING (customer#) | ||
ORDER BY c.lastname, c.firstname | ORDER BY c.lastname, c.firstname | ||
A | |||
SELECT A.sales_date, A.order_id, A.product_id, B.product_name | |||
FROM sales A LEFT JOIN product B | |||
ON A.product_id = B.product_id ; | |||
''' ORACLE SYNTAX ''' | |||
SELECT A.sales_date, A.order_id, A.product_id, B.product_name | |||
FROM sales A , product B | |||
WHERE A.product_id = B.product_id(+) ; | |||
=== Full Outer Join === | |||
''' Returns all rows for the LEFT and RIGHT table with NULLS in place where the join condition is not met ''' | |||
SELECT A.sales_date, A.order_id, A.product_id, B.product_name | |||
FROM sales A FULL OUTER JOIN product B | |||
ON A.product_id = B.product_id ; | |||
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]] | [[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]] | ||
Latest revision as of 15:28, 1 November 2017
An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL records so that they are included in the output
An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords
limitations when using the traditional approach to outer joins:
- The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.
- A condition that includes the outer join operator can’t use the IN or ORoperator.
Outer Joins: JOIN Method
When creating a traditional outer join with the outer join operator, the join can be applied to only one table—not both. However, with the JOIN keyword, you can specify which table the join should be applied to by using a left, right, or full outer join. Left and right outer joins specify which table the outer join should be applied to, based on the table’s location in the join condition. For example, a left outer join instructs Oracle to keep any rows in the table listed on the left side of the join condition, even if no matches are found with the table listed on the right. A full outer join keeps all rows from both tables in the results, no matter which table is deficient when matching rows. (That is, it performs a combination of left and right outer joins.)
LEFT OUTER JOIN
The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null
This type of join returns all rows froM the LEFT hand table specified in the ON condition and ONLY those rows from the other table where the joined fields are equal(join condition is met). if there are no match on table 2 then it will have a null value
SELECT c.lastname, c.firstname, o.order# FROM customers c LEFT OUTER JOIN orders o USING (customer#) ORDER BY c.lastname, c.firstname
SELECT A.sales_date, A.order_id, A.product_id, B.product_name FROM sales A LEFT JOIN product B ON A.product_id = B.product_id ;
ORACLE SYNTAX
SELECT A.sales_date, A.order_id, A.product_id, B.product_name FROM sales A , product B WHERE A.product_id = B.product_id(+) ;
Full Outer Join
Returns all rows for the LEFT and RIGHT table with NULLS in place where the join condition is not met
SELECT A.sales_date, A.order_id, A.product_id, B.product_name FROM sales A FULL OUTER JOIN product B ON A.product_id = B.product_id ;