SQL 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 :
- inner join,
- left outer join,
- right outer join,
- full outer join, and
- 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 ?
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 |
