SQLite Subqueries
Introduction
A subquery is a SQL query nested inside a larger query.
- A subquery may occur in :
- - A SELECT clause
- - A FROM clause
- - A WHERE clause
- A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
- You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, NOT IN
- A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
- The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.
Table of contents
Subqueries : Guidelines and Types of Subqueries
SQLite Subquery as Scalar Operand
SQLite Subqueries : Using Comparisons
SQLite Subqueries with IN, NOT IN
SQLite Subqueries in the FROM Clause
Subquery Syntax:
- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.
SQLite Subquery Example:
Using a subquery, list the name of the employees, paid more than 'Alexander' (first_name) from employees :
Sample Table: employees
Code:
sqlite> SELECT first_name,last_name, salary
FROM employees
WHERE salary >(
SELECT salary FROM employees WHERE first_name='Alexander');
Output:
first_name last_name salary ---------- ---------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 Nancy Greenberg 12000 Den Raphaely 11000 John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Peter Tucker 10000 David Bernstein 9500 Janette King 10000 Patrick Sully 9500 Clara Vishney 10500 Danielle Greene 9500 Lisa Ozer 11500 Harrison Bloom 10000 Tayler Fox 9600 Ellen Abel 11000 Michael Hartstein 13000 Hermann Baer 10000 Shelley Higgins 12000
Subqueries: Guidelines
There are some guidelines to consider when using subqueries:
- A subquery must be enclosed in parentheses.
- Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries.
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
Types of Subqueries
- The Subquery as Scalar Operand
- Comparisons using Subqueries
- Subqueries with IN, NOT IN, EXISTS operator
- Correlated Subqueries
- Subqueries in the FROM Clause
SQLite Subquery as Scalar Operand
A SELECT statement enclosed in parentheses may appear as a scalar quantity. A scalar subquery is a subquery that returns exactly one column value from one row.
- The SELECT query is executed and the single returned value is used in the surrounding value expression.
- It is an error to use a query that returns more than one row or more than one column as a scalar subquery.
- During a particular execution, if the subquery returns no rows, that is not an error; the scalar result is taken to be null.
- The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.
Example:
sqlite> SELECT employee_id, last_name,
(CASE WHEN department_id=( SELECT department_id from departments
WHERE location_id=2500) THEN 'Canada'
ELSE 'USA' END) location
FROM employees;
Output:
employee_id last_name location ----------- ---------- ---------- 100 King USA 101 Kochhar USA 102 De Haan USA 103 Hunold USA 104 Ernst USA 105 Austin USA 106 Pataballa USA 107 Lorentz USA ----------- ---------- ---------- ----------- ---------- ----------
SQLite Subqueries: Using Comparisons
A subquery can be used before or after any of the comparison operators. The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. SQL then compares the value that results from the subquery with the value on the other side of the comparison operator. You can use the following comparison operators:
| Operator | Description |
|---|---|
| = | Test for equality. |
| == | Test for equality. |
| > | Greater than. |
| < | Less than. |
| >= | Greater than equal to. |
| <= | Less than equal to. |
| <> | Test for inequality. |
| != | Test for inequality. |
For example, suppose you want to find the employee id, first_name, last_name, and salaries for employees whose average salary is higher than the average salary throughout the company.
sqlite> SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(SALARY) FROM employees);
Output :
employee_id first_name last_name salary ----------- ---------- ---------- ---------- 100 Steven King 24000 101 Neena Kochhar 17000 102 Lex De Haan 17000 103 Alexander Hunold 9000 108 Nancy Greenberg 12000 109 Daniel Faviet 9000 110 John Chen 8200 ----------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
SQLite Subqueries with IN operator
Syntax:
expression IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result.
- The result of IN is true if any equal subquery row is found.
- The result is "false" if no equal row is found (including the case where the subquery returns no rows).
- If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false.
Example: SQLite Subquery, IN operator
The following query selects those employees who work in the location 1700. The subquery finds the department id in the 1700 location, and then the main query selects the employees who work in any of these departments.
Sample Table: employees
Code:
sqlite> SELECT first_name, last_name,department_id
...> FROM employees
...> WHERE department_id IN
...> (SELECT DEPARTMENT_ID FROM departments
...> WHERE location_id=1700);
Output:
first_name last_name department_id ---------- ---------- ------------- Steven King 90 Neena Kochhar 90 Lex De Haan 90 Nancy Greenberg 100 Daniel Faviet 100 John Chen 100 Ismael Sciarra 100 Jose Manue Urman 100 Luis Popp 100 Den Raphaely 30 Alexander Khoo 30 Shelli Baida 30 Sigal Tobias 30 Guy Himuro 30 Karen Colmenares 30 Jennifer Whalen 10 Shelley Higgins 110 William Gietz 110)
SQLite Subqueries with NOT IN operator
Syntax:
expression NOT IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result.
- The result of NOT IN is true if any equal subquery row is found.
- The result is "false" if no equal row is found (including the case where the subquery returns no rows).
- If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not false.
Example: SQLite Subquery, NOT IN operator
The following query selects those employees who do not work under the manager, whose id is between 100 and 200. The subquery finds the department id which is under the manager whose id is between 100 and 200, and then the main query selects the employees who do not work in any of these departments.
Sample Table : employees
Sample Table: departments
Code:
sqlite> SELECT first_name, last_name,department_id
...> FROM employees
...> WHERE department_id NOT IN
..> (SELECT DEPARTMENT_ID FROM departments
...> WHERE manager_id
...> BETWEEN 100 AND 200);
Output:
first_name last_name department_id ---------- ---------- ------------- Kimberely Grant Michael Hartstein 20 Pat Fay 20 Susan Mavris 40 Hermann Baer 70 Shelley Higgins 110 William Gietz 110
SQLite Subqueries with EXISTS operator
Syntax:
EXISTS (subquery)
The argument of EXISTS is an arbitrary SELECT statement or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is true; if the subquery returns no rows, the result of EXISTS is false.
Example: SQLite Subqueries with EXISTS
From the following tables (employees) find employees (employee_id, first_name, last_name, job_id, department_id) who have at least one person reporting to them.
Sample table: employees table
Code:
sqlite> SELECT employee_id, first_name, last_name, job_id, department_id
...> FROM employees E
...> WHERE EXISTS
...> (SELECT * FROM employees
...> WHERE manager_id = E.employee_id);
Output:
employee_id first_name last_name job_id department_id ----------- ---------- ---------- ---------- ------------- 100 Steven King AD_PRES 90 101 Neena Kochhar AD_VP 90 102 Lex De Haan AD_VP 90 103 Alexander Hunold IT_PROG 60 108 Nancy Greenberg FI_MGR 100 114 Den Raphaely PU_MAN 30 120 Matthew Weiss ST_MAN 50 121 Adam Fripp ST_MAN 50 122 Payam Kaufling ST_MAN 50 123 Shanta Vollman ST_MAN 50 124 Kevin Mourgos ST_MAN 50 145 John Russell SA_MAN 80 146 Karen Partners SA_MAN 80 147 Alberto Errazuriz SA_MAN 80 148 Gerald Cambrault SA_MAN 80 149 Eleni Zlotkey SA_MAN 80 201 Michael Hartstein MK_MAN 20 205 Shelley Higgins AC_MGR 110
SQLite Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table (in the parent query) that also appears in the outer query. SQLite evaluates from inside to outside.
Correlated subquery syntax:
Example - 1: SQLite Correlated Subqueries
Following query find all employees who earn more than the average salary in their department.
employees table:
Code:
sqlite> SELECT last_name, salary, department_id
FROM employees outerr
WHERE salary >(SELECT AVG(salary)
FROM employees
WHERE department_id = outerr.department_id);
Output:
last_name salary department_id ---------- ---------- ------------- King 24000 90 Hunold 9000 60 Ernst 6000 60 Greenberg 12000 100 Faviet 9000 100 Raphaely 11000 30 Weiss 8000 50 Fripp 8200 50 Kaufling 7900 50 Vollman 6500 50 Mourgos 5800 50 Ladwig 3600 50 Rajs 3500 50 Russell 14000 80 Partners 13500 80 Errazuriz 12000 80 Cambrault 11000 80 Zlotkey 10500 80 Tucker 10000 80 Bernstein 9500 80 Hall 9000 80 King 10000 80 Sully 9500 80 McEwen 9000 80 Vishney 10500 80 Greene 9500 80 Ozer 11500 80 Bloom 10000 80 Fox 9600 80 Abel 11000 80 Sarchand 4200 50 Bull 4100 50 Chung 3800 50 Dilly 3600 50 Bell 4000 50 Everett 3900 50 Hartstein 13000 20 Higgins 12000 110
Example - 2: SQLite Correlated Subqueries
From the employees and job_history tables find details of those employees who have changed jobs at least once.
Sample table: employees table:
Sample table: job_history table:
Code:
sqlite> SELECT first_name, last_name, employee_id, job_id
...> FROM employees E
...> WHERE 1 <=
...> (SELECT COUNT(*) FROM Job_history
...> WHERE employee_id = E.employee_id);
output:
first_name last_name employee_id job_id ---------- ---------- ----------- ---------- Neena Kochhar 101 AD_VP Lex De Haan 102 AD_VP Den Raphaely 114 PU_MAN Payam Kaufling 122 ST_MAN Jonathon Taylor 176 SA_REP Jennifer Whalen 200 AD_ASST Michael Hartstein 201 MK_MAN
SQLite Subqueries in the FROM Clause
Subqueries work in a SELECT statement's FROM clause. The syntax is :
SELECT ... FROM (subquery) [AS] name ...
Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any columns in the subquery select list must have unique names.
Example: SQLite Subqueries in the FROM Clause
We have the following table tb1.
sqlite> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
Let insert some values into tb1.
sqlite> INSERT INTO tb1 VALUES (1, '1', 1.0); sqlite> INSERT INTO tb1 VALUES (2, '2', 2.0); sqlite> INSERT INTO tb1 VALUES (3, '3', 3.0); sqlite> select * from tb1; c1 c2 c3 ---------- ---------- ---------- 1 1 1.0 2 2 2.0 3 3 3.0
Here is how to use a subquery in the FROM clause, using the example table (tb1) :
sqlite> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
sc1 sc2 sc3
---------- ---------- ----------
2 2 6.0
3 3 9.0
Previous:
NATURAL JOIN
Next:
Triggers
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics