SQL OUTER Joins:
A NORMAL join finds values from two tables that are in a relation to each other. Usually, this relation is equality (=), but it can also be all sorts of operations that either return true or false. The important thing is that a NORMAL join only returns matching rows from both (joined) tables. Obviously, a row whose column-value is not found in the other table's joined column is not returned at all. But, sometimes, we need to show these rows as well.
The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.
A LEFT OUTER JOIN results in a table which includes joined rows and any unmatched rows from the table listed to the left. The keyword LEFT in a LEFT OUTER JOIN tells you that the resulting table will include unmatched rows from the table to the LEFT of the keyword JOIN in the FROM clause of the query. In other words when we use LEFT OUTER JOIN clause we point out that we want to get all rows from the left table listed in our FROM clause, even if they don’t have a match in the right table.
A RIGHT OUTER JOIN is just reverse of LEFT OUTER JOIN.
According to old oracle syntax(which uses ‘+’ for outer join operations) :
An outer join uses a (+) on the side of the operator (equality operator) where we want to have nulls returned if no value matches.
The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.
According to http://www.oracle.com/technology/products/oracle9i/
daily/may31.html
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
- To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
- To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
- To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
Full outer joins – They are like a left or right outer join, but all the rows from both row sources are returned - if they "join" - fine - they will be joined - but if they don't have a match in the other table by the join key(s) they are returned anyway.
| Employee Table: | |
|---|---|
| LN | D_Id |
| Kamal | 31 |
| Jones | 33 |
| Singh | 33 |
| Smith | 34 |
| Robinson | 34 |
| Jasper | 36 |
| Department Table: | |
|---|---|
| Department Name | D_Id |
| Sales | 31 |
| Engineering | 33 |
| Clerical | 34 |
| Marketing | 35 |
Example left outer join (ANSI 92 standard syntax):
SELECT distinct *
FROM employee
LEFT OUTER JOIN
department
ON employee.D_id = department.D_id
Example left outer join (non-standard syntax):
SELECT *
FROM employee
WHERE employee.D_id = department.D_id(+)
Example right outer join (ANSI 92 standard syntax):
SELECT *
FROM employee
RIGHT OUTER JOIN
department
ON employee.D_id = department.D_id
SELECT *
FROM employee
WHERE employee.D_id )+) = department.D_id
| LN | D_Id | Department Name | D_Id |
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Mathematicsg | 33 |
| Robinson | 34 | Clerical | 34 |
| Singh | 33 | mathematics | 33 |
| Kamal | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
Example full outer join (ANSI 92 standard syntax):
SELECT *
FROM employee
FULL OUTER JOIN
department
ON employee.D_id = department.D_id
| LN | D_Id | Department Name | D_Id |
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| Jasper | 36 | NULL | NULL |
| Singh | 33 | Engineering | 33 |
| Kamal | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
