Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

EXISTS Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Exists Operator
MySQL Exists Operator
The EXISTS operator in MySQL checks for the existence of a record in a table. It's used in the WHERE clause of a SELECT statement to verify if a subquery returns any rows. It returns TRUE if the subquery returns at least one record, else false.
We can also use the operator with the SQL statements such as SELECT, INSERT, UPDATE, and DELETE to verify the existence of the records in subqueries.
Syntax
Following is the syntax of the EXISTS operator in MySQL 
SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);
Example
Before performing the EXISTS operator, let us first two different tables named CUSTOMERS and CARS. Here, we are creating the CUSTOMERS table
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
The following query uses INSERT INTO statement to add 7 records into the above-created table 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Execute the following query to fetch all the records present in the CUSTOMERS table 
SELECT * FROM CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Let us create another table named CARS, which contains the details such as ID of the customer, NAME and PRICE of the car 
CREATE TABLE CARS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY (ID)
);
The following query inserts 3 records into the above-created table 
INSERT INTO CARS (ID, NAME, PRICE) VALUES
(2, 'Maruti Swift', 450000),
(4, 'VOLVO', 2250000),
(7, 'Toyota', 2400000);
Execute the below query to fetch all the records present in the CARS table 
SELECT * FROM CARS;
Following is the CARS table 
ID
NAME
PRICE
2
Maruti Swift
450000
4
VOLVO
2250000
7
Toyota
2400000
EXISTS operator with SELECT statement
The SELECT statement in MySQL is used to retrieve data from one or more tables. The EXISTS operator can be used with the SELECT statement to check if rows exist that match a specific condition.
Example
Now, let us fetch the list of the customers with the price of the car greater than 2,000,000 
SELECT * FROM CUSTOMERS 
WHERE EXISTS 
(SELECT PRICE FROM CARS 
WHERE CARS.ID = CUSTOMERS.ID 
AND PRICE > 2000000);
Output
On executing the given query, the output is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
4
Chaitali
25
Mumbai
6500.00
7
Muffy
24
Indore
10000.00
EXISTS Operator with UPDATE statement
The MySQL EXISTS operator can be used with the UPDATE statement to update the rows in a table based on the existence of rows matching in another table.
Example
In this query, we are using the EXISTS operator to UPDATE the name 'Kushal' to all of the customers whose ID is equal to the ID of the CARS table 
UPDATE CUSTOMERS
SET NAME = 'Kushal'
WHERE EXISTS 
(SELECT NAME FROM CARS 
WHERE CUSTOMERS.ID = CARS.ID);
Output
As we can observe the output, 3 rows have been modified 
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
Verification
To verify whether the changes are reflected in the CUSTOMERS table, execute the following query 
SELECT * FROM CUSTOMERS;
The CUSTOMERS table is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Kushal
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Kushal
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Kushal
24
Indore
10000.00
EXISTS Operator with DELETE statement
The MySQL EXISTS operator is used with the DELETE statement to delete the rows in a table based on the existence of rows returned by a subquery.
Example
Here, we are deleting all the records from the CUSTOMERS table whose ID is equal to the ID in the CARS table having a price equal to 2,250,000 
DELETE FROM CUSTOMERS
WHERE EXISTS 
(SELECT * FROM CARS 
WHERE CARS.ID = CUSTOMERS.ID 
AND CARS.PRICE = 2250000);
Output
As we can observe the output, 1 row has been deleted 
Query OK, 1 row affected (0.00 sec)
Verification
We can verify whether the changes have been reflected in the CUSTOMERS table using the following query 
SELECT * FROM CUSTOMERS;
Output
The output for the query above is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
NOT Operator with EXISTS Operator
If we use the NOT with EXISTS operator in MySQL, it will select records from one table that do not exist in another table.
Syntax
Following is the syntax of the NOT EXISTS operator in MySQL 
SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);
Example
In the following query, we are fetching the NAME of the customers who have not bought any car 
SELECT * FROM CUSTOMERS
WHERE NOT EXISTS 
(SELECT * FROM CARS 
WHERE CUSTOMERS.ID = CARS.ID);
Output
The output for the query above is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
3
Kaushik
23
Kota
2000.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
Exists Operator Using a Client Program
In addition to verify whether a particular record exists in a MySQL table with a MySQL query, you can also use a client program to perform the EXISTS operation.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To verify whether a particular record exists in a MySQL table through a PHP program, we need to execute SELECT statement with EXISTS operator using the mysqli function query() as follows 
$sql = "SELECT column1, column2, ... FROM table_name 
WHERE EXISTS (subquery)";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",   
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Table records:
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 7, Name: Muffy, Age: 24, Address Delhi, Salary 10000.000000   
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Exists Operator

MySQL Exists Operator

Syntax

Example

EXISTS operator with SELECT statement

Example

Output

EXISTS Operator with UPDATE statement

Example

Output

Verification

EXISTS Operator with DELETE statement

Example

Output

Verification

Output

NOT Operator with EXISTS Operator

Syntax

Example

Output

Exists Operator Using a Client Program

Syntax

Example

Output