>Understanding MySQL subqueries - w3resource (2023)

subqueries

A subquery is a nested SQL query within a larger query.

• A subquery can appear in:
  • - A SELECT clause
  • - A FROM clause
  • - A WHERE clause
• In MySQL, the subquery can be nested within a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement, or within another subquery.
• Typically, a subquery is added to the WHERE clause of another SQL SELECT statement.
• You can use comparison operators such as >, <, or =. The comparison operator can also be a multi-line operator, e.g. e.g. IN, ANY, SOME or ALL.
• A subquery can be treated as an inner query, which is a SQL query placed as part of another query called an outer query.
• The inner query runs before your main query so the results of the inner query can be passed to the outer query.

MySQL-Unterabfragen mit EXISTS oder NOT EXISTS

MySQL correlated subqueries

MySQL subqueries in the FROM clause

Subquery syntax:

• The subquery (inner query) is executed once before the main query (outer query) is executed.
• The main query (outer query) uses the result of the subquery.

Subquery syntax as specified by the SQL standard and compatible with MySQL

DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* semidica */ FROM t2 WHERE DOESNOT EXIST (SELECT * FROM t3 WHERE ROW (5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50.77 FROM (SELECT * FROM t5) AS t5)));

A subquery can return a scalar (single value), a single row, a single column, or a table (one or more rows with one or more columns). They are called scalar, column, row, and table subqueries.

Example of a MySQL subquery:

Using a subquery, list the names of employees who paid more than "Alexander" from emp_details.

mysql> SELECT firstname, lastname, salary FROM emp_details WHERE salary >(SELECT salary FROM emp_details WHERE firstname='Alexander');+------------+---------- -------------+| name | Surname | Salary |+------------+-----------+----------+| Stephen | king | 24000.00 || sweetheart | Kochar | 17000.00 || Lex | DeHan | 17000.00 || RABBI | CHANDRA | 15000.00 || Anna | king | 17000.00 |+-----------+-----------+----------+5 lines in the sentence (0.00 sec. )

Subqueries: Policies

There are a few guidelines to keep in mind when using subqueries:
- A subquery must be enclosed in parentheses.
- Use single-line operators with single-line subqueries and use multi-line operators with multi-line subqueries.
- If a subquery (inner query) returns null to the outer query, the outer query will return no rows when certain comparison operators are used in a WHERE clause.

Types of Subqueries

• The subquery as a scalar operand
• Comparisons with subqueries
• Unterabfragen mit ALL, SOME, IN SOME
• row subqueries
• Subqueries with EXISTS or NOT EXISTS
• correlated subqueries
• Subqueries in the FROM clause

MySQL subquery as scalar operand

A scalar subquery is a subquery that returns exactly one column value from one row. A scalar subquery is a single operand, and you can use it almost anywhere a single column or literal value is legal. If the subquery returns 0 rows, the value of the subquery's scalar expression is NULL, and if the subquery returns more than one row, MySQL returns an error.
There are some situations where a scalar subquery cannot be used. If a statement only accepts a literal value, you cannot use a subquery. For example, LIMIT requires literal integer arguments and LOAD DATA INFILE requires a literal string filename. You cannot use subqueries to provide these values.

Example: MySQL subquery as scalar operand

mysql> SELECT employee_id, lastname, (CASE WHEN department_id=(SELECT department_id from Departments WHERE location_id=2500) THEN 'Canada' ELSE 'USA' END) location FROM Employees;+------------ + -------------+----------+| Employee_ID | Surname | Location |+------------+-------------+----------+| 100 | king | United States || 101 | Kochar | United States || 102 | DeHan | United States || 103 | hunold | United States || 104 | seriously | United States || 105 | Austin | United States || - - - - - - - - - - - - - - - - - -|| - - - - - - - - - - - - - - - - - -|107 lines in sentence (0.00 sec)

MySQL Subqueries: Using Comparisons

A subquery can be used before or after any of the comparison operators. The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. SQL then compares the value resulting from the subquery with the value on the other side of the comparison operator. You can use the following comparison operators:

Suppose you want to find the ID, first name, last name, and salaries of employees whose average salary is higher than the average salary for the entire company.

mysql> SELECT employee_id, firstname, lastname, salary FROM employees WHERE salary> (SELECT AVERAGE (SALARY) FROM employees); +-------------+------------+------------+-------- - -+| Employee_ID | name | Surname | Salary |+------------+------------+------------+------ - - --+| 100 | Stephen | king | 24000.00 || 101 | sweetheart | Kochar | 17000.00 || 102 | Lex | DeHan | 17000.00 || 103 | Alexander | hunold | 9000.00 || 108 | Nancy | Grünberg | 12000.00 || 109 | Daniel | Favorite | 9000.00 || 120 | Matthew | White | 8000.00 || 121 | Adam | Fripp | 8200.00 || 122 | Payment | Merchant | 7900.00 ||- - - - - - - - - - - - - - - - - - - - -||- - - - - - - - - - - - - - - - - - - - - - - - - - -|+-------------+-------------+---------- -+ -- - -- -----+51 lines in sentence (0.00 sec)

MySQL-Unterabfragen mit ALL, ANY, SOME

You can use a subquery for a comparison operator followed by the keywords ALL, ANY, or SOME.

The ALL operator compares the value to each value returned by the subquery. Therefore, the ALL operator (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ALL values ​​in the column returned by the subquery.

ALL comparison_operator_operator (subquery)

NOT IN is an alias of <> ALL. So these two statements are identical:

Code:

SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2); CHOOSE c1 FROM t1 WHERE c1 IS NOT (CHOOSE c1 FROM t2);

Example: MySQL subquery, ALL operator

The following query selects the department with the highest average salary. The subquery finds the average salary for each department, and the main query then selects the department with the highest average salary.

mysql> SELECT department_id, STAFF GROUP AVERAGE(SALARY) BY department_id WITH AVERAGE(SALARY)>=ALL (SELECT STAFF GROUP AVERAGE(SALARY) BY department_id);+----------- - -- -+ --------------+| Department ID | AVERAGE(SALARY) |+----------+--------------+| 90 | 19333.333333 |+---------------+---------------------+1 line in array (0.00 sec .)

Note: We use the ALL keyword here for this subquery because the department selected by the query must have an average salary that is greater than or equal to all of the average salaries of all other departments.

The ANY operator compares the value to each value returned by the subquery. Therefore, the ANY keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY of the values ​​in the column returned by the subquery.

Syntax:

operand_comparison_operator ANY (Unterkonsulta)

Example: MySQL subquery, ANY operator

The following query selects all employees who work at location 1800. The subquery finds the department ID at location 1800, and then the main query selects employees who work in one of those departments.

staff table:

Department table:

mysql> SELECT first_name, last_name,department_id FROM Employees WHERE Department_id= ANY(SELECT DEPARTMENT_ID FROM Departments WHERE location_id=1800);+------------+----------- +---------------+| Vorname | Spitzname | Abteilungs-ID |+------------+----------------+---------------+ | Michael | Hartstein | 20 || streicheln | Fee | 20 |+-------------+-----------+---------------+2 Zeilen ohne Satz ( 0,00 Sek.)

Note: We use the ANY keyword in this query because the subquery is likely to find more than one department at location 1800. If you use the keyword ALL instead of ANY, no data will be selected because no employee works in all departments of 1800 plot

When used with a subquery, the word IN (like any list item) is an alias for =ANY. Therefore, the following two statements are identical:

Code:

SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2); SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);

The word SOME is a nickname for ANY. So these two statements are identical:

Code:

SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2); SELECT c1 FROM t1 WO c1 <> SOME (SELECT c1 FROM t2);

MySQL row subqueries

A row subquery is a subquery that returns a single row and more than one column value. You can use = , >, <, >=, <=, <>, !=, <=>comparison operators. Check out the examples to continue:

Code:

SELECT * FROM table1 WHERE (col1, col2) = (SELECT col3, col4 FROM table2 WHERE id = 10); SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3,col4 FROM table2 WHERE id = 10);

For both queries

• If Table2 contains a single row with ID = 10, the subquery returns a single row. If this row has col3 and col4 values ​​equal to the col1 and col2 values ​​of any row in Table 1, the WHERE expression is TRUE and any query returns those rows from Table 1.
• If the values ​​col3 and col4 in row table2 are not equal to the values ​​col1 and col2 in any row table1, the expression is FALSE and the query returns an empty result set. The expression is unknown (that is, NULL) if the subquery produces no rows.
• If the subquery produces multiple rows, an error occurs because a row subquery can return at most one row.

Example: MySQL row subqueries

In the following examples, the queries return different results based on the above conditions:

Department table:

staff table:

mysql> SELECT first_name FROM Empleados WHERE ROW(department_id, manager_id) = (SELECT division_id, manager_id FROM departmentamentos WHERE location_id = 1800);+------------+| Primer_Name |+-------------+| Pat |+------------+1 carreira na série (0,00 sek)

Code:

mysql>SELECT first_name FROM EmployeesWHERE ROW(department_id, manager_id) = (SELECT division_id, manager_id FROM departments WHERE location_id = 2800); Conjunto vacio (0,00 Sek.)

Code:

mysql>SELECT first_name FROM Employees WHERE ROW(department_id, manager_id) = (SELECT division_id, manager_id FROM departments WHERE location_id = 1700);ERRO 1242 (21000): Subconsulta regresa mais de 1 linha

MySQL-Unterabfragen mit EXISTS oder NOT EXISTS

The EXISTS operator checks whether there are rows in the result set of the subquery. If a subquery row value is found, the subquery is EXISTS TRUE, and in this case the subquery is NOT EXISTS FALSE.

Syntax:

SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2);

In the above statement, if Table2 contains rows, even rows with NULL values, the EXISTS condition is TRUE. An EXISTS subquery usually starts with SELECT *, but can start with SELECT 'X', SELECT 5, or SELECT column1, or anything else. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

Example: MySQL subqueries with EXISTS

In the following tables (Employees), look for employees (Employee ID, First Name, Last Name, Job ID, Department ID) that have at least one person reporting to them.

staff table:

SELECT employee_id, firstname, lastname, job_id, department_id FROM employee E WHERE EXISTS (SELECT * FROM employee WHERE manager_id = E.employee_id);+-------------+------- -----+-----------+---------+---------------+| employer_id | first name | Nickname | job_id | Department ID |+-------------+-------------+-----------+-- --- --- ------------------+| 100 | Estevao | king | ANNOUNCEMENT_PRESS | 90 || 101 | sweetheart | Kochar | AD_VP | 90 || 102 | Lex | DeHan | AD_VP | 90 || 103 | Alexander | hunold | EN_PROG | 60 || 108 | Nancy | Grünberg | FI_MGR | 100 || 114 | cover | Raphael | PU_MAN | 30 || 120 | Matthew | White | ST_MAN | 50 || 121 | Adao | Fripp | ST_MAN | 50 || ---------- | ---------- | ---------- | ------- | ------------- |+-------------+------------+-------- ---+--------+---------------+18 Disconnected Beats (0.02 sec)

Example: MySQL subqueries com does not exist

The NOT EXISTS subquery almost always contains correlations. Here is an example:
In the following table (departments and employees) you can find all departments (department id, department name) that have no employees.

Department table:

staff table:

mysql> SELECT departmentid, departmentname FROM departments d WHERE NOT EXIST (SELECT * FROM employees WHERE departmentid = d.departmentid);+--------------+--- -------------------+| Department ID | Department name |+---------------+------------------------------- -----+| 120 | treasury || 130 | corporate income tax || 140 | Control and Credit || 150 | Shareholder Services || 160 | Benefits || 170 | Manufacturing || 180 | construction || 190 | Recruitment || 200 | Operations || ------------ | -------------------- | +---------------------------+----------------------+16 rows in array (0.00 sec)

MySQL correlated subqueries

A correlated subquery is a subquery that contains a reference to a table (in the main query) that also appears in the outer query. MySQL evaluates from the inside out.

Correlated subquery syntax:

Example - 1: MySQL correlated subqueries

Next, find all of the employees who make more than the average salary in your department.

staff table:

mysql> SELECT LastName, Salary, DeptID FROM Employee OutsiderWHERE Salary > (SELECT AVERAGE(Salary) FROM Employee WHERE DeptID = Outsider.DeptID );+-------------- ---+- ------- -------------------+| Surname | Salary | Department ID |+-----------+----------+---------------+| king | 24000.00 | 90 || hunold | 9000.00 | 60 || seriously | 6000.00 | 60 || Grünberg | 12000.00 | 100 || Favorite | 9000.00 | 100 || Raphael | 11000.00 | 30 || White | 8000.00 | 50 || Fripp | 8200.00 | 50 || -------- | -------- | ------------ |+-----------+----------+----------- -- -+38 lines in sentence (0.02 sec.)

Example - 2: MySQL correlated subqueries

The Employees and job_history tables show details of employees who have changed jobs at least once.

staff table:

tabela job_history:

mysql> SELECT firstname, lastname, employee_id, job_id FROM employee E WHERE 1 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = E.employee_id);+------------+--- --------+------------+---------+| first name | Nickname | employer_id | work_id |+-----------+-----------+------------+---------- ------- -+| sweetheart | Kochar | 101 | AD_VP || Lex | DeHan | 102 | AD_VP || cover | Raphael | 114 | PU_MAN || Payment | Merchant | 122 | ST_MAN || Jonathan | Taylor | 176 | SA_REP || Jennifer | baleía | 200 | ANNOUNCEMENT_ASST || Michael | hard stone | 201 | MK_MAN |+------------+-----------+------------+-------- - +7 lines without a sentence (0.00 sec)

MySQL subqueries in the FROM clause

Subqueries work within the FROM clause of a SELECT statement. The syntax is:

SELECT... FROM (Unterabfrage) [AS] Name...

Each table in a FROM clause must have a name, so the [AS] naming clause is mandatory. All columns in the subquery select list must have unique names.

Example: MySQL subqueries in the FROM clause

We have the following table tb1.

mysql> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT); Query OK, 0 rows affected (0.73 sec)

Let's put some values ​​in tb1.

mysql> INSERT INTO VALUES tb1(1, '1', 1.0); Query OK, 1 row affected (0.11 sec) mysql> INSERT INTO VALUES tb1(2, '2', 2.0); Query OK, 1 row affected (0.07 sec)mysql> INSERT INTO tb1 VALUES(3, '3', 3.0); Query OK, 1 row affected (0.03 sec)mysql> select * from tb1;+------+----- -+ ------+| c1 | c2 | c3 |+------+------+------+| 1 | 1 | 1 || 2 | 2 | 2 || 3 | 3 | 3 |+------+------+------+3 lines in sentence (0.00 sec)

To use a subquery in the FROM clause using the sample table (tb1):

mysql> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;+------+------+- -----+| sc1 | sc2 | sc3 |+------+------+------+| 2 | 2 | 6 || 3 | 3 | 9 |+------+------+------+2 Zeilen ohne Satz (0,02 Sek.)

Front:NATURAL UNION
Next:MySQL procedure