Queries the minimum wage information of each department and the department wage information.

Queries the minimum wage information of each department and the department wage information.

Example:

Query the minimum wage information of each department

Join subquery Method

1. query the minimum wage of each department

select deptno, min(sal) min_sal from emp group by deptno;

2. Minimum wage associated personnel information

select e.* from emp e, (select deptno, min(sal) min_sal from emp group by deptno) swhere e.deptno = s.deptnoand e.sal = s.min_sal;

In Keyword Method

The in keyword is a method that is easy to think of in this scenario. First, the minimum wage of the Department is queried, and then the employee information of the minimum wage is matched.

select * from emp where sal in (select min(sal) from emp group by deptno);

Error Parsing

The in method has a problem. When there are multiple identical wage values in the two Departments, an error is returned.

In the case of emp, the number of user departments with empno 1234 is changed to 20.

update emp set deptno = 20 where empno = 1234;

Then, use the in query method to check the employee information of the Department's minimum wage.

At this time, the minimum wage for employees with empno of 1234 is the same as that for departments with deptno of 10. Therefore, the minimum wage is found through the in query.

HoweverAssociate subqueryMethod query,

In summary, in a similar query, the in keyword is used with caution for direct query, but the associated subquery should be used for step-by-step query.