Contents
SQL FULL OUTER JOIN Keyword: Main Tips
- This keyword will return the left and right table rows.
- After the rows are returned it will also merge that data.
- The rows with no matches will be listed anyway.
FULL OUTER JOIN: Syntax
Example
SELECT col_name(s)
FROM tbl1
FULL OUTER JOIN table2 ON tbl1.col_name = tbl2.col_name;Demo Database
In the table below you can see a piece of Customers table:
| ID | Name | Contact | Address | City | Postal_Code | Country |
|---|---|---|---|---|---|---|
| 1 | Ben Choplinks | Ben Choplink | Obeesre Str. 51 | Rome | 11207 | Italy |
| 2 | Donald Rich | Donald Richario | Avda. de la Confgfstitución 4122 | Tallin | 17021 | Estonia |
| 3 | Lilly Smilkins | Lilly Smilkin | Matadsderos 2312 | Eguero | 14023 | Mexico |
| 4 | Brandinina | Tom Hitchins | 110 Hanegover Sq. | London | WB2 2DP | UK |
| 5 | Carizmos | Christiano Kerrys | Berguvsesvägen 9 | Luleå | S-968 43 | Sweden |
The Orders table:
| ID | Name | Count | Price | Date |
|---|---|---|---|---|
| 1 | Apple | 120 | 11.9 | 2017-08-18 |
| 2 | Milk | 50 | 9.1 | 2016-12-19 |
| 3 | Bread | 60 | 15.4 | 2017-06-21 |
| 3 | Water | 150 | 12.3 | 2017-07-10 |
| 3 | Chocolate | 85 | 21.9 | 2016-12-25 |
SQL FULL OUTER JOIN: Example
In the example below picks out all of the data from customers and orders:
Example
SELECT Customers.Name, Customer_orders.ID
FROM Customers
FULL OUTER JOIN Orders ON Customers.ID=Customer_orders.customer_id
ORDER BY Customers.Name;The final data looks like this:
| CustomersName | OrdersID |
|---|---|
| Ben Choplinks | |
| Donald Rich | 20408 |
| Lilly Smilkins | 10574 |
| 26482 | |
| 37541 |