- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are single row and multiple row subqueries?
Single Row Sub Query
A single-row subquery is used when the outer query's results are based on a single, unknown value. Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.
In the below SELECT query, inner MySQL returns only one row i.e. the minimum salary for the company. It, in turn, uses this value to compare the salary of all the employees and displays only those, whose salary is equal to minimum salary.
SELECT first_name, salary, department_id FROM employees WHERE salary = (SELECT MIN (salary) FROM employees);
A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery's result must be compared with a group function, you must nest the inner query in the outer query's HAVING clause.
SELECT department_id, MIN (salary) FROM employees GROUP BY department_id HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)
Multiple Row Sub Query
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).While IN operator holds the same meaning as discussed in the earlier chapter, ANY operator compares a specified value to each value returned by the subquery while ALL compares a value to every value returned by a subquery. The below query will show the error because single-row subquery returns multiple rows.
SELECT first_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
- Update multiple columns of a single row MySQL?
- Concatenate multiple rows and columns in a single row with MySQL
- Single-Row Keyboard in python
- Selecting a single row in MySQL?
- How to use COUNT(*) to return a single row instead of multiple?
- What are connected and disconnected Row Sets in JDBC?
- Count multiple rows and display the result in different columns (and a single row) with MySQL
- How to multiply row values in a data frame having multiple rows with single row data frame in R?
- What are MySQL subqueries and its general categories?
- MySQL LIMIT to select a single row
- MySQL query to get the highest value from a single row with multiple columns
- Sum values of a single row in MySQL?
- How to multiply corresponding row values in a matrix with single row matrix in R?
- How to multiply single row matrix and a square matrix in R?
- MySQL query to select one specific row and another random row?