Text copied!
SQL full join
FULL JOIN (or FULL OUTER JOIN) returns all the rows from both tables, with null values for the columns that do not have a match in the other table.
The syntax of the SQL full join generally looks like this :
SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
• 'table1' and 'table2' refer to the two tables being joined together.
• 'column_name(s)' refer to the selected columns from the tables being joined together.
• 'ON' clause is used to specify the conditions for joining two or more tables together.
As seen in above image, SQL full join produces a result set that includes each row from both the tables (Table-A), (Table-B).
Here's an example of how you might use the SQL full join :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Department_Id].
[b] Departments Table :
→ It contains columns [Department_Id] and [Department_Name].
1. Let's use SQL full join to retrieve the employees names and their respective department names by joining the [Employees] and [Departments] tables together based on matching values in both the tables.
2. Please run the following SQL statement :
SELECT Emp.Employee_Name, Emp.Department_Id, Dpt.Department_Name
FROM [Employees] Emp
FULL JOIN [Departments] Dpt
ON Emp.Department_Id = Dpt.Department_Id
3. Above statement selects the columns from both tables based on the matching values in the 'DepartmentId' column. It returns all the rows from both the tables as the output.
• As you have seen, Only specific selected columns ( [Employee_Name], [Department_Id] from "Employees" table and [Department_Name] from "Departments" table ) are returned.
• It returned rows where the [Department_Id] column contained the values '1', '2'. This was done because '1' and '2' were the only values shared by both tables.
• The left table ('Employees') also included '5' but with no matching 'Department_Name' value based on [Department_Id] column, resulting in a 'NULL' value for that column.
• The right table ('Departments') also included 'Sales' and 'Maintenance' department values but with no matching 'Department_Name' value based on [Department_Id] column, resulting in a 'NULL' value for the left table columns.
That's it! You have successfully retrieved data using 'FULL JOIN' statement.