SQLite: EXISTS Condition
This SQLite tutorial 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');
Advertisements