SQL server is a relational database management system, that means we may have two or more related tables which have some relations which each other with reference to the relationship I mean to say 1 to many relationships, 1-1, many to one relationship which is basically referenced using Foreign key constraints.

In order to understand the benefits of using joins let’s take an example of two tables Orders, Products.

So as per business requirement, we want to show the user order details information i.e. OrderId, StoreId from where the order has to be dispatched, Total Gross amount and total
Discount he has received, with the product description. So in order to show the report we need to join these tables to be shown to the user. So in order to achieve the same as we discussed, we need to have some relation here we have a foreign key relation on ProductId. So now in order achieve the output I have to join these two tables we will join ProductId as a relation as shown below:

In SQL we have three kinds of Joins:

1. Inner Join
2. Outer Join
3. Cross Join

Outer Join is then subdivided into three different outer Joins:

• Left Outer Join/Left Join
• Right Outer Join/Right Join
• Full Join or Full Outer Join

Inner Join:

An inner join is a kind of intersection between both the tables which means the condition on which join is performed should be present in both the tables i.e. matching rows only. To specify the join condition we use On clause. We can use Join or Inner Join both means the same.

SELECT Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT, PROD.Description
FROM Orders Ord
INNER JOIN
Product PROD
ON Ord.PRODUCTID=PROD.ID;

Output:


Left Outer Join or Left Join

So now what if we want all the orders whether they have product id associated or not. So in order to do the same, we will use Left Join. So in order to achieve the same, we will make use of Left Join or Left Outer Join. This will return all the Orders whether they have ProductId associated with or not, If not display ProductNotSpecified

 SELECT Ord.CUSTOMERID,Ord.GROSSTOTAL,Ord.TOTALORDERDISCOUNT,
 CASE
 WHEN PROD.Description IS NULL THEN
 'ProductNotSpecified'
 ELSE PROD.Description END
 FROM Orders Ord 
 LEFT
 OUTER
 JOIN
 Product PROD
 ON Ord.PRODUCTID=PROD.ID;
 

So left join returns all the matching row from both table and non-matching rows from the left table.

Right Outer Join/ Full Join

So now we want to reverse the scenarios and want all the rows from right table and only matching rows from the Order Table, in order to do the same we need to apply Right outer Join. i.e. now we will display all the product description and display matching or not matching orders for the respective Products as shown below:

SELECT
Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT,
PROD.Description
FROM Orders Ord right OUTER JOIN
Product PROD
ON
Ord.PRODUCTID=PROD.ID;

Cross Join

Cross join Cartesian product of two table, that means each record right table will be mapped with all records of the left table one by one. Cross join doesn’t require On clause.

Orders table:


Product table:


So in Orders table, we have 6 records and in Product table, we have 3 records so each Order records will be mapped to Product records i.e.

Number of Orders Records * Number of Product Records=18

SELECT
 Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT,
 PROD.Description
 FROM Orders Ord
 CROSS JOIN
 Product  PROD
 ORDER BY ORD.ID ASC
 

Full Join

Full join returns all the rows from both the table, including matching or not matching rows from both the tables

SELECT Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT, PROD.Description
 FROM Orders Ord 
 FULL  JOIN 
Product PROD 
ON ORD.PRODUCTID=PROD.ID ORDER
BY ORD.ID ASC
 

Happy Learning.