Quick Reach
Full outer join of SQL
The outer join is a type of join that retrieves all rows from both the left and right tables.
The column values that do not exist in either table will be filled with the null value.
Syntax of outer join clause
The general syntax of SQL full outer join clause is:
Select * from table_name1
full outer Join table_name2 ON table_name1.common_field = table_name2.common_field
Example of using SQL outer join clause
For our example, we will use departmental store scenario, as explained in the main Joins chapter. Consider a departmental store’s database tables:
- tbl_employee
- tbl_customers_orders
- tbl_products
Where tbl_employee stores the employees data, tbl_prodcuts stores the products in the store and tbl_customers_orders store the orders placed by the customers.
This example will retrieve all records from the left and right tables where the one after the select from (left), and the other after the join clause (right).
The outer join query:
|
1
|
select * from tbl_employee TE full outer join tbl_customers_orders TC ON TE.emp_id=TC.emp_id
|
See the graphic of all tables and outer join
As you can see in above graphic, all rows are retrieved from both tables by using the outer join.
Related: Inner join | Left join | Right join | Joins in SQL
Leave A Comment?