Types of Join In SQL Server

We use SQL JOIN clause to combine rows from two or more tables of a database, based on a common field between them.

Join means combining fields from two or more tables by having common values in each table of a relational database. SQL INNER JOIN is the most common and a simple join used mostly.

Types of Join in SQL Server

Inner Join

Returns all rows when there is at least one match in both tables

Example 1: I have 2 tables Students and Employees.

   



Example 2: Here I’m using where  lause



Outer Join

It is categorized into 3 types:

i. Left Join: Return all rows from the left table, and the matched rows from the right table. The result will be NULL in the right side when there is no match.

Syntax:

SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2
              ON table1.column_name=table2.column_name;

Example 1: When there will be no match to right table the output will have NULL values. Here's the image,



Example 2: In the following example Left Table is Persons and right table is Trainee1, Now all the left table values are combining to the matched values of right side table, when left table not matched to right side table values the result is NULL.



Right Join

Return all rows from the right table, and the matched rows from the left table.

Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2
                  ON table1.column_name=table2.column_name;

Example: We have left table: Persons and Right table: Trainee1, Now in right outer join, the right table columns will combine to the matched values of left table.



Full Join

Return all rows from both the tables, when there is a match in one of the tables it acts as a combination of both left and right Join.

    Syntax: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2
                   ON table1.column_name=table2.column_name;

Example

         
   
Self-Join useful

A self-join is useful for joining a table to itself, Self Join means that each row of the table is combined with itself and with every other row of the table that we have.

Syntax:

SELECT a.column_name, b.column_name... FROM table1 a, table1 b
              WHERE a.common_filed = b.common_field;

Example



Cross Join

It returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. 

Example: I have 2 tables, Students and employees. The Cross Join result will be 45(5*9).