Skip to main content

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

Comments