MySQL – Exists Operator
Table of content
- MySQL Exists Operator
- EXISTS Operator with SELECT statement
- EXISTS Operator with UPDATE statement
- EXISTS Operator with DELETE statement
- NOT Operator with EXISTS Operator
- Exists Operator Using a Client Program
”;
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 −
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);
To verify whether a particular record exists in a MySQL table through a Node.js program, we need to execute SELECT statement with EXISTS operator using the query() function of the mysql2 library as follows −
sql= "SELECT column1, column2, ... FROM table_name WHERE EXISTS (subquery)"; con.query(sql);
To verify whether a particular record exists in a MySQL table through a Java program, we need to execute SELECT statement with EXISTS operator using the JDBC function executeUpdate() as follows −
String sql = "SELECT column1, column2, ... FROM table_name WHERE EXISTS (subquery)"; statement.executeQuery(sql);
To verify whether a particular record exists in a MySQL table through a Python program, we need to execute SELECT statement with EXISTS operator using the execute() function of the MySQL Connector/Python as follows −
exists_query = "SELECT column1, column2, ... FROM table_name WHERE EXISTS (subquery)" cursorObj.execute(exists_query);
Example
Following are the programs −
$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
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating a Database sql = "create database TUTORIALS" con.query(sql); //Select database sql = "USE TUTORIALS" con.query(sql); //Creating CUSTOMERS table sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));" con.query(sql); //Inserting Records sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',4000.00),(2,''Khilan'', 25, ''Kerala'', 8000.00),(3,''kaushik'', 23, ''Hyderabad'', 11000.00),(4,''Chaital'', 25, ''Mumbai'', 1200.00),(5,''Hardik'', 27, ''Vishakapatnam'', 10000.00),(6, ''Komal'',29, ''Vishakapatnam'', 7000.00),(7, ''Muffy'',24, ''Delhi'', 10000.00);" con.query(sql); //Creating CARS table sql = "CREATE TABLE CARS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,PRICE INT NOT NULL,PRIMARY KEY (ID));" con.query(sql); //Inserting Records sql = "INSERT INTO CARS VALUES(2, ''Maruti Swift'', 450000),(4, ''VOLVO'', 2250000),(7, ''Toyota'', 2400000);" con.query(sql); //Using EXISTS Operator sql = "SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);" con.query(sql, function(err, result){ if (err) throw err console.log(result) }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { ID: 4, NAME: ''Chaital'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: ''1200.00'' }, { ID: 7, NAME: ''Muffy'', AGE: 24, ADDRESS: ''Delhi'', SALARY: ''10000.00'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ExistsOperator { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000)"; rs = st.executeQuery(sql); System.out.println("Table records: "); while(rs.next()) { String id = rs.getString("Id"); String name = rs.getString("Name"); String age = rs.getString("Age"); String address = rs.getString("Address"); String salary = rs.getString("Salary"); System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Table records: Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00 Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) cursorObj = connection.cursor() exists_query = f""" SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000); """ cursorObj.execute(exists_query) # Fetching all the rows that meet the criteria filtered_rows = cursorObj.fetchall() for row in filtered_rows: print(row) cursorObj.close() connection.close()
Output
Following is the output of the above code −
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00'')) (7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))
”;