Friday, May 14, 2010

Understanding Sql Server Joins

The join operator builds a relation from two specified relations. This relation consists of all possible combination of tuples, one from each relation that satisfy the specified condition.
            Other word’s The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

A join condition defines the way two tables are related in a query by: 
• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

There are flowing types of join in SQL server 2008.
1. Inner join.
2. Outer join.
An outer join is of three types:

i. Left outer join.
ii. Right outer join.
iii. Full outer join.

3. Cross join.
4. Equi join.
5.Non-Equi join
6. Self join.

1. Describe the inner join.
An inner join retrieves records from multiple tables by using a comparison operator on a common column. When an inner join is applied, only rows values satisfying the join condition in the common column are displayed. Row in both tables that do not satisfy the join condition are not displayed. Inner join is the most widely used join type.

Inner join syntax is below.
Select[column list] from[left joined table] [inner] join [right joined table]

NOTE:-Keyword INNER sometimes is avoided, but anyway it remains inner join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT.

Query of [INNER] JOIN .
create table Emp_Record
[Employee Name] varchar(50),
[Employee city] varchar(50),
[Employee contact#] bigint,
[Employee ID] int,
[Employee Dept ID] int
insert into Emp_Record values('johan','Mumbai',2487563,1,101)
insert into Emp_Record values('James bond', 'New york',2546823,2,102)
insert into Emp_Record values('Harry','New Delhi', 2215896,3,103)
insert into Emp_Record values('Michel','pune',2245614,4,104)
insert into Emp_Record values('Thomes','Banglor',2325642,5,105)
insert into Emp_Record values('jacky','New york',3225645,6,106)

create table Emp_Department

[Employee Dept name] varchar(50),
[Employee Dept login id] varchar(50),
[Employee Dept D-O-O] varchar(50),
[Employee Dept contact#] bigint,
[Employee Dept id] int

insert into Emp_Department values('Tool designer','adventure-works','12-05-1999',24875965,101)
insert into Emp_Department values('Testing','test123','05-12-2008',25982569,102)
insert into Emp_Department values('Human Resources','human456','08-09-2005',25894729,103)
insert into Emp_Department values('Manager','manager132','01-02-2006',25782469,104)
insert into Emp_Department values('Research & Development','dev516','08-01-1998',28754569,105)
insert into Emp_Department values('Purchasing','pur568','03-09-2009',28922569,102)

command in [INNER]JOIN query.
select Record.[Employee Name],Record.[Employee city],Record.[Employee contact#],Record.[Employee ID],Record.[Employee Dept ID],
Dept.[Employee Dept name],Dept.[Employee Dept login id],Dept.[Employee Dept D-O-O],Dept.[Employee Dept contact#],Dept.[Employee Dept id]
from Emp_Record as Record join Emp_Department as Dept
on Record.[Employee Dept id] = Dept.[Employee Dept id]

The result is displayed below with JOIN the both table Emp_Record and Emp_ Department.
 The two Set in the vain diagram shows table  Emp_Record and Emp_ Department.
Intersected area shows  the JOIN conditions apply. 
 And the final results.


No comments:

Recent Posts