Understanding SQL Full Join

SQL Full Join

The SQL full join is the result of 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 is used to combine the result of both left and right outer join and returns all rows (don’t care its matched or unmatched) from the both participating tables.

Example:

SELECT *

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name;

Note: Here table1 and table2 are the names of the tables participating in joining and column_name is the column of the participating tables.

Let us take two tables to demonstrate full outer join:

Because this is a full outer join all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.

Course Video

NOTE: Practice below practice questions on MS SQL SERVER, it will not execute on site editor.

1. Write a SQL query to get productCategoryId, CategoryNamw and Product SubcategoryId, name from Production.ProductSubcategory and Production.ProductCategory.

NOTE: OUTPUT on SQL Server Count: 37 Rows

2. Write a SQL query to get all employeeID, Job title, firstName, LastName and startDate from Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory tables.