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:
- Inner Join
- Outer Join
- 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
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
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:
Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT,
FROM Orders Ord right OUTER 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.
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 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