FULL OUTER JOIN statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from both of the tables.
-- full join Customers and Orders tables -- based on their shared customer_id columns -- Customers is the left table -- Orders is the right table SELECT Customers.customer_id, Customers.first_name, Orders.item FROM Customers FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;
Here, the SQL query performs a
FULL OUTER JOIN on two tables, Customers and Orders. This means that the result set contains all the rows from both tables, including the ones that don't have common customer_id values.
FULL OUTER JOIN SYNTAX
The syntax of the SQL
FULL OUTER JOIN statement is:
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2;
- table1 and table2 are the tables to be joined
- column1 and column2 are the related columns in the two tables
Example: SQL OUTER Join
SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer;
Here, the SQL command selects the
first_name columns (from the
Customers table) and the
amount column (from the
The result set will contain all rows of both the tables, regardless of whether there is a match between
customer_id (of the
Customers table) and
customer (of the
FULL OUTER JOIN With WHERE Clause
FULL OUTER JOIN statement can have an optional WHERE clause. For example,
SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer WHERE Orders.amount >= 500;
Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.
SQL FULL OUTER JOIN With AS Alias
We can use AS aliases inside
FULL OUTER JOIN to make our query short and clean. For example,
-- use alias C for Categories table -- use alias P for Products table SELECT C.category_name, P.product_title FROM Categories AS C FULL OUTER JOIN Products AS P ON C.category_id = P.cat_id;
Here, the SQL command performs a full outer join on the Categories and Products tables while assigning the aliases C and P to them, respectively.