Skip to main content

Subqueries in SQL

Subqueries in SQL

Sub query are written to find the unknown valuessub-queries

SELECT first_name,Salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE first_name='Lex' );

Guidelines for Using Subqueries

  • Enclose subqueries in parentheses.
  • Place subqueries on the right side of the comparison condition for readability
  • Use single-row operators with single-row subqueries
  • Subqueries that return more than one row can only be used with multiple value operators, such as the IN and ANY operator

Types of Subqueries

Single-row subquery: Return Zero or one row.
Multiple-row subquery: Return more than one row

Single-Row Subqueries

  • Return zero or one row.

Query:Display employees whose job_id same as Ernst and earning less then Ernst.

SELECT last_name, job_id, salary
FROM employees
WHERE job_id= (SELECT job_id FROM employees WHERE last_name='Ernst')
AND   salary <(SELECT salary FROM employees WHERE last_name='Ernst')

Using Group Functions in a Subquery

SELECT last_name, job_id, salary
FROM employees
WHERE salary =(SELECT MAX(salary) FROM employees);

Multiple-Row Sub-queries

  • Return more than one row
Operator Description
IN Equal to any member in the list
ANY Must be preceded by =, !=, >, <, <=, >=.
Compares a value to each value in a list or returned by a query.
Evaluates to FALSE if the query returns no rows.
ALL Must be preceded by =, !=, >, <, <=, >=.
Compares a value to every value in a list or returned by a query.
Evaluates to TRUE if the query returns no rows.
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')