Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Inner Join

Original Tables Data:

Products Table:


Categories Table:


Inner Join:

This join returns common or matching records/rows from both the database tables (left table and right table).This s the most used join in the SQL. In the join condition, you can also use other operators like <,>,<>.


Syntax:

SELECT
column_name(s)
FROM Table1 (Left Side Table)
INNER JOIN Table2 (Right Side Table)
ON Table1.column_name=Table2.column_name;


Example:

SELECT
ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
INNER JOIN Categories C ON P.CategoryID=C.CategoryID


Inner join t-sql query will return result 



Right outer Join or Right join

Products Table



Categories Table




Right outer Join or Right join:

 

The RIGHT JOIN keyword returns all rows from the right table (Categories), with the matching rows in the left table (Product). The result is NULL in the left side when there is no match. 


Syntax:

 

SELECT
column_name(s)
FROM Table1 (Left Side Table)
RIGHT JOIN Table2 (Right Side Table)
ON table1.column_name=table2.column_name
 
or:
 
SELECT column_name(s)
FROM Table1 (Left Side Table)
RIGHT OUTER JOIN Table2 (Right Side Table)
ON table1.column_name=table2.column_name

 

Example:

SELECT ProductName AS [Product Name]

,UnitPrice AS [Price]

,CategoryName AS [Product Category],ImagePath AS [Image]

 FROM Products P

RIGHT OUTER JOIN Categories C ON P.CategoryID=C.CategoryID 

Right join t-sql query will return result 


Left outer Join or Left join

Products Table



Categories Table



Left outer Join or Left join:

 The LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table (Categories). The result is NULL in the right side when there is no match.



Syntax:

SELECT
column_name(s)
FROM Table1 (Left Side Table)
LEFT JOIN Table2 (Right Side Table)
ON table1.column_name=table2.column_name
or:
SELECT column_name(s)
FROM Table1 (Left Side Table)
LEFT OUTER JOIN Table2 (Right Side Table)
ON Table1.column_name=Table2.column_name

Example:

SELECT
ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
LEFT OUTER JOIN Categories C ON P.CategoryID=C.CategoryID


Left outer query will return below result