Subquery in MySQL allows nesting queries, where the inner query runs first and its result is used by the outer query. It can be applied in SELECT, FROM, WHERE, and HAVING clauses. In the WHERE clause, it filters data, such as finding employees with salaries above the average: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);. Here, the subquery calculates the average salary, and the outer query retrieves employees earning more. In the FROM clause, a subquery acts as a derived table; for example, to find the highest departmental average salary: SELECT MAX(dept_avg) FROM (SELECT department, AVG(salary) AS dept_avg FROM employees GROUP BY department) AS dept_salaries;. The subquery computes average salaries per department, aliased as dept_salaries, and the outer query finds the maximum. In the SELECT clause, scalar subqueries return single values, like displaying each employee’s salary alongside the company average: SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees;. This subquery runs once unless correlated. Correlated subqueries reference outer query columns and execute once per row, such as identifying employees earning more than their department’s average: SELECT e1.name, e1.department, e1.salary FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e1.department);.
Writing a subquery in MySQL involves placing one query inside another. The inner query (subquery) runs first and provides results that the outer query uses for filtering or calculations. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses. Here’s how to use them correctly.
Subquery in WHERE Clause
This is the most common use. The subquery filters data in the outer query based on its result.
- Select employees whose salary is above the average:
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
- The inner query calculates the average salary.
- The outer query retrieves all employees earning more than that value.
Subquery in FROM Clause
Use a subquery as a derived table when you need to work with intermediate results.
- Find the maximum salary per department and then get the highest among them:
SELECT MAX(dept_avg) FROM (SELECT department, AVG(salary) AS dept_avg FROM employees GROUP BY department) AS dept_salaries;
- The subquery groups employees by department and computes average salaries.
- The outer query finds the maximum of those averages.
- Always alias subqueries in FROM (e.g., AS dept_salaries).
Subquery in SELECT Clause
Include scalar (single-value) results from a subquery in your output columns.
- Show each employee and the company's average salary alongside their data:
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees;
- The subquery returns one value, so it can appear in SELECT.
- It runs once unless correlated with the outer row.
Correlated Subqueries
A subquery that references a column from the outer query. It runs once for each row in the outer query.
- Find employees earning more than the average in their department:
SELECT e1.name, e1.department, e1.salary FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e1.department);
- The inner query uses e1.department from the outer query.
- For each employee, it computes the average of their own department.
Make sure subqueries return appropriate result types: single values for comparisons, single columns for IN clauses, and properly structured rows for FROM usage. Always test subqueries independently before embedding them.
Basically just nest your queries where needed, ensure correct syntax, and remember performance can suffer with large datasets—indexes help.
The above is the detailed content of How to write a subquery in MySQL. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
How to use a CASE statement in MySQL?
Sep 20, 2025 am 02:00 AM
The answer is: MySQL's CASE statement is used to implement conditional logic in query, and supports two forms: simple and search. Different values ??can be dynamically returned in clauses such as SELECT, WHERE, and ORDERBY; for example, in SELECT, classification of scores by fractional segments, combining aggregate functions to count the number of states, or prioritizing specific roles in ORDERBY, it is necessary to always end with END and it is recommended to use ELSE to handle the default situation.
How to Automate MySQL Backups with a Script?
Sep 21, 2025 am 02:24 AM
Create a shell script containing the database configuration and mysqldump command and save it as mysql_backup.sh; 2. Store MySQL credentials by creating ~/.my.cnf file and set 600 permissions to improve security, modify the script to use configuration file authentication; 3. Use chmod x to make the script executable and manually test whether the backup is successful; 4. Add timed tasks through crontab-e, such as 02/path/to/mysql_backup.sh>>/path/to/backup/backup.log2>&1, realize automatic backup and logging at 2 a.m. every day; 5.
How to update a row if it exists or insert if not in MySQL
Sep 21, 2025 am 01:45 AM
INSERT...ONDUPLICATEKEYUPDATE implementation will be updated if it exists, otherwise it will be inserted, and it requires unique or primary key constraints; 2. Reinsert after deletion of REPLACEINTO, which may cause changes in the auto-increment ID; 3. INSERTIGNORE only inserts and does not repetitive data, and does not update. It is recommended to use the first implementation of upsert.
How to select distinct values in MySQL?
Sep 16, 2025 am 12:52 AM
Use the DISTINCT keyword to remove duplicate values ??from the specified column and return unique values. 1. The basic syntax is SELECTDISTINCTcolumn_nameFROMtable_name; 2. Query the unique value of a single column, such as SELECTDISTINCTcityFROMcustomers; 3. Query the unique combination of multiple columns, such as SELECTDISTINCTcity, stateFROMcustomers; 4. Filter with the WHERE clause and get the unique value, such as SELECTDISTINCTproduct_nameFROMordersWHEREorder_date>'202
How to use the EXPLAIN command in MySQL?
Sep 18, 2025 am 01:48 AM
EXPLAINinMySQLrevealsqueryexecutionplans,showingindexusage,tablereadorder,androwfilteringtooptimizeperformance;useitbeforeSELECTtoanalyzesteps,checkkeycolumnsliketypeandrows,identifyinefficienciesinExtra,andcombinewithindexingstrategiesforfasterqueri
How to use subqueries in MySQL?
Sep 20, 2025 am 01:07 AM
Subqueries can be used in WHERE, FROM, SELECT, and HAVING clauses to implement filtering or calculation based on the result of another query. Operators such as IN, ANY, ALL are commonly used in WHERE; alias are required as derivative tables in FROM; single values ??must be returned in SELECT; related subqueries rely on outer query to execute each row. For example, check employees whose average salary is higher than the department, or add the company average salary list. Subqueries improve logical clarity, but performance may be lower than JOIN, so you need to ensure that you return the expected results.
How to handle timezones in MySQL?
Sep 20, 2025 am 04:37 AM
Use UTC to store time, set the MySQL server time zone to UTC, use TIMESTAMP to realize automatic time zone conversion, adjust the time zone according to user needs in the session, display the local time through the CONVERT_TZ function, and ensure that the time zone table is loaded.
How to calculate distance between two points in MySQL
Sep 21, 2025 am 02:15 AM
MySQL can calculate geographical distances through the Haversine formula or the ST_Distance_Sphere function. The former is suitable for all versions, and the latter provides easier and more accurate spherical distance calculations since 5.7.
