Date: July 30, 2022
- query within another query embedded within the WHERE clause
- when a user wants to filter an output using a condition met from another table
- advantageous for readability
Use Case for a Nested Subquery
- When a user wants to filter an output using a condition met from another table.
- This type of placement also has advantages for making the code easy to read.
SELECT * FROM students WHERE student_id IN (SELECT DISTINCT student_id FROM gpa_table WHERE gpa>3.5 );
- component of the FROM clause taking the place of a table name
- very similar use cause to WITH subqueries. Creating a “pseudo table” that aggregates or manipulates an existing table to be used in a larger query.
- not as advantageous for readability
Use Case for Inline Subquery
- It is a very similar use case to ‘With’ subqueries.
- Inline subqueries create a “pseudo table” that aggregates or manipulates an existing table to be used in a larger query.
- The disadvantage of the inline subquery is that it is not easy to read.
SELECT dept_name, max_gpa FROM department_db x (SELECT dept_id MAX(gpa) as max_gpa FROM students GROUP BY dept_id )y WHERE x.dept_id = y.dept_id ORDER BY dept_name;
- selects only one column or expression and returns one row, used in the SELECT clause of the main query
- advantageous for performance or when the data set is small
- If a scalar subquery does not find a match, it returns a NULL.
- If a scalar subquery finds multiple matches, it returns an ERROR.
SELECT (SELECT MAX(salary) FROM employees_db) AS top_salary, employee_name FROM employees_db;
- It is ideal to use when you need an operand for an expression.
- A valid scalar subquery results in a single column value for a single row of output.
- A scalar subquery WILL NOT create a valid result if its output includes multiple rows.
- It WILL NOT throw an error if it’s output includes 0 rows.