Whether you’re a first-time SQL user or an expert who knows most of it by heart, there’s always something new to learn about how databases work. I will explain what the OUTER JOIN in SQL does and how to use it in real-life applications.
OUTER JOIN is used to join tables and get all records from those tables, even for those records that have no matching values. This returns NULL because there are no values for that column in the table.
Example
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;How code works
The SQL command selects particular fields from two tables by querying customer_id, first_name, and amount from Customers, and amount from Orders.
The final query will return those customer_id and orders where there’s a match between the two. This is because, we join all the tables and return the result set containing all the rows in which there’s a match.
Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;With WHERE Clause
The SQL command can have an optional WHERE clause with the FULL OUTER JOIN statement. For e.g., it can consist of a table field such as Customers.first_name
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;SQL statement that joins two tables is running, and the matching rows are selected where the sum is greater than 500
With AS Alias
We can use alternative expressions AS to make our SELECT query short and clean.
SELECT C.cat_name, P.prod_title
FROM Category AS C
FULL OUTER JOIN Products AS P
ON C.cat_id= P.cat_id;The SQL command finds the rows that are most common between the Category and Products tables.