SQLite EXISTS Condition

This SQLite post explains how to use the SQLite EXISTS condition with syntax and examples.

Description

The SQLite EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in SQLite is

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

A SELECT statement that usually starts with SELECT * rather than a list of expressions or column names.

Note

• SQL statements that use the EXISTS Condition in SQLite are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.

Example - With SELECT Statement

Let's look at a simple example.

The following is a SELECT statement that uses the SQLite EXISTS condition:

SELECT *
FROM departments
WHERE EXISTS (SELECT *
              FROM employees
              WHERE departments.department_id = employees.department_id);

This SQLite EXISTS condition example will return all records from the departments table where there is at least one record in the employees table with the matching department_id.

Example - With SELECT Statement using NOT EXISTS

The SQLite EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM departments
WHERE NOT EXISTS (SELECT *
                  FROM employees
                  WHERE departments.department_id = employees.department_id);

This SQLite EXISTS example will return all records from the departments table where there are no records in the employees table for the given department_id.

Example - With INSERT Statement

The following is an example of an INSERT statement that uses the SQLite EXISTS condition:

INSERT INTO active_departments
(active_department_id, active_department_name)
SELECT department_id, department_name
FROM departments
WHERE EXISTS (SELECT *
              FROM employees
              WHERE departments.department_id = employees.department_id);

Example - With UPDATE Statement

The following is an example of an UPDATE statement that uses the SQLite EXISTS condition:

UPDATE employees
SET hire_date = (SELECT departments.start_date
                 FROM departments
                 WHERE departments.department_id = employees.department_id
                 AND departments.department_name = 'HR')
WHERE EXISTS (SELECT *
              FROM departments
              WHERE departments.department_id = employees.department_id
              AND departments.department_name ='HR');

Example - With DELETE Statement

The following is an example of a DELETE statement that uses the SQLite EXISTS condition:

DELETE FROM employees
WHERE EXISTS (SELECT *
              FROM departments
              WHERE employees.department_id = departments.department_id
              AND departments.department_name = 'IT');