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.

SQL Join Tables

In SQL join keyword is used quite offen. It is used to join two or more table together. There may be situation when you would like to join two or more table to get the common data or to verify some query.

Lets take this example.


This is a basic example and the complexities depend on the level of query that you want.

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

To join a table, the most important thing to remember is to join the table on primary key. I am saying this because we want to join table on unique ID and primary key sufies this situation. As in the TABLE A table P_ID is unique ID and no two employees can have the same, so we join the table on this bases. If this condition is not taken into account the table will still join and provide the out put but that would be a catisian product i.e all the tables in TABLE A and all the tables in TABLE B. Another thing to remember is that both the tables should have common columb, on which you could join the table. As you can see TABLE A and TABLE B have P_ID is common, so the common table will be returned.

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

For this example
SELECT P_ID
FROM TABLE A
INNER JOIN TABLE B
ON TABLE A.P_ID=TABLE B.P_ID;