SQL Placements

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.