SQL Placements
Date: July 30, 2022
Nested
- 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
);
Inline
- 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;
Scalar
- 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.
Leave a Reply