- 4 years ago
- Zaid Bin Khalid
- 2411 Views
-
4
In this session, you will learn how to retrieve data from two tables by using SQL full join.
Full Join Operations.
By using FULL Join statement in SQL it will returns all the rows from the joined tables, whether they are a match or not. A Full join is a type of outer join that’s why it is also referred to as full outer join.
In other words, you can say The SQL full join is the result of the combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.
SQL full outer join and SQL join are the same. Generally, it is known as SQL FULL JOIN.
Representation of Full Join with Venn Diagram
The following Venn diagram shows how full join works.
Proposed Tables: Using Full Joins
To understand this clearly, let’s look at the following employees and departments tables.
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
Let’s say you just want to retrieve the names of all the employees and the names of available departments, regardless of whether they have corresponding rows in the other table, in that case, you can use a full join as demonstrated below.
Full Joins – Example.
The following statement retrieves all the departments as well as the details of all the employees by joining the employees and departments tables together using the common dept_id field.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
Alternate of Full Joins.
There are some databases, such as Oracle, MySQL do not support full joins. In such types of cases, you will use the UNION ALL operator to combine the LEFT JOIN and RIGHT JOIN as follows.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id
UNION ALL
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
Example Output – Full Joins.
After executing the above command, you’ll get the output something like this.
+--------+--------------+------------+------------------+
| emp_id | emp_name | hire_date | dept_name |
+--------+--------------+------------+------------------+
| NULL | NULL | NULL | Customer Service |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 1 | Ethan Hunt | 2001-05-01 | Human Resources |
| 5 | Martin Blank | 2008-06-24 | NULL |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 4 | Rick Deckard | 2007-01-03 | Finance |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 3 | Sarah Connor | 2005-10-18 | Sales |
| 2 | Tony Montana | 2002-07-15 | Administration |
| 2 | Tony Montana | 2002-07-15 | Administration |
+--------+--------------+------------+------------------+
As you already know in the join query, the left table is the one that will show the leftmost in the JOIN clause. And the right table is the one that shows rightmost, but in FULL JOIN it will show both.
Note
When performing outer joins, the Database Management System cannot match any row, it places NULL in the Columns to indicate data do not exist.
- 4 years ago
- Zaid Bin Khalid
- 2411 Views
-
4