Joins in SQL Server 2012

JOINS

Joins are used to display/retrieve values from one or more tables at a time.It is used to join the columns of different tables.

Types of Joins:

Joins are of below types

1.Inner Join

       1.Equi Join 

       2.Non Equi Join 

       3.Self Join 

2.Outer Join

     1.Left outer Join

     2.Right outer Join

     3.Full outer Join

3.cross join or Cartesian join 

1.Inner JOINS

It is used to join the tables containing matched or related records.

Ex: Equi Join,Non Equi Join,self join

1.Equi Join:

If two or more tables tables are combined using equality condition we call it as Equi Join.

Write a query to get the all the employee details along with their dept information

select E.enum,E.ename,e.Hiredate,e.Job,e.sal,e.comm,e.dno,
 e.Mng,d.deptno,d.dname,d.loc
 from Employee10 E,department10 d
where e.dno=d.deptno

The above query will retrieves the records from two tables but the syntax of joining the table is not in ANSI standard.to write joins in ANSI standard we need to follow two rules

1.We need to replace the "WHERE" with "ON"

2.We need to separate the tables in the from list with join keywords(Inner/self/left outer/right outer/full outer/cross Join)

So we can reframe the above select query as below

select E.enum,E.ename,e.Hiredate,e.Job,e.sal,e.comm,e.dno,
 e.Mng,d.deptno,d.dname,d.loc
 from Employee10 E INNER JOIN department10 d
ON e.dno=d.deptno

Both the queries results the same result.

 * we will get only the matching records
 * it will check for equality

2.Non Equi Join 

If we join two tables other thans equlity condition we called this kind of join as NON Equi Join.

 create table salgrades
 (
 sgrade int,
 minsal int,
 maxsal int)

 insert into salgrades(sgrade,minsal,maxsal)
 values(1,5000,8000),(2,8001,12000),(3,12001,16000),
 (4,16001,18000),(5,18001,22000),(6,22001,28001)

Write a Query to get all the employees with their salary grade info

Normal Query

select e.enum,e.ename,e.sal,s.sgrade,s.minsal,s.maxsal
 from Employ1000 e,salgrades s
 where e.sal between s.minsal and s.maxsal

ANSI Standard

 select e.enum,e.ename,e.sal,s.sgrade,s.minsal,s.maxsal
 from Employ1000 e JOIN salgrades s
 ON e.sal between s.minsal and s.maxsal

For Equi and Non Equi joins we need minimum of two tables.

3.SELF Join

A table can be joined by itself.

I want to get the emp details who have subordinates under them

Normal Query
 select distinct e.enum,e.ename
 from Employee10 e,Employee10 ee
 where e.enum=ee.Mng

ANSI Standard
 select distinct e.enum,e.ename
 from Employee10 e JOIN Employee10 ee
 ON e.enum=ee.Mng

 I want to get the emp details who are not having subordinates under them

select distinct e.enum,e.ename
 from Employee10 e JOIN Employee10 ee
 ON ee.enum !=e.Mng

**Join/inner Join we can use any one both are same.

2.CARTESIAN/CROSS JOIN

If two or more tables are combined with each other without any condition we call it as CROSS\CARTESIAN  join.

Here each row of first table is joined with each row of second table.so we will get m*n rows as a result set.

NON ANSI Standard

select * from employee10,department10

ANSI Standard

 select * from employee10 cross join department10

3.OUTER JOINS

It is an extension for the equi join that is in an equi join condition we are getting the matching records where as in outer joins we will get matching records with non matched records from left side,non matched records from right side or ,non matched records from left and right side.

We have three types of records in outer joins they are

1.Left outer Join

In left outer join we will get matching records from all the tables and non matched records from LEFT side table.

ex:

select e.enum,e.ename,e.sal,e.dno,d.deptno,d.dname,d.loc
 from department10 d LEFT OUTER  JOIN Employ1000 e

 ON e.dno=d.deptno

2.Right outer Join

In Right outer join we will get matching records from all the tables and non matched records from RIGHT side table.

ex:
 select e.enum,e.ename,e.sal,e.dno,d.deptno,d.dname,d.loc
 from department10 d RIGHT OUTER JOIN Employ1000 e
ON e.dno=d.deptno

3.Full outer Join

In Full outer join we will get matching records from all the tables and non matched records from LEFT and  RIGHT side tables.

ex:

 select e.enum,e.ename,e.sal,e.dno,d.deptno,d.dname,d.loc
 from department10 d FULL OUTER JOIN Employ1000 e

 ON e.dno=d.deptno

Let me know if you have any doubts on this article.

Thanks
Srinivas