Home / Database / Inner Join in SQL Server
inner

Inner Join in SQL Server

Joins are used to combine the data of two or more tables using single select statement.

Joins are classified into Inner Joins, Outer Join & Cross Join.

The Inner & Outer join again classified into several subtypes as Equi Join, Non-Equi Join, Natural join & Self join.

Syntax :

SELECT */(COLUMN LIST) FROM <TABLENAME1>

INNER/OUTER/CROSS JOIN

<TABLENAME2> [ON JOIN CONDITION]

Sample Data for understanding:

Table 1: Student

1_1 Inner Join in SQL Server Database SQL Server

Table 2: Department

1-2 Inner Join in SQL Server Database SQL Server

Table 3: Marks

3 Inner Join in SQL Server Database SQL Server

Table 4: Employee

4 Inner Join in SQL Server Database SQL Server

INNER JOIN

Inner join returns the rows which satisfies the join condition.

Definition : Join which satisfies the join condition is Inner Join.

Equi Join: The join which uses “EQUAL TO” operator in join condition is a Equi Join.

E.g. Select the student & related department.

SELECT * FROM STUDENT S

INNER JOIN

DEPARMENT D

ON S.DepartmentNumber= D.DepartmentNumber

Result will be :

5 Inner Join in SQL Server Database SQL Server

Natural Join: The join is called as natural if it satisfies below conditions.

  1. Join must be Equi Join.
  2. All common columns of tables must be in join condition.
  • Result should contain only one set of common column i.e. distinct column.

E.g. Select the student & related department without distinct values.

SELECT S.*, D.Department  FROM STUDENT S

INNER JOIN

DEPARMENT D

ON S.DepartmentNumber= D.DepartmentNumber

Result will be :

6 Inner Join in SQL Server Database SQL Server

Non-Equi Join: The join which does not use “Equal To” operator in join condition is Non-Equi join.

E.g. Select the student who is having marks between 50 & 80.

SELECT S.*, M.MARKS FROM STUDENT S

INNER JOIN

MARKS M

ON M.MARKS BETWEEN 50 AND 80

Result will be :

7 Inner Join in SQL Server Database SQL Server

Self-Join: The join which joins with itself is called as Self-Join.

E.g. Display the employee details with manager name.

SELECT emp.ID,emp.[Employee Name],mgr.ID,mgr.[Employee Name] FROM EMPLOYEE emp

INNER JOIN EMPLOYEE mgr

ON emp.ID = mgr.MANAGER

Result will be :

9 Inner Join in SQL Server Database SQL Server

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Delete Command

Difference between Delete and Truncate command

Difference between Delete and Truncate command Delete Command 1. It allows to delete all rows as ...

One comment

Leave a Reply