SQL FULL JOIN

by Manzoor Siddiqui · Published September 17, 2015 · Updated September 23, 2015

SQL FULL JOIN returns all the rows from the left table and all the rows from the right table. If there are no matching values in either of the tables then non-matching values returned as NULL.

Syntax

SELECT columnName(s)
FROM Table1 FULL JOIN Table2
ON Table1.columnName = Table2.columnName

SQL FULLJOIN returns all the rows from both the tables mentioned with the FULL JOIN clause. If there are no matching values in the tables then output rows will return NULL values.

Refer below image to understand FULL JOIN, here green part is output for FULL JOIN.

Here we will consider one example for FULL JOIN condition where we will use two tables named as SalesDetails and ClientDetails.

Table Name: SalesDetails

Table Name: ClientDetails

As you can see, first table has 8 records and second table has 10 records. From above two tables, we need columns OrderID and OrderDate from SalesDetails table and one column ClientName from ClientDetails table and sorted in descending order by OrderDate column. So to get the output from two tables we will use FULL JOIN query as given below.

SELECT SalesDetails.OrderID, ClientDetails.ClientName, SalesDetails.OrderDate
FROM SalesDetails FULL JOIN ClientDetails 
ON SalesDetails.ClientID = ClientDetails.ClientID
ORDER BY OrderDate DESC

Kindly note the above highlighted values, in FULL JOIN all the values are returned from both the tables and where there was no matching values it has returned NULL. You can refer ClientID columns from both the table and check the output of FULL JOIN.