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.
This entry was posted in Study Notes and tagged . Bookmark the permalink.

Leave a Reply