MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
A Subquery (also called an Inner Query or Nested Query) is a SELECT query embedded inside another SQL statement. Subqueries allow you to compute dynamic intermediate values on the fly without running multiple separate queries from your application code.
Depending on the cardinality of results returned by the inner query, subqueries are classified into Single-Row Subqueries and Multi-Row Subqueries.
A Single-Row Subquery returns at most one column and one row (a single scalar value). Because it produces a single value, it can be used with standard scalar comparison operators (=, >, <, >=, <=, <>):
SELECT AVG(salary) FROM employees) executes once, returning, for example, 62000.00.WHERE salary > 62000.00.= returns more than one row, MySQL halts with error: Error 1242: Subquery returns more than 1 row!IN, ANY, and ALLWhen an inner query can return multiple rows, scalar operators (=, >) cannot be used directly. You must use multi-row operators: IN, ANY, or ALL.
IN Operator with SubqueriesANY (or SOME) OperatorThe ANY operator compares a scalar value to each value returned by the subquery and evaluates to TRUE if at least one comparison succeeds:
> ANY (subquery): Greater than the minimum value of the subquery.< ANY (subquery): Less than the maximum value of the subquery.= ANY (subquery): Exactly identical to IN!ALL OperatorThe ALL operator compares a scalar value to all values returned by the subquery and evaluates to TRUE only if every single comparison succeeds:
> ALL (subquery): Greater than the maximum value of the subquery.< ALL (subquery): Less than the minimum value of the subquery.<> ALL (subquery): Exactly identical to NOT IN!WHERE id IN (SELECT id ...) subqueries into internal Semi-Joins, executing them with the same speed as an INNER JOIN.WHERE salary = (...)) returns 3 rows?= ANY (subquery) completely identical to in SQL?[40000, 60000, 80000], what does the condition WHERE salary > ALL (subquery) require?> ANY (subquery) evaluate to when the subquery returns [10, 25, 50]?Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Emulating EXCEPT / MINUS in MySQL | Subqueries in SELECT, FROM & WHERE |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.