Powered by Blogger.

Saturday, February 15, 2014

Sql Server 2008 joins tutorial




In this post we will discuss about various types of joins in SQL Server 2008. The joins are like Equi-joins, Non Equi-joins, Self-joins, Cartesian-joins, Outer joins. Also you can check out my previous posts on:

- NuGet tutorial in Asp.Net

- Data access block of Microsoft enterprise library 6.0 using stored procedure

- Constraints in SQL Server 2008

In order to retrive data from two or more tables based on logical relationship between the two tables, we require joins. Joins indicate how database should use data from one table to select the rows in another table.

There are different types of joins are there. These are like
1-Equi-joins
2-Non Equi-joins
3-Self-joins
4-Cartesian joins
5-Outer joins
 -Left Outer Joins
 -Right Outer Joins

1- Equi-joins:
It returns the specified columns from both of the tables, and returns only the rows for which an equal value in the join column.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

2- Non Equi-joins:
Here you can join values in two columns that are not equal. The same operators can be used for equijoins can be used here in case of non equijoins.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,
 S.SALGRADE,S.LOWSAL,S.HISAL
 FROM EMP E INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL

3- Self-joins:
If a table has a reflexive relationship in the database, you can join it to itself automatically, This is also known as self joins.

For Example:
SELECT DISTINCT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
FROM EMP E INNER JOIN EMP M
ON E.EMPNO=M.MGR

4- Cartesian joins:
If the cartesian join have a where clause ,its behaves as a n innerjoin.
If the cartesian join that doesnot have a where clause,it produces the cartesian product of the tables involved in the joins. The size of the cartesian product result set is the number of rows in the first tables multiplied by the no of rows in the second table. This is also known as cross join.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC FROM EMP E CROSS JOIN DEPT D

5- Outer joins:
By default, when we join multiple tables using innerjoin  what we get is the matching data from the two tables, if we want to include the data rows in the resultset that donot have a match in the joined tables, we can use outer join.

The outer join is basically devided into 3 types like
-Left Outer join
-Right Outer join
-Full Outer join

-Left Outer join:
We use the  Left Outer join to get the matching information plus the unmatched information from the Left hand side table.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

-Right Outer join:
we use the Right Outer join to get the matching information plus the unmatched information from the Right hand side table.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

-Full Outer join:
Suppose we have unmatched information in both the sides we cannot retrive it at the same time to over come this in the ANSI style of join statement they have introduced Full Outer join.

For Example:
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
Full OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO



0 comments

Post a Comment