SQL FULL OUTER JOIN

The SQL FULL OUTER JOIN command returns all rows from both tables being joined, regardless of whether the tables have matching values in the join condition.

• If there is a match between the two tables, the joined row is shown.
• If there’s no match, the result will still include the row, but with NULL values in the columns from the table that doesn’t have a match.

Syntax

SELECT column_list
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

In the syntax:

• table1, table2: The tables being joined.
• common_column: The column used to match rows between the tables.

Example 1: Joining Customers and Orders Using SQL FULL OUTER JOIN

Suppose there are two tables:

Table 1: Customers

Table 2: Orders

Here is the query using SQL FULL OUTER JOIN:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Copy to clipboard
Copy to clipboard

The output will be:

Example 2: Joining Departments and Employees Using SQL FULL OUTER JOIN

Suppose there are two tables:

Table 1: Departments

Table 2: Employees

Here is the query using SQL FULL OUTER JOIN:

SELECT Departments.DeptID, Departments.DeptName, Employees.EmpID, Employees.EmpName
FROM Departments
FULL OUTER JOIN Employees
ON Departments.DeptID = Employees.DeptID;

Copy to clipboard
Copy to clipboard

The output will be:

Example 3: Joining Students and Courses Using SQL FULL OUTER JOIN

Suppose there are two tables:

Table 1: Students

Table 2: Courses

Here is the query using SQL FULL OUTER JOIN:

SELECT Students.StudentID, Students.Name, Courses.CourseID, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.StudentID = Courses.StudentID;

Copy to clipboard
Copy to clipboard

The output will be:

Frequently Asked Questions

1. What are the 4 types of JOINs in SQL?

The four basic types of SQL joins are:

• INNER JOIN: Returns only rows where there is a match in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table; unmatched rows from the right show as NULL.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table; unmatched rows from the left show as NULL.
• FULL OUTER JOIN: Returns all rows from both tables, filling in NULL where matches don’t exist.

2. What does (+) mean in SQL JOINs?

The (+) symbol is used in Oracle SQL to indicate an outer join.

Example:

SELECT a.id, b.name
FROM tableA a, tableB b
WHERE a.id = b.id(+);

Copy to clipboard
Copy to clipboard

Here, (+) means a LEFT OUTER JOIN—it returns all rows from tableA and matches from tableB, inserting NULL where no match exists.

3. What’s the difference between CROSS JOIN and FULL OUTER JOIN?

• CROSS JOIN: Produces the Cartesian product of two tables, meaning every row from the first table is paired with every row from the second. If tableA has 3 rows and tableB has 4 rows, the result will have 12 rows.
• FULL OUTER JOIN: Combines rows from both tables based on a condition and includes unmatched rows, filling missing values with NULL.