Joining Tables:
Join is used to relate multiple table. It is used to avoid redundant data. It just follow the concept Divide and Conquer. Instead maintaining the whole redundant data in one table and wasting of storage. we divide table into multiple part and as join the table. we join the table as what information is need. Also join save store memory, as below example table for employee thomas the dept_id is 1, instead maintaining the character storage.
Lets join 6 table
employee
In this table employee, the address of employee are divided as below.
state_table
city_table
id | Emp_name | Dept_id |
1 | Thomas | 1 |
2 | Jebas | 2 |
3 | Mark | 3 |
4 | Harry | 4 |
Deparment
Dept_id | Dept_name |
1 | Director |
2 | Manager |
3 | Employee |
4 | HR |
country_table
country_id | country_name |
1 | INDIA |
2 | UK |
3 | US |
state_table
state_id | country_id | state_name |
1 | 1 | Tamil Nadu |
2 | 1 | Andhra Pradesh |
3 | 1 | Kerala |
4 | 3 | New York |
5 | 2 | England |
city_table
city_id | state_id | city_name |
1 | 1 | Tirunelveli |
2 | 1 | Tuticorin |
3 | 3 | Thiruvananthapuram |
4 | 4 | Beacon |
5 | 5 | Gloucester |
Address_table
Emp_id | city_id | state_id | country_id |
1 | 1 | 1 | 1 |
2 | 2 | 1 | 1 |
3 | 4 | 4 | 3 |
4 | 5 | 5 | 2 |
SELECT Emp_name
,dept_name
,city_name
,state_name
,country_name
FROM Employee, dept_table,
city_table,
state_table,
country_table,
Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id
AND Adresss_table.state_id =state_table.state_id
AND Adresss_table.country_id =country_table.country_id
This statement return as
Emp_name | dept_name | city_name | state_name | country_name |
Thomas | Director | Tirunelveli | Tamil Nadu | INDIA |
Jebas | Manager | Tuticorin | Tamil Nadu | INDIA |
Mark | Employee | Beacon | New York | US |
Harry | HR | Gloucester | England | UK |
Also we filter the result as we need
SELECT Emp_name
,dept_name
,city_name
,state_name
,country_name
FROM Employee, dept_table,
city_table,
state_table,
country_table,
Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id
AND Adresss_table.state_id =state_table.state_id
AND Adresss_table.country_id =country_table.country_id
AND country_table.country_name='INDIA'
This statement the employee whose country is india
Emp_name | dept_name | city_name | state_name | country_name |
Thomas | Director | Tirunelveli | Tamil Nadu | INDIA |
Jebas | Manager | Tuticorin | Tamil Nadu | INDIA |
SELECT Emp_name
,dept_name
,city_name
,state_name
,country_name
FROM Employee, dept_table,
city_table,
state_table,
country_table,
Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id
AND Adresss_table.state_id =state_table.state_id
AND Adresss_table.country_id =country_table.country_id
AND country_table.country_name='INDIA'
AND city_table.city_name='Tirunelveli'.
This statement the employee whose city name is Tirunelveli
Emp_name | dept_name | city_name | state_name | country_name |
Thomas | Director | Tirunelveli | Tamil Nadu | INDIA |
Thank You. Like, Share and Subscribe 👍 Followlect.com
Comments
Post a Comment