The FULL JOIN in SQL combines the results out of both right and left outer joins. Here, the joined table will consist of all records from both tables, and then it will fill in NULLs for all the missing matches on either of the sides.
In this article, we will take a look at the concept of Full Join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.
Table of Contents
What is Full Join in SQL?
The FULL OUTER JOIN in SQL combines the result that we get out of both right and left outer joins and then returns all (unmatched or matched) rows from the tables that are there on both sides of the given join clause.
Syntax
The basic syntax used in the Full Join is here:
SELECT table_1.column_1, table_2.column_2…
FROM table_1
FULL JOIN table_2
ON table_1.common_field = table_2.common_field;
Now we will take a look at the example given below:
Example
We will consider now the two tables given below:
Table 1 − The CUSTOMERS Table is given as follows:
| ID | NAMETAG | ADDRESS | AGE | SALARY |
| 1 | Riri | Ahmedabad | 32 | 2000.00 |
| 2 | Kiran | Delhi | 25 | 1500.00 |
| 3 | Kaeley | Kota | 23 | 2000.00 |
| 4 | Hilary | Mumbai | 25 | 6500.00 |
| 5 | Chilly | Bhopal | 27 | 8500.00 |
| 6 | Kate | MP | 22 | 4500.00 |
| 7 | Barry | Indore | 24 | 10000.00 |
Table 2 − The ORDERS Table is given as follows:
| OID | DATE | CUSTOMER_ID | TOTAL |
| 102 | 2019-10-18 00:00:00 | 3 | 3000 |
| 100 | 2019-10-18 00:00:00 | 3 | 1500 |
| 101 | 2019-11-10 00:00:00 | 2 | 1560 |
| 103 | 2018-05-10 00:00:00 | 4 | 2060 |
Now, we can join the two tables given above as follows with the help of the LEFT JOIN:
SQL> SELECT ID, NAMETAG, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
As a result, it would produce the result as follows:
| ID | NAMETAG | AMOUNT | DATE |
| 1 | Riri | NULL | NULL |
| 2 | Kiran | 1560 | 2019-11-10 00:00:00 |
| 3 | Kaeley | 3000 | 2019-10-18 00:00:00 |
| 3 | Kaeley | 1500 | 2019-10-18 00:00:00 |
| 4 | Hilary | 2060 | 2018-05-10 00:00:00 |
| 5 | Chilly | NULL | NULL |
| 6 | Kate | NULL | NULL |
| 7 | Barry | NULL | NULL |
| 3 | Kaeley | 3000 | 2019-10-18 00:00:00 |
| 3 | Kaeley | 1500 | 2019-10-18 00:00:00 |
| 2 | Kiran | 1560 | 2019-11-10 00:00:00 |
| 4 | Hilary | 2060 | 2018-05-10 00:00:00 |
FAQs
What is a full join in SQL?
The FULL JOIN in SQL combines the results out of both right and left outer joins. Here, the joined table will consist of all records from both tables, and then it will fill in NULLs for all the missing matches on either of the sides.
Why do we use Full join in SQL?
Both the Right Join and Left Join return rows (that are unmatched) from one of the tables. On the other hand, a Full Join would return rows that are unmatched from both tables. We generally use it in conjunction with aggregations so as to understand what amount of overlap is present between two tables.
Is JOIN the same as INNER JOIN in SQL?
The INNER JOIN in SQL is the same as the JOIN clause. It combines rows from multiple tables. Here, the SQL INNER JOIN joins two given tables matching certain criteria with the help of a comparison operator.
What is SQL default join?
SQL inner join is the default SQL join we get when we happen to use the join keyword by itself. Now, the result of a typical SQL inner join would include rows from both the available tables wherever the join conditions are actually met.
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments