you are at Encyclopedia of SQL >> OUTER JOIN
OUTER JOIN
OUTER JOIN returns all records of one table if even not every record is matched with record in second table. There are few kinds of JOINS :
LEFT JOIN - returns all rows of left table even there don't matched with records in second table
RIGHT JOIN - returns all records of right table even they aren't matched with records in left table
In some databases above joins are known accordingly as LEFT OUTER JOIN and RIGHT OUTER JOIN
Syntax
SELECT `column_name1`,`column_name2`
FROM `table_name1`
LEFT or RIGHT JOIN `column_name2`
ON `table_name1`.`column_name1`=`table_name2`.`column_name2`
Example of LEFT JOIN
Display name and surname of people from `people` table with order amount from `orders` table.
| id | name | surname | age |
|---|
| 1 | Paweł | Kowalski | 3 |
| 2 | Piotr | Janik | 7 |
| 3 | Michał | Nowak | 13 |
| id | order_amount | person_id |
|---|
| 1 | 100 | 1 |
| 2 | 200 | 2 |
| 3 | 100 | 2 |
| 4 | 100 | 20 |
query
SELECT *
FROM `people`
LEFT JOIN `orders`
ON `orders`.`person_id`=`people`.`id`
result
| id | name | surname | age | id | order_amount | person_id |
|---|
| 1 | Paweł | Kowalski | 3 | 1 | 100 | 1 |
| 2 | Piotr | Janik | 7 | 2 | 200 | 2 |
| 2 | Piotr | Janik | 7 | 3 | 100 | 2 |
| 3 | Michał | Nowak | 13 | NULL | NULL | NULL |
At Oracle there is used plus (+) after WHERE keyword on the second side of table name, which we want to display all records. Code from this example is as the follows:
SELECT *
FROM `people`,`orders`
WHERE `people`.`id`=`orders`.`person_id`(+)
Example of RIGHT JOIN
Display all orders with name and surname of customer from `people` table
| id | name | surname | age |
|---|
| 1 | Paweł | Kowalski | 3 |
| 2 | Piotr | Janik | 7 |
| 3 | Michał | Nowak | 13 |
| id | order_amount | person_id |
|---|
| 1 | 100 | 1 |
| 2 | 200 | 2 |
| 3 | 100 | 2 |
| 4 | 100 | 20 |
query
SELECT *
FROM `people`
RIGHT JOIN `orders`
ON `orders`.`person_id`=`people`.`id`
result
| id | order_amount | person_id | id | name | surname | age |
|---|
| 1 | 100 | 1 | 1 | Paweł | Kowalski | 3 |
| 2 | 200 | 2 | 2 | Piotr | Janik | 7 |
| 3 | 100 | 2 | 2 | Piotr | Janik | 7 |
| 4 | 100 | 20 | NULL | NULL | NULL | NULL |
[ wróć na górę strony ]
