Full Join (Full Outer Join) In Sql
In SQL database management systems, FULL JOIN is a database table connectivity join task, also known as FULL OUTER JOIN. Full join is a popular database table join method that displays all row values from two different table data to the database user. In a full join, it displays rows with matching values in the matched table columns and rows without matches in either of the two tables. When there is no proper value match in both database tables, it displays NULL values for the columns of the unmatched database table in the final result.
In other words, a FULL OUTER JOIN combines the behaviour of both LEFT JOIN and RIGHT JOIN concepts into a single table. This method ensures that all row records from both the left and right database tables are displayed in the result set, and NULL values are displayed in the table columns where no proper match is found.
Syntax of FULL JOIN in SQL database.
SELECT columns
FROM tableX
FULL JOIN tableY
ON tableX.column_name = tableY.column_name;
Elements of FULL JOIN.
- tableX – This is the first database table connected in the full join method, known as the left table.
- tableY – This is the second table connected in the full join method, known as the right table.
- column_name – This is the table column used to match rows between the two left and right database tables in the full join process.
How FULL JOIN works in SQL database.
In SQL database tables, FULL JOIN adds and displays all table rows from both the first and second tables.
Here, rows are displayed from both database tables that have matching values from the two different database tables.
Here, rows from the left database table are included even if there is no match in the right table; in this case, it displays NULL values for the columns of the right database table. Similarly, if there are rows in the right database table that do not have a matching value in the left database table, it remains with NULL for the columns of the left table.
Example of a basic FULL JOIN in an SQL database.
Number of database tables for FULL JOIN.
- employee – This table contains all the details of the employee.
- employee_id, emp_name, department_id
- department – This table contains all the details of the department.
- department_id, department_name
Sample data for a basic FULL JOIN.
employee database table.
employee_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
307 Anil Jain NULL
department database table.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Employee and Department database query.
SELECT employee.emp_name, department.department_name
FROM employee
FULL JOIN department
ON employee.department_id = department.department_id;
Explanation of the Employee and Department database query.
- Here, this query extracts and displays the employee name and department name as output.
- Here, the FULL JOIN activity in the database ensures that all employees and departments are included in the result, where there is no table match, the table column information is displayed with a NULL value.
Result of an Employee and Department database query.
emp_name department_name
Siddhi deora Marketing
Harry deora Development
Bhavshi deora Design
Anil Jain NULL
NULL sales
How FULL JOIN works in an SQL database.
In this query, the departments of Siddhi deora, Harry deora, and Bhavshi deora match, therefore both are included in the result table. Anil Jain employee does not have a department; the department_id has a NULL value, therefore the employee’s department_name displays a NULL value. There is no employee value in Sales here, therefore the value for that department name is NULL.
FULL JOIN example when there is no matching data in either table.
Here, modify the table data in the full join so that there is no matching department for any employee, and no employee in any department.
Updated Employee Table Information.
Employee database table query.
employee_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
Department database table query.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Employee and department database table query.
SELECT employe.emp_name, department.department_name
FROM employe
FULL JOIN department
ON employe.department_id = department.department_id;
Result of the employee and department database table.
emp_name department_name
Siddhi Deora NULL
Harry Deora NULL
NULL Marketing
Explanation of the employee and department database table.
- Siddhi Deora has a department here, but it is not found in the department table, which is why the department name is displayed as NULL.
- Similarly, Harry Deora also does not have a matching department in the department table, which is why his department_name is also displayed as NULL.
- Similarly, there is no employee in the Marketing department, which is why the emp_name column has a NULL value for that department.
How FULL JOIN works in SQL database management.
Here, in the full join table process, all table records from both the left and right database tables are included, and for those database table columns where no match is found, NULL values are returned.
Example of FULL JOIN with aliases in an SQL database.
Here, using alias names in the employee and department tables can help in creating database table queries easily and making them more readable.
Database table query with aliases.
SELECT e.emp_name, d.department_name
FROM employee AS e
FULL JOIN department AS d
ON e.department_id = d.department_id;
Explanation of database table query with aliases.
- Here, ‘e’ is an alias created for the employee table name, and ‘d’ is an alias defined for the department table.
- This method makes the database table results easier for the user, making the database query more detailed and readable.
Example of FULL JOIN with multiple tables.
Here, the database user can use the FULL JOIN method to group data from two or more tables. So let’s create and add a third table, ‘pay’, which stores and displays the employee’s salary information.
FULL JOIN with multiple tables description.
- employees – Here, the employee table contains all the details of the employees. employee_id, emp_name, department_id, etc.
- department – This table contains all the details of the departments. department_id, department_name, etc.
- Pay – This table contains all the details of employee salaries. For example, employee_id, salary, etc.
Sample Employee Database Table Data.
Employee database table.
employee_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
Pay database table.
employee_id salary
10 34909
30 65000
Employee, Department, Pay database table query.
SELECT e.emp_name, d.department_name, p.pay
FROM employee AS e
FULL JOIN department AS d
ON e.department_id = d.department_id
FULL JOIN pay AS p
ON e.employee_id = p.employee_id;
Result of the Employee, Department, Pay database table query.
emp_name department_name salary
Siddhi deora Marketing 34909
Harry deora Development NULL
NULL Sales NULL
Explanation of the Employee, Department, Pay database table query.
- Here, the employee Siddhi deora has both a department and a salary value, so both are displayed in the result.
- The employee Harry deora has a department (Development), but no salary, so a NULL value is returned in the salary column.
- Here, there is no employee in the Sales department, so the employee name is a NULL value.
Main Differences Between FULL JOIN, LEFT JOIN, and RIGHT JOIN In sql.
| Join Aspect | LEFT JOIN/LEFT OUTER JOIN features | RIGHT JOIN/RIGHT OUTER JOIN features | FULL JOIN/FULL OUTER JOIN features |
| What Join returns | Left join enables database user to display All rows from the left first table, and matching rows with the right second table | Right join enables database user to display All table rows from the right second table, and match table rows with the left database table. | Full join enables database user to display All table rows from both left and right tables, with NULLs for non-matching table rows columns. |
| Not-matching table rows | Left join method Excludes table rows without matches in the right database table. | Right join method Excludes table rows without matches in the left database table. | Full join Includes table rows without matches from both left and right database tables. |
| Where to Usage | Left join method Use when database user wants all table records from the left first table, even if there are no matches in the right second database table. | Right join method Use when database user wants all table records from the right second table, even if there are no matches in the left first table. | Full join method Use when database user wants all table records from both left and right database tables, even if there are no matches in both tables. |
Conclusion of the Full Join (Full Outer Join) join method.
- In SQL database management systems, a FULL JOIN or FULL OUTER JOIN is a very useful join operation for two or more database tables. It allows the database user to extract all table rows from both tables, regardless of whether there is a proper match between the two database tables. If a match is found between the two tables, the related table rows are grouped together. If there is no match between the two tables, the final result displays a NULL value in the column of the table that does not have any matching rows.
- In SQL database management systems, a FULL JOIN is used when the database user needs to include all table records from both the first and second tables in the join, even if some table records do not have matching rows in the second table.
