The SQL Full Join

SQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fills NULL values for missing matches on either side. In short, full join is a type of outer join that combines the result-sets of both left and right joins.

MySQL does not support Full Outer Join. Instead, you can imitate its working by performing union operation between the result-sets obtained from Left Join and Right Join.

Let us understand this concept in detail with the help of a Venn diagram below. Assume that we have two tables as two sets (represented by circles). The result-set (or newly joined table) obtained using full join is nothing but the union of these two sets.

You can also achieve the equivalent result-set of FULL JOIN by performing the UNION operation on result-sets of the LEFT JOIN and RIGHT JOIN.

Syntax

Following is the basic syntax of Full Join in SQL −

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.Using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as −

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, ''2009-10-08 00:00:00'', 3, 3000.00),
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00),
(103, ''2008-05-20 00:00:00'', 4, 2060.00);

The table is displayed as follows −

Following query joins the two tables CUSTOMERS and ORDERS in SQL Server −

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The resultant table is produced as follows −

Joining Multiple Tables with Full Join

The Full Join query can also be used to join more than just two tables. To do that, we sequentially combine two tables at a time, until all the tables are joined together.

Note that in MySQL database, there is no provision to directly use the FULL JOIN keyword to perform join operation on multiple tables. Instead, calculate the UNION of LEFT JOIN and RIGHT JOIN on two tables at a time, until all the tables are joined.

Syntax

The syntax to join multiple tables using Full Join is given below −

SELECT column1, column2, column3... 
FROM table1
FULL JOIN table2
ON condition_1
FULL JOIN table3
ON condition_2
....
....
FULL JOIN tableN
ON condition_N;

Example

To demonstrate Full Join, let us consider the sample tables CUSTOMERS and ORDERS that we previously created, and create another table name EMPLOYEE using the following query −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

The EMPLOYEE table created, will be as shown below −

Let us join these three tables using the full join query given below −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME 
FROM CUSTOMERS 
FULL JOIN ORDERS 
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
FULL JOIN EMPLOYEE 
ON ORDERS.OID = EMPLOYEE.EID; 

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

The resultant table is obtained as follows −

Full Join with WHERE Clause

Joins use the ON clause to filter records by default. Let us suppose there is a further requirement to filter these records based on a certain condition/constraint, we can also make use of the WHERE clause with Joins.

Syntax

The syntax of Full Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example

Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The resultant table after applying the WHERE clause with full join contains the rows that has amount values greater than 2000.00 −