JOIN in SQL with Where clause

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