Khóa học miễn phí MySQL – EXISTS Operator nhận dự án làm có lương

MySQL – Exists Operator

Table of content


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''))   

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc