Joins in SQL Server

What is join in sql server

When we need to select data from more than one table then we can use joins in SQL Server. We can join two or more tables on the basis of common column between these tables.

Consider the following table which I am using in this example:-

Table 1:- Emp_Record

		
Create table Emp_Record(eid int primary key, ename varchar(100), email varchar(100))
	
		

		


		Figure 1

		

Table 2:-emp_sal

		
Create table emp_sal(eid int foreign key references emp_record(eid), salary int, ispermanent varchar(100))
	
		

		


		Figure 2

		

Types of Joins

Following are the Type of Joins:-

Inner Join

When we need to select the common data from more than one table then we will use inner join. It will select only those data which will be common in these two tables.

		


		Figure 3

		

Example of Inner Join

		
select emp_record.eid, emp_record.ename, emp_record.email, emp_sal.salary, emp_sal.ispermanent
from emp_record inner join emp_sal on emp_record.eid=emp_sal.eid

		
		

Output:-

		


		Figure 4

		
		

As you can see it is showing only those records which are common between two tables.

Inner join is the also known as Default join. We can use this join also using JOIN keywords.

For Example:-

		
select emp.eid, emp.ename, emp.email, es.salary, es.ispermanent
from emp_record emp join emp_sal es on emp.eid=es.eid

		
		

Note: - we can also use alias of tables in joins instead of table name with every column.

For Example:-

		
select emp.eid, emp.ename, emp.email, es.salary, es.ispermanent
from emp_record emp inner join emp_sal es on emp.eid=es.eid

		

Outer Join

When we need to select all data from one table including common data from Second table then we use outer join. It includes NULL value where it does not find data in second column.

Types of Outer Join

Following are the types of Outer Join:-

Left outer Join

When we need to find the all data from left table including common data from right table then we use left outer join.

		


		Figure 5

		
		

Example of Left Outer Join

		
select emp_record.eid, emp_record.ename, emp_record.email, emp_sal.salary, emp_sal.ispermanent
from emp_record left outer join emp_sal on emp_record.eid=emp_sal.eid

		
		

		


		Figure 6

		
		

When we need to select data only from left table excluding common data from right table then we will use query in following manner

		


		Figure 7

		
		

For Example

		
select emp.eid, emp.ename, emp.email
from emp_record emp left outer join emp_sal es on emp.eid=es.eid where es.salary is null

		
		

Output:-

		


		Figure 8

	
		

Right Outer Join

When we need to find the all data from right table including common data from left table then we use right outer join.

		


		Figure 9
	
		

		
select emp.eid, emp.ename, emp.email, es.salary, es.ispermanent
from emp_sal es right outer join  emp_record emp on emp.eid=es.eid

	
		

Similarly if we need to select data only from right table then we can exclude left table by implementing null in where clause as we used earlier in left outer join.

Full Outer Join

When we need to select all data from all tables whether it is common or uncommon then we use full outer join.

		


		Figure 10
	
		

For Example

		
select emp.eid, emp.ename, emp.email, es.salary, es.ispermanent
from Emp_Record emp full outer join  emp_sal es on emp.eid=es.eid

	
		

Cross Join

Cross join is used to find Cartesian product between two tables. It will simply joins each row from one table to each row in second tables. In genral language we will say it will multiply each row of first table with each row of second table. We need not to implement any key column in cross join.

For Example

		
select emp.eid, emp.ename, emp.email, es.salary, es.ispermanent
from Emp_Record emp cross join  emp_sal es

	
		

Output

		


		Figure 11
	
		

Self Join

When we need to join a table with itself then it is known as Self join. We joins same table by creating their alias. Self-join is not any keyword. We use existing joins in self-join but difference is that we use only one table.

For Example

Consider the following table in which emphead is also another employee.

		


		Figure 12
	
		

So we use empid data into emphead. Now my query is to select the data of all employee head. Then I will use self join which is as follows:-

		
select distinct e.eid, e.ename, e.email
from Emp_data e inner join Emp_data ed on e.eid=ed.emphead

	

		


		Figure 13
	
		

Email Address

For any query you can send mail at info@techaltum.com
Thanks

• ← Top in SQL
• Stored Procedure →