FULL JOIN IN SQL

Introduction

The SQL FULL JOIN is a type of join operation that combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records from both tables being joined, matching records from both tables where available, and filling in NULL values for missing matches.

Syntax

SELECT column_list
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

Example of FULL JOIN IN SQL

Consider two tables: departments and employees.

Table: departments

Table: employees

Example 1: Basic FULL JOIN

SELECT departments.department_name, employees.employee_name
FROM departments
FULL JOIN employees ON departments.department_id = employees.department_id;

Output:

Example 2: FULL JOIN with Alias

SELECT d.department_name, e.employee_name
FROM departments AS d
FULL JOIN employees AS e ON d.department_id = e.department_id;

Output:

Example 3: FULL JOIN with WHERE clause

SELECT departments.department_name, employees.employee_name
FROM departments
FULL JOIN employees ON departments.department_id = employees.department_id
WHERE departments.department_name IS NULL OR employees.employee_name IS NULL;

Output:

Example 4: FULL JOIN with Aggregate Function

SELECT departments.department_name, COUNT(employees.employee_id) AS employee_count
FROM departments
FULL JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_name;

Output:

Conclusion

In conclusion, the SQL FULL JOIN is a flexible join operation that combines the outcomes of each LEFT JOIN and RIGHT JOIN, making sure that every one statistics from each tables being joined are blanketed in the result set. It returns matching data from both tables wherein to be had and fills in NULL values for lacking suits.

The FULL JOIN operation is specially useful when you need to retrieve all data from each tables, irrespective of whether there are matches inside the other table. It permits for complete evaluation of facts, including situations wherein there can be unrivaled records in either or each tables.

Using FULL JOIN, database builders and analysts can gain a entire know-how of the relationships between datasets and derive insights from the mixed facts. It allows the identification of lacking or incomplete records and helps selection-making based on a complete view of the statistics.

It’s essential to note that FULL JOIN might also bring about larger end result sets as compared to different sorts of joins, in particular if there are numerous unrivaled information in both desk.

Overall, the SQL FULL JOIN is a powerful device in the database developer’s toolkit, imparting flexibility and completeness in records retrieval and analysis responsibilities. Its ability to encompass all facts from each tables makes it a treasured asset in numerous records-related situations.

Frequently Asked Questions