How to join two tables to get combined result

I am trying to write a query, where I am retrieving employee data, fname, salary and unit no., where fname is ‘Khalid’ and his department name should be either ‘IT’ or ‘Development’.

I am writing the query as

Select fname, salary, unitno from Employee Where fname = Khalid and Department.name = 'IT' or 'Development'

Both table have relationship based on Department number.

For Example:

Employee Table:

Fname salary unitno Dno
Angel 2000   5      2
Jame  1000   3      2
Khalid 1500  6      4
Khalid 2500  9      2

Department Table:

Dnumber Name
2       IT
2       IT 
4       Development
2       IT 

Now the two table is in a relationship. My query is not using Dno and Dnumber to connect them and finding the accurate result. Do I need to use JOIN here? and how I know which is left table and which is right table?

Answer

You can join and filter:

select e.fname, e.salary, e.unitno, d.name
from employee e
inner join department d on d.dnumber = e.dno
where e.fname = 'Khalid' and d.name in ('IT', 'Development')

Alternatively, you can also use a correlated subquery for filtering, if you don’t need to display the department name:

select e.fname, e.salary, e.unitno 
from employee e
where e.fname = 'Khalid' and exists (
    select 1 from department d where d.dnumber = e.dno and d.name in ('IT', 'Development')
)