MySQL Summary Subquery - Detailed

1. What is a subquery

-- Requirements: Query who is in the development department select * from emp; -- Query by two statements select id from dept where name='Development Department' ; select * from emp where dept_id = 1; -- Use subqueries select * from emp where dept_id = (select id from dept where name='Marketing Department');

The concept of subqueries:
- 1) The result of one query as a condition of another query
- 2) Nested queries, internal queries called subqueries
- 3) Use parentheses for subqueries

2. Three cases of subquery results

1) The result of the subquery is a single row, single column

2) Subquery results in multiple rows and single column

3) Subquery results in multiple rows and columns

3. When the result of a subquery is a value

As long as the resu lt of the subquery is single row and single column, it must be conditioned after WHERE. The parent query uses: comparison operators, such as: >, <, <>, =and so on.

SELECT query field FROM table WHERE field= (subquery);

3.1 Case 1: Query who is the highest paid employee?

-- 1) Query what the maximum wage is select max(salary) from emp; -- 2) Query the corresponding employee information from the maximum salary to the employee table select * from emp where salary = (select max(salary) from emp);

3.2 Case 2: Query what employees are paid less than the average salary?

-- 1) Query what the average salary is select avg(salary) from emp; -- 2) Query employee table for less than average information select * from emp where salary < (select avg(salary) from emp);

4. The result of the subquery is multiple rows and single column

The result of the subquery is a single multiple row, the result set is like an array, and the parent query uses the IN operator

SELECT query field FROM table WHERE field IN (subquery);

4.1 Case 1: Query the names of departments whose employees are paid more than 5000

-- Query the Department where employees over 5000 are located first id select dept_id from emp where salary > 5000; -- Query again in these departments id Name of Central Department Subquery returns more than 1 row select name from dept where id = (select dept_id from emp where salary > 5000); select name from dept where id in (select dept_id from emp where salary > 5000);

4.2 Case 2; Query all employee information in Development and Finance

-- Inquire first from Development and Finance id select id from dept where name in('Development Department','Finance Department'); -- Query again in these departments id Which employees are there select * from emp where dept_id in (select id from dept where name in('Development Department','Finance Department'));

5. Subquery results in multiple rows and columns

Subquery results with multiple columns must follow FROM as tables

SELECT query field FROM (subquery) table alias WHERE condition;

Subqueries need to be aliased as tables, otherwise fields in tables cannot be accessed without a name for this table

5.1 Case: Query out employee information, including department name, who will be employed after 2011

-- Query out employee information, including department name, that will be available after 2011 -- Query 2011 in the employee table-1-1 Future Employees select * from emp where join_date >='2011-1-1'; -- Query all department information, combine with the information in the virtual table above, and find all departments id Equal dept_id select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

You can also use table joins:

select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';