-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
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
