MySql sub-query is an important part of multi-table query. It is often used together with join query and is the basis of multi-table query. This paper will take you to learn about common sub-query and some examples to consolidate your learning.
1. What is a subquery?
When one query is a condition of another, it is called a subquery.Subqueries can use several simple commands to construct powerful composite commands.Subqueries are most commonly used in the WHERE clause of SELECT-SQL commands.A subquery is a SELECT statement nested within a SELECT, SELECT...INTO statement, INSERT...INTO statement, DELETE statement, or UPDATE statement or nested in another subquery.
2. Table-building Statements
List sql statements needed for subquery exercises
-- /Create Department Table/ CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); -- /Create employee table/ CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), COMM DECIMAL(7,2), deptno INT, CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ); -- /Create payroll scale/ CREATE TABLE salgrade( grade INT PRIMARY KEY, losal INT, hisal INT ); -- /insert dept table data/ INSERT INTO dept VALUES (10, 'Department of Education and Research', 'Beijing'); INSERT INTO dept VALUES (20, 'Department of Engineering', 'Shanghai'); INSERT INTO dept VALUES (30, 'Sales Department', 'Guangzhou'); INSERT INTO dept VALUES (40, 'Finance Department', 'Wuhan'); -- /insert emp table data/ INSERT INTO emp VALUES (1009, 'Zeng A Niu', 'Chairman', NULL, '2001-11-17', 50000, NULL, 10); INSERT INTO emp VALUES (1004, 'Liu Bei', 'manager', 1009, '2001-04-02', 29750, NULL, 20); INSERT INTO emp VALUES (1006, 'Guan Yu', 'manager', 1009, '2001-05-01', 28500, NULL, 30); INSERT INTO emp VALUES (1007, 'Zhang Fei', 'manager', 1009, '2001-09-01', 24500, NULL, 10); INSERT INTO emp VALUES (1008, 'Zhuge Liang', 'analyst', 1004, '2007-04-19', 30000, NULL, 20); INSERT INTO emp VALUES (1013, 'Pang Tong', 'analyst', 1004, '2001-12-03', 30000, NULL, 20); INSERT INTO emp VALUES (1002, 'Daisy', 'Salesperson', 1006, '2001-02-20', 16000, 3000, 30); INSERT INTO emp VALUES (1003, 'Yin Tianzheng', 'Salesperson', 1006, '2001-02-22', 12500, 5000, 30); INSERT INTO emp VALUES (1005, 'Thankson', 'Salesperson', 1006, '2001-09-28', 12500, 14000, 30); INSERT INTO emp VALUES (1010, 'Xiangr', 'Salesperson', 1006, '2001-09-08', 15000, 0, 30); INSERT INTO emp VALUES (1012, 'Cheng Pu', 'Clerk', 1006, '2001-12-03', 9500, NULL, 30); INSERT INTO emp VALUES (1014, 'Yellow lid', 'Clerk', 1007, '2002-01-23', 13000, NULL, 10); INSERT INTO emp VALUES (1011, 'Zhou Tai', 'Clerk', 1008, '2007-05-23', 11000, NULL, 20); INSERT INTO emp VALUES (1001, 'Ganning', 'Clerk', 1013, '2000-12-17', 8000, NULL, 20); -- /insert salgrade table data/ INSERT INTO salgrade VALUES (1, 7000, 12000); INSERT INTO salgrade VALUES (2, 12010, 14000); INSERT INTO salgrade VALUES (3, 14010, 20000); INSERT INTO salgrade VALUES (4, 20010, 30000); INSERT INTO salgrade VALUES (5, 30010, 99990);
3. Format of Subquery Use
SELECT xxx WHERE XX = (SELECT xx FROM XX)...
4. Subquery Practice
1. Find out the names and salaries of all the employees in the Education and Research Department
Analysis: The fields that need to be found are employee name and salary. Both fields are provided by EMP table, so only data need to be extracted from emp, but there is only one deptno field in EMP table, so department number of education and research department needs to be found. Here is a conditional query.
SELECT e.ename,e.sal FROM emp e WHERE e.deptno = (SELECT d.deptno FROM dept d WHERE d.dname = 'Department of Education and Research');
2. Find out the name of each department and the number of employees included
Analysis: To find out the Department name and the number of employees, my best practice is to use an internal join, have the Department table and the employee table join, then filter out the data with the same employee Department ID and department table ID, filter out the Department name and use the COUNT() function, and then group by Department name, which is the best method
SELECT d.dname ,COUNT(*) FROM dept d INNER JOIN emp e ON d.deptno = e.deptno GROUP BY d.dname;
Some friends may ask, why not use the left outer connection but the inner connection?Wouldn't you like to query all departments?If you use a left outer connection, you can find out all the Department names, but if no employees correspond to that department, fake data will appear, like this one:
This data will still be queried and counted into the COUNT() function, which shows that the Department has one data, one employee, which is wrong
3. Identify departments with at least four employees.Display Department number, Department name, Department location, number of departments
This Sql is an upgraded version of Question 2 and requires querying specific fields and encapsulating it as a temporary table temp.
SELECT dept.dname,temp.num FROM dept INNER JOIN (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) temp ON dept.deptno=temp.deptno WHERE temp.num >= 4 ;
4. List all employees with higher salaries than those with lower salaries
SELECT e1.ename FROM emp e1 WHERE e1.sal >(SELECT e2.sal FROM emp e2 WHERE e2.ename = 'Thankson');
5. List the names of all employees and their immediate superiors
Since all employees are queried, the left connection is used instead of the inner connection
SELECT e1.ename employee name, e2.ename superior name FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno;
6. List the number, name, department name of all employees who are hired earlier than their immediate superiors
Catalog
1. Find out the names and salaries of all the employees in the Education and Research Department
2. Find out the name of each department and the number of employees included
4. List all employees with higher salaries than those with lower salaries
5. List the names of all employees and their immediate superiors
SELECT e1.`empno`,e1.`ename`,d.deptno FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.`mgr` = e2.`empno` LEFT OUTER JOIN dept d ON e1.`deptno` = d.`deptno` WHERE e1.`hiredate` < e2.`hiredate`
