Joins in SQL Server

In this article we learn about joins in SQL Server.

A join is used to combined data from two or more tables in SQL.

To provide a sample, first we create two tables called Table1 and Table2.

Create table table1
(
id int,
Name varchar(30)
)

Create table table2
(
id int,
Name varchar(30),
data varchar(100)
)

Insert some data into the tables as in the following:

insert into table1 values(1,’test1′)
insert into table1 values(2,’test2′)
insert into table1 values(3,’test3′)

insert into table2 values(1,’test1′,null)
insert into table2 values(3,’test3′,”)
insert into table2 values(4,’test4′,’some text’)
insert into table2 values(5,’test5′,’other text’)

Joins

The following are the various types of joins in SQL Server.

Cross Join

A cross join that does not have a where clause. It produces the cartesian product of the tables involved in the join.

The output rows of a cross join = number of rows in first_table * number of rows in second_table

Syntax:

select * from table1,table2

Inner Join

An inner join selects all rows from both tables that have a match in both joined tables.

Syntax:

select table1.id,table2.name,table2.data from table1 inner join table2 on table1.id=table2.id

Output

inner join output

Left Outer Join

In this join are all the rows in the first table and the matching rows of the second table. An unmatched row in the second table appears as null in the result.

Syntax:

select table1.id,table2.name,table2.data from table1 left outer join table2 on table1.id=table2.id

Output

 

Right Outer Join

In this join all the rows in the second table and the matching rows of the first table. An unmatched row in the first table appears as null in the result.

Syntax:

 select table1.id,table2.name,table2.data from table1 right outer join table2 on table1.id=table2.id

Output

right outer join

Full Outer Join

In this join all the rows are the result of whether or not they are matched.

Syntax:

 select table1.id,table2.name,table2.data from table1 full outer join table2 on table1.id=table2.id

Output

full outer join

Self Join

In this join one table is joined to itself. We need to create another table for explaining the self join.

create table emp
(
id int identity(1,1),
name varchar(30),
managerid int
)

insert into emp values(‘pramod’,0)
insert into emp values(‘prem’,1)
insert into emp values(‘sameer’,1)
insert into emp values(‘ravi’,2)
insert into emp values(‘rahul’,3)

Syntax: select distinct e1.id, e1.name from emp e1, emp e2 where e1.id=e2.managerid

Output

self join

I hope this article is helpful for you..

Thanks  🙂

Tags: , , , , ,

  • rahul

    nice article