JOIN with WHERE clause:
We can filter records with conditions in join.
Table Alias:
The table alias is temporary name of table , it mostly during JOIN operation.
Employee
id | Emp_name | Age | Salary | Dept_id | Location |
1 | Jebas | 35 | 55000 | 2 | TVL |
2 | Thomas | 30 | 70000 | 1 | TVL |
3 | Mark | 25 | 55000 | 2 | TEN |
4 | Harry | 42 | 55000 | 2 | TUTY |
5 | Willam | 45 | 70000 | 1 | TUTY |
6 | James | 32 | 45000 | 3 | TEN |
7 | Mock | 22 | 45000 | 3 | KOVI |
8 | Sherin | 23 | 12000 | 7 | TUTY |
9 | Don Meon | 41 | 78000 | NULL | MDY |
10 | Moses | 26 | 23000 | 4 | RAMD |
Dept_table
Dept_id | Dept_name |
1 | Director |
2 | Manager |
3 | Employee |
4 | HR |
5 | TeamLeader |
6 | Developer |
7 | Tester |
8 | Supporter |
select emp_name, dept_name, salary FROM Employee e, dept_table d
where e.dept_id =d.dept_id AND e.age < 40;
This statement employee name their salary, department name whose age less than 40.The e is alias for table Employee and d is alias for table dept_table.
Emp_name | Dept_name | Salary |
Jebas | Manager | 55000 |
Thomas | Director | 70000 |
Mark | Manager | 55000 |
James | Employee | 45000 |
Mock | Employee | 45000 |
Sherin | Tester | 12000 |
Moses | HR | 23000 |
select emp_name, dept_name, location FROM Employee e,dept_table d
where e.dept_id =d.dept_id AND e.location ='TVL'
This statement return employee name and department name, whose location is TVL.
Emp_name | Dept_name | location |
Jebas | Director | TVL |
Thomas | Manager | TVL |
select emp_name, dept_name, location FROM Employee e, dept_table d
where e.dept_id =d.dept_id AND e.location ='TVL' AND Age<32
This statement return employee name and department name, whose location is TVL and age less than 32.
Emp_name | Dept_name | location |
Jebas | Director | TVL |
select emp_name, dept_name FROM Employee e, dept_table d
where e.dept_id =d.dept_id AND d.dept_name ='MANAGER'
This statement return name of manager.
Emp_name | Dept_name |
Jebas | Manager |
Mark | Manager |
Harry | Manager |
select emp_name, dept_name, location FROM Employee e,dept_table d
where e.dept_id =d.dept_id AND d.dept_name ='manager' and e.location ='TEN'
This statement return name of manager whose location is 'TEN'
Emp_name | Dept_name | Location |
Mark | Manager | TEN |
Thank You. Like, Share and Subscribe 👍 Followlect.com
Comments
Post a Comment