Inner join

Inner join is one type of join in SQL server to retrieve data from two tables.

Inner join syntax is as below:

Select column1[, column2 …]

from Table1

     inner join Table2 on Table1.Column1 = Table2.Column4 [ and Table1.Column6 = Table2.ColumnN]

Inner join returns only those data which are matched with condition in both tables. Inner join will exclude the data from table1 and table2 which are not met the condition.

Let’s take an example:

We have two tables Employee and Projects. Employee table have 10 employee records and Project table have 5 projects information.

Out of 10 employees, 2 employees are not allocated into any project. So when you tried to execute inner join on that table then inner join will return 8 records (except the 2 employees which are not allocated into the project).

Syntax:

Select *

    from EmployeeMaster
         inner join ProjectMaster on EmployeeMaster.ProjectId = ProjectMaster.ProjectId

Employee records (which are not allocated into the project) are not met the condition. So that data will be excluded from the output.

Joins in SQL Server

SQL server supports following types of joins to retrieve the data from more than one table.

1. Inner join
2. Left outer join
3. Right outer join
4. Self-join
5. Cross join

Inner join: This join is used to retrieve the data which are matched into both tables. For example, if table1 have 10 records and table2 have 8 records and only 3 records are matched with each other using condition then only 3 records will return.

Left outer join: This join is used when need to retrieve all data from the left side table and matched data from right side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then left outer join returns 10 records.

Right outer join: This join is used when need to retrieve all data from the right side table and matched data from left side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then right outer join returns 8 records.


Self-join: When we use same table on both side of join then it is called self-join.


Cross join: This join is used to retrieve the cross joined data. Means every record in table1 will join with all the records of table2. For example, If table1 have 10 records and table2 have 8 records then this join will return 80 records.