MySQL Summary Subquery - Detailed

Subquery

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';

6. Subquery Summary

  • Subquery results are conditional after WHERE as long as they are single column
  • Subquery results with multiple columns are queried twice as a table after FROM

OK, that's it

Tags: Database less

Posted on Fri, 12 Jun 2020 13:09:12 -0400 by JeffK