w3resource

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

Subquery Syntax

SQLite Subquery Example

Subqueries : Guidelines and Types of Subqueries

SQLite Subquery as Scalar Operand

SQLite Subqueries : Using Comparisons

SQLite Subqueries with IN, NOT IN

SQLite Subqueries with EXISTS

SQLite Correlated Subqueries

SQLite Subqueries in the FROM Clause

Subquery Syntax:

mysql 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


sqlite subquery example

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.

mysql comparison operator
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:

SQLite Correlated Subqueries - w3resource

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sqlite/sqlite-subqueries.php