Home » SQL Basics » Retrieving Data » Using JOIN

Using JOIN

Using JOIN: There are different types of Joins that can be used as below:

•Inner Join: Return rows when there is at least one match in both tables
•Left Join: Return all rows from the left table, even if there are no matches in the right table
•Right Join: Return all rows from the right table, even if there are no matches in the left table
•Full Join: Return rows when there is a match in one of the tables

Inner Join:

Syntax: SELECT “table_alias1”.”column_name1″ as “column_alias1″ , “table_alias2”.”column_name2″ as “Column_Alias2,….”table_alias1”.”column_nameZ” as “Column Alias3″
FROM “table_name” “table_alias1”
INNER JOIN “table_name” “table_alias2”
on “table_alias1”.”column_name1″=“table_alias2”.”column_name2″

Example: SELECT A.Product_name as Product, A.Sales as “Store Sales”, B.Sales as “Internet Sales”
FROM Store_Sales A
Inner Join Internet_Sales B on A.Product_name=B.Product_name

Outer Join: There are three types of Outer Joins which are
•Left Join: Return all rows from the left table, even if there are no matches in the right table
•Right Join: Return all rows from the right table, even if there are no matches in the left table
•Full Join: Return rows when there is a match in one of the tables

The Full outer Join shows all results from both tables which are Joined.
The Left outer Join shows all results from left table in the Join Statement and only corresponding results from the right side table.
The Right outer Join shows all results from Right table in the Join Statement and only corresponding results from the left side table.

Syntax for Left Outer Join:
Syntax: SELECT “table_alias1”.”column_name1″ as “column_alias1″ , “table_alias2”.”column_name2″ as “Column_Alias2,….”table_alias1”.”column_nameZ” as “Column Alias3″
FROM “table_name” “table_alias1”
Left JOIN “table_name” “table_alias2”
on “table_alias1”.”column_name1″=“table_alias2”.”column_name2″

Example: SELECT A.Product_name as Product, A.Sales as “Store Sales”, B.Sales as “Internet Sales”
FROM Store_Sales A
Left Join Internet_Sales B on A.Product_name=B.Product_name

Syntax for Right Outer Join:
Syntax: SELECT “table_alias1”.”column_name1″ as “column_alias1″ , “table_alias2”.”column_name2″ as “Column_Alias2,….”table_alias1”.”column_nameZ” as “Column Alias3″
FROM “table_name” “table_alias1”
Right JOIN “table_name” “table_alias2”
on “table_alias1”.”column_name1″=“table_alias2”.”column_name2″

Example: SELECT A.Product_name as Product, A.Sales as “Store Sales”, B.Sales as “Internet Sales”
FROM Store_Sales A
Right Join Internet_Sales B on A.Product_name=B.Product_name

Syntax for Full Outer Join:
Syntax: SELECT “table_alias1”.”column_name1″ as “column_alias1″ , “table_alias2”.”column_name2″ as “Column_Alias2,….”table_alias1”.”column_nameZ” as “Column Alias3″
FROM “table_name” “table_alias1”
Full JOIN “table_name” “table_alias2”
on “table_alias1”.”column_name1″=“table_alias2”.”column_name2″

Example: SELECT A.Product_name as Product, A.Sales as “Store Sales”, B.Sales as “Internet Sales”
FROM Store_Sales A
Full Join Internet_Sales B on A.Product_name=B.Product_name

Leave a Reply

Your email address will not be published. Required fields are marked *