Monday, February 16, 2009

INNER JOIN

Today let's discuss on the advance and the most important part that is how to join the two tables. Their are many ways to join a table but it is up to you what type of joining do you need The two type of joining is the INNER JOIN and OUTER JOIN.

Lets start with INNER JOIN.
By the term inner join we mean joining two or more table under some condition i.e through WHERE clause. It is just like a JOIN statement. Lets see the example

Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

TABLE A

P_Id

LastName

FirstName

Address

City

1

ABC

DEF

Street 10

PPP

2

GHI

IJK

Street 20

QQQ

3

LMN

OPQ

Street 30

DDD



TABLE B
O_Id OrderNo P_Id
1 111111
1
2 222222 2
3 444444 3
4 555555
4
5 666666
5

Now we ask for INNER JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM TABLE A
INNER JOIN TABLE B
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName

The out put that it will return will be

LastName FirstName OrderNo
ABC DEF
111111
GHI IJK
222222
LMN OPQ 333333

The INNER JOIN keyword will return the number of rows that are common in both the table , like here the column that are returned are of two common people Hansen, Pettersen.