Description

SQL FULL JOIN can be used to join two tables, irrespective of the match rows, which always generates all the rows from both the tables.

  • It is a type of outer join, so it is also referred to as the full outer join, which combines the functions of LEFT JOIN and RIGHT JOIN.
  • It can be implemented on SELECT, UPDATE, and DELETE statements.
  • It always generates a result set containing the rows equal to the number of rows on both tables together.
  • If there is a row in either of the tables but no matching row in the other table, then such columns are filled with NULL values.

The left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

Also, check the other types of outer join - left join and right join.

Syntax

Here is the basic syntax of a SELECT statement with a RIGHT JOIN.

  • The table1 and table2 represents the left and right table names.
  • The column_list represents the column names from either or both the tables.
  • The column1 and column2 are the columns from the left and right tables, which are used for creating the FULL JOIN.
SELECT column_list
FROM table1
FULL JOIN table2 ON table1.column1 = table2.column2;

Table Data

Consider the below data in the orders, customers, and shippers tables, with the below observations.

  • Tables orders and customers are relationally connected via customer_id column.
  • Tables orders and shippers are relationally connected via shipper_id column.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Full Join Multiple Columns

The below SQL statement fetches data from both tables by joining them together as mentioned below.

  • Returns all the rows from the left table along with the matching row data from the right table.
  • Also, returns all the rows from the right table along with the matching row data from the left table.
  • If there is a row in either of the tables but no matching row in the other table, then such columns are filled with NULL values.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains all rows from both tables, as shown below.

order_id order_date order_value customer_name
10250 2014-07-05 1807.5 Dominique Perrier
10251 2014-07-15 1159 Fran Wilson
10252 2014-07-18 2822 Maria Anders
10253 2014-07-19 2575.3 Dominique Perrier
10254 2014-07-24 1256.25 Maria Anders
10255 2014-07-31 9247.5 NULL
10252 2014-07-18 2822 Maria Anders
10251 2014-07-15 1159 Fran Wilson
10250 2014-07-05 1807.5 Dominique Perrier
NULL NULL NULL Martin Blank

Full Join All Columns

The below SQL statement fetches data from both the tables as mentioned below.

  • The SELECT * returns all the columns from both the tables, without repeating the joined columns.

Run this on IDE

SELECT *
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains data from both the joined tables as shown below.

order_id order_date order_value customer_id supplier_id customer_name address city country postal_code
10250 2014-07-05 1807.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10252 2014-07-18 2822 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10253 2014-07-19 2575.3 3 2 Dominique Perrier 25, rue Lauriston Paris France 75016
10254 2014-07-24 1256.25 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10255 2014-07-31 9247.5 5 1 NULL NULL NULL NULL NULL
10252 2014-07-18 2822 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10250 2014-07-05 1807.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016
NULL NULL NULL 4 NULL Martin Blank Via Monte Bianco 34 Turin Italy 10100

Tips & Recommendations

An outer join is a join that includes rows in the result set even though there may not be a match between the rows of the joined tables.

In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

In an outer join, wherever the database system can't find a match record, it places NULL values to indicate that the data do not exist in columns of such table.

Overall

We now know how can we use a SELECT Statement with FULL JOIN to combine tables and fetch data from the joined tables.

Related Links