Join in SQL Server: Part 1

Join:

Join clause use to combine the data from one and more than one table.. Suppose there is a company in this company there is more than 2 departments and management people of this company would like to display the data from both table at a time. In this case back end developer we will be create one join. For better understanding we will create two tables as EmployeeDetails and Department in database like.

  1. create table Empdetails  
  2.     (  
  3.         eid int Primary key,ename varchar(20),Designation varchar(20),Salary int  
  4.     )  
  5. create table Department  
  6.     (  
  7.         Did int,DName varchar(20),eid int foreign key references Empdetails(eid)  
  8.     )  
Inserting values in Database
  1. Insert into Empdetails values  
  2.     (  
  3.         1,’Sandeep’,’S/w Dev.’,25000  
  4.     )  
  5. Insert into Department values  
  6.     (  
  7.         101,’Development’,1  
  8.     )  
EmployeeDetails
                        Figure: EmployeeDetails table

Department 
                       Figure: Department table

Now back end developer will be create one joins by using following syntax.

Syntax:
  1. Select columns name  
  2. From Left side table name  
  3. Joins type Right side table name  
  4. On condition  
Sample Example :
  1. select ename,Designation,did,dname  
  2. from EmpDetails   
  3. inner join Department   
  4. on EmpDetails.eid=department.eid  
Above view Query will display the data as,

JoinsData   
                                    Figure: JoinsData

Point to Remember: Joins clause use to combined the data from one and more than one tables. There are types of Joins and these are: 
  1. Inner Join
  2. Left outer join
  3. Right outer join
  4. Cross join

Syntax for join

  1. Select column name  
  2. From table name//Left side table name  
  3. Join type table name//Right side table name  
  4. On condition  
Sample Example for join
  1. select ename,Designation,did,dname  
  2. from EmpDetails //EmpDetails is left side table   
  3. inner join Department // Department is Right side table  
  4. on EmpDetails.eid=department.eid