Description
The SQL keyword FULL OUTER JOIN is a type of JOIN that returns all the rows from both the tables joined.
- It returns all the rows from the left table.
- If a matching record exists on the right table, then the corresponding data is used against the columns coming from that table.
- If a matching record does not exist on the right table, then the NULL value is used against the columns coming from that table.
- It returns all the rows from the right table, along with the data from the left table for the columns coming from that table.
- If a matching record exists on the left table, then the corresponding data is used against the columns coming from that table.
- If a matching record does not exist on the left table, then the NULL value is used against the columns coming from that table.
This is a rarely used JOIN, as it simply displays all the rows from both tables.
The below SQL fetches all customers and all orders from the respective tables using the FULL OUTER JOIN.
SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date, orders.order_value
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;