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.
NOTE: OUTPUT on SQL Server Count: 19,978 Rows
3. Write a query to join salesOrderHeader, SalesOrderDetail, SpecialOfferproduct and SpecialOffer table to retrieve two columns from each table.
NOTE: OUTPUT on SQL Server Count: 5579 Rows
OUTPUT
4. write a SQL query to get all PurchaseOrderHeader details, ShipMethods Name, ShipRate.
[Tables: Purchasing.PurchaseOrderHeader and Purchasing.ShipMethod]
NOTE: OUTPUT on SQL Server Count: 500 Rows
OUTPUT
5. write a SQL query to join PurchaseOrderDetail, PurchaseOrderHeader, ShipMethod table to retrieve two columns from each table.
NOTE: OUTPUT on SQL Server Count: 1115 Rows.
OUTPUT
NOTE: Use NORTHWIND DATABASE For Practicing these Questions.
6. Construct a SQL query to intricately extract all Product IDs, Unit Prices, Order IDs, and Order Dates from the OrderDetails and Orders tables. [OUTPUT: 2155 ROWS]
OUTPUT
7. write a query to retrieve all product IDs ,unit prices, order IDs, order dates, product names,and quantities per unit from orders, order details and products tables, while also ensuring that only orders containing products with a unit price higher than the average unit price of all products are included. [OUTPUT: 718 ROWS]
OUTPUT
8. write a query to extract all product IDs ,product names, order IDs, unit prices, quantities, category names, supplier IDs, contact titles, and cities, from the Products, OrderDetails, Categories, and Suppliers tables, with the additional stipulation that only orders containing products from suppliers located in cities starting with the letter ‘M’ are considered. [output: 215 ROWS]
OUTPUT
9. Compose a SQL query to join the Shippers table, Orders, Customers, and Employees tables, extracting two columns from each table. Apply filters that include restrict employee records based on a hire date earlier than January 1st, 1993. [OUTPUT: 838 ROWS]
OUTPUT
10. Compose a SQL query to get all Order IDs ,Quantities ,Product IDs, Product Names, Category IDs, Category Names, Company Names, and Contact Names from the OrderDetails, Products, and Categories tables. Ensure that only order details with a discount greater than zero are included, and categories labeled as ‘confections’ or ‘produce’. Additionally, arrange the output in ascending order based on Product IDs. [OUTPUT: 2161 ROWS]
OUTPUT
YouTube Reference :
1) SQL Full Join in Hindi/Urdu
2) SQL Full Join in English
SQL FULL JOIN retrieves all records from both tables, including unmatched rows from either table. NULLs are displayed in columns where no match is found.
The basic syntax is:
sql
Copy code
SELECT columns
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;
Example:
sql
Copy code
SELECT Employees.name, Departments.name
FROM Employees
FULL JOIN Departments ON Employees.dept_id = Departments.id
INNER JOIN: Returns only matching records from both tables.
FULL JOIN: Returns all records from both tables, including unmatched rows.
LEFT JOIN/RIGHT JOIN: Returns all records from one table and matching records from the other.
To find all customers and orders, including those who haven’t placed orders or orders without a customer match:
sql
Copy code
SELECT Customers.name, Orders.amount
FROM Customers
FULL JOIN Orders ON Customers.id = Orders.customer_id
WHERE Customers.region = ‘India’;
Merging datasets to identify discrepancies.
Analyzing data where not all entries are expected to have matches in both tables.
Generating comprehensive reports that include matched and unmatched records.
Use indexes on JOIN columns to improve performance.
Select only required columns to reduce query complexity.
Avoid unnecessary conditions in the WHERE clause.
Not handling NULL values correctly in subsequent operations.
Overloading queries with too many columns or complex calculations.
Assuming FULL JOIN results are always symmetrical in structure.
Yes, you can chain multiple FULL JOINs. Example:
sql
Copy code
SELECT Employees.name, Projects.name, Departments.name
FROM Employees
FULL JOIN Projects ON Employees.project_id = Projects.id
FULL JOIN Departments ON Employees.dept_id = Departments.id;
Visit the SQL FULL JOIN Tutorial for comprehensive examples and best practices.
Since FULL JOIN retrieves all records from both tables, it can be resource-intensive for large datasets. Proper indexing and query optimization are essential.
