JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means “to combine two or more tables”.

The SQL JOIN clause takes records from two or more tables in a database and combines it together.

ANSI standard SQL defines five types of JOIN :

1. inner join,
2. left outer join,
3. right outer join,
4. full outer join, and
5. cross join.

In the process of joining, rows of both tables are combined in a single table.

Why SQL JOIN is used?

If you want to access more than one table through a select statement.

If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.

The joining of two or more tables is based on common field between them.

SQL INNER JOIN also known as simple join is the most common type of join.

How to use SQL join or SQL Inner Join?

Let an example to deploy SQL JOIN process:

1.Staff table

ID	Staff_NAME	Staff_AGE	STAFF_ADDRESS	Monthley_Package
1	ARYAN	22	MUMBAI	18000
2	SUSHIL	32	DELHI	20000
3	MONTY	25	MOHALI	22000
4	AMIT	20	ALLAHABAD	12000

2.Payment table

Payment_ID	DATE	Staff_ID	AMOUNT
101	30/12/2009	1	3000.00
102	22/02/2010	3	2500.00
103	23/02/2010	4	3500.00

So if you follow this JOIN statement to join these two tables ?

SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT   

   FROM STAFF s, PAYMENT p  

   WHERE s.ID =p.STAFF_ID;

This will produce the result like this:

STAFF_ID	NAME	Staff_AGE	AMOUNT
3	MONTY	25	2500
1	ARYAN	22	3000
4	AMIT	25	3500
1	ARYAN	22	3000