Mysql subquery with IN and NOT IN operators

Mysql subquery is a nested query  that is being used inside another queries like select, update, delete etc. A mysql subquery also called a inner query and the main query that contain this subquery is called outer query.

We can use IN query like this in where clause –

Select empid from employee where salary IN (2000, 30000,40000,50000)

Lets takes  another example of sub query that return employees from Asian country.

Select empname from employees where country IN (Select country from worldwide where region = 'Asia'  )

Above, outer query selects the employee name where country is result set of inner query. Inner query return all country in Asia region.

We can use also sub query in where statement –

Select billlno, amount  from payments where amount= (select max(amount) from payments)

Above query return bill no, amount from payments where amount is max from payments table.

We can use the > and < operators in sub query.

We can also use IN and NOT IN operators in where clause –

Select `customers name` from `customer list` where `customer number` NOT IN (select Distinct `customer number` from orders)

Above, we return customers name who has not ordered any product.

please write in comments with other examples. thanks.