Syntax of the Exists Operator in SQLite
The SQLite Exists operator is used with a select statement in the following syntax.
FROM Table_Name
WHERE EXISTS
(SELECT Column_Names,....n
FROM Table_Name
WHERE condition);
The EXISTS operator takes the SELECT statement as a parameter and is referred to as an EXISTS subquery. Instead of a collection of statements or column names, a SELECT statement normally begins with SELECT *. Note that in SQLite, queries with the EXISTS operator in SQLite are wasteful because, for each row in the table of the outer query, the sub-query is re-run. Most queries can be implemented more efficiently if the EXISTS Condition is not used.
Create Tables for Performing SQLite Exists Operation
We have created two tables in the database of SQLite3. The first table is given the name “Customer” by creating a query of SQLite. We have set the column names for this table inside the create query. We have four columns here ID, NAME, PHONE, and EMAIL with the mentioned data types. Here, field ID is the primary key, as denoted in the figure.
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
PHONE INT NOT NULL,
EMAIL TEXT NOT NULL
);
Then, with the SQLite INSERT query, we have input the values for the above-defined columns. We have inserted five rows with the specific values.
INSERT INTO Customer VALUES (2, 'Haris', 923890321, '[email protected]' );
INSERT INTO Customer VALUES (3, 'Jannet', 922904852, '[email protected]');
INSERT INTO Customer VALUES (4, 'Sofia', 923089024, '[email protected]');
INSERT INTO Customer VALUES (5, 'Mansoor', 9223417787, '[email protected]' );
We have retrieved the record in the table “Customer” by the SELECT SQLite query as mentioned below.
We have created our second table, which is represented as “Product”. The columns for the table “Product” are ID, PRODUCT_NAME, PRODUCT_QUANTITY, and PRODUCT_PRICE with the data type assigned.
After creating the table and defining its column names, we have inserted the record for the table “Product”.Each column has its values inserted, and we have a record of five rows for this table.
INSERT INTO Product VALUES (2, 'Shoes', '5', 3600);
INSERT INTO Product VALUES (3, 'Brushes', 50, 270);
INSERT INTO Product VALUES (4, 'Tissues', 7, 100);
INSERT INTO Product VALUES (5, 'Soaps', 9, 224);
INSERT INTO Product VALUES (6, 'Laptop', 1, 50000);
We have called a SELECT with the Asterisk “*” symbol to view the table and its record. It displays the whole record stored in the table. The figure displays the record, which we have inserted in the table “Product”. AS tables are created now; let’s use the EXISTS operator on these tables.
Example 1: Program of Using SQLite Exists Operator With the SELECT Statement
We have used the EXIST operator with the SELECT* expression. Firstly, we have given a query that selects the entire data from the table Product; then, with the where clause, we have specified the EXIST operator and passed a subquery as a parameter. The subquery is the select statement that selects the whole record from the table customer and gives the WHERE condition that the ID from the table product is equal to the ID from the table Customer. It returns all the records from the table product where the product ID matched at least one entry in the Customer table.
FROM Product
WHERE EXISTS (SELECT *
FROM Customer
WHERE Product.ID = Customer.ID);
We have retrieved the data of the table Product easily because the ID of both the tables is matched. The output is displayed in the SQLite shell.
Example 2: Program of Using SQLite NOT Exists Operator With the SELECT Statement
The NOT operator is utilized with the EXISTS operator in this case. In the following example, we have selected the entire record from the table Product and used the NOT EXISTS operator. The NOT EXISTS operator has the select expression as a parameter with the where condition. If the condition matched, this will only show the distinct record from the table Product.
FROM Product
WHERE NOT EXISTS (SELECT *
FROM Customer
WHERE Product.ID= Customer.ID);
The record of the table product shown in the figure is not matched with the customer table. Hence, we get this as an output.
Example 3: Program of Using SQLite Exists Operator With the UPDATE Statement
We have an UPDATE query where we have set the column PHONE of the table Customer. We have set the PHONE column with the PRODUCT_PRICE from the table Product by applying the WHERE condition. Then, we have an EXISTS operator and pass a subquery of the SELECT statement, which selects the record from the table Product where the ID of both the tables matched.
SET PHONE = (SELECT Product.PRODUCT_PRICE
FROM Product
WHERE Product.ID = Customer.ID
AND Product.PRODUCT_NAME = 'Bags')
WHERE EXISTS (SELECT *
FROM Product
WHERE Product.ID = Customer.ID
AND Product.PRODUCT_NAME = 'Bags');
The results executed from the EXISTS query have updated the table Customer successfully. You can notice in the figure that in the column PHONE, the PRODUCT_PRICE is set.
Example 4: Program of Using SQLite Exists Operator With the DELETE Statement
We are using the DELETE statement with the EXISTS operator to delete the record of a single row from the given table. In the following query, we have a DELETE statement first, to which we have given a table named Customer. Then, utilize the EXISTS operator where we have given a subquery that selects records from the table Product where the ID is equal to the ID of the table Product and the PRODUCT_NAME is “Shoes” of the table Product.
WHERE EXISTS (SELECT *
FROM Product
WHERE Customer.ID = Product.ID
AND Product.PRODUCT_NAME = 'Shoes');
The row is deleted successfully if the figure has ID ‘2’.
Conclusion
Summarize the SQLite EXISTS operator article here. We have discussed in deep the usage of the SQLite EXISTS operator. Firstly, we have the introduction where we have to define the EXISTS operator. Then, with its syntax demonstration, we have seen how to utilize the EXISTS operator with SQLite Queries. At last, we have a simple example that uses the EXISTS operator and the SELECT, UPDATE, and DELETEs statements.
