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 fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.

Let us understand this concept in detail with the help of two sample tables. Say we have two employee tables, given in the figure below, which consists of their details. If we join these tables using FULL JOIN, matched rows from both tables are adjoined and unmatched rows are added into the result with NULL filling up the missing records.

Syntax

Following is the basic syntax of Full Join in SQL −

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Here, the given condition could be any given expression based on your requirement.

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 (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

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 (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2009-10-08 00:00:00', 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2009-10-08 00:00:00', 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2009-11-20 00:00:00', 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 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 |
+-----+---------------------+-------------+---------+

Using the query shown below, we are trying to join the two tables Customers and Orders.

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

Output

The resultant table is produced as follows −

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
+------+----------+--------+---------------------+

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. However, the two tables being combined with Full Join must have common columns to be able to use them with the ON clause.

Syntax

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

SELECT column1, column2, column3…
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
FULL JOIN table3
ON table2.column_name = table3.column_name
.
.
.

Example

To demonstrate Full Join, let us consider the sample tables Customers and Orders that we previously created; along with the newly created table “Employee”.

We will try to create the Employee table using the query below −

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);
INSERT INTO EMPLOYEE VALUES (100, 'ALEKHYA', 3623);
INSERT INTO EMPLOYEE VALUES (101, 'REVATHI', 1291);
INSERT INTO EMPLOYEE VALUES (103, 'VIVEK', 3426);
INSERT INTO EMPLOYEE VALUES (100, 'ALEKHYA', 3456);

The details of Employee table can be seen below.

+-----+---------------+------------+
| EID | EMPLOYEE_NAME | SALES_MADE |
+-----+---------------+------------+
| 102 | SARIKA        |       4500 |
| 100 | ALEKHYA       |       3623 |
| 101 | REVATHI       |       1291 |
| 103 | VIVEK         |       3426 |
| 100 | ALEKHYA       |       3456 |
+-----+---------------+------------+

Full Join Query

Let us try to 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 are trying to display the records of Customer IDs, Customer names, Orders made on specific dates and names of the employees that sold them.

Output

The resultant table is obtained as follows −

+----+----------+---------------------+---------------+
| ID | NAME     | DATE                | EMPLOYEE_NAME |
+----+----------+---------------------+---------------+
|  1 | Ramesh   | NULL                | NULL          |
|  2 | Khilan   | 2009-11-20 00:00:00 | REVATHI       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | SARIKA        |
|  4 | Chaitali | 2008-05-20 00:00:00 | VIVEK         |
|  5 | Hardik   | NULL                | NULL          |
|  6 | Komal    | NULL                | NULL          |
|  7 | Muffy    | NULL                | NULL          |
+----+----------+---------------------+---------------+

Full Join with WHERE Clause

With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins.

Syntax

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

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

Consider the previous two tables Customers and Orders; and try to 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 −

+----+----------+---------------------+---------+
| ID | NAME     | DATE                | AMOUNT  |
+----+----------+---------------------+---------+
|  3 | Kaushik  | 2009-10-08 00:00:00 | 3000.00 |
|  4 | Chaitali | 2008-05-20 00:00:00 | 2060.00 |
+----+----------+---------------------+---------+

Conclusion

As we can observe from the examples given in this chapter, the Full Join statement produces the same result as the Left Join if the first table acts like a super set of the second table; and it replicates the result of Right Join if the second table acts similar to the super set of the first table.