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

MySQL – IN Operator

Table of content

MySQL In Operator

The IN operator in MySQL is a logical operator that allows us to check whether the values in a database are present in a list of values specified in the SQL statement.

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. It returns all rows in which the specified column value matches any one of the values in the list.

The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions.

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. You can use IN clause to replace many OR conditions

Syntax

Following is the basic syntax of IN operator −

WHERE COLUMN_NAME IN (value1, value2, value3,....);

Example

To understand IN clause, let us first create a table named CUSTOMERS, using the following CREATE TABLE statement −

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

Now, insert the following records using the INSERT statement −

INSERT INTO CUSTOMERS 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);

The table will be created as follows −

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

Using the following query, retrieve the records with the names ‘Khilan’, ‘Hardik’, ‘Muffy’, from the CUSTOMERS table −

SELECT * FROM CUSTOMERS
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

Output

The output obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

The IN Operator in UPDATE statement

The MySQL UPDATE statement is used to modify existing data in a database table. So, we can also use the IN operator in an UPDATE statement (as a filter) to update existing rows.

Example

In this example, let us update the records of the customers with age ‘25’ or ‘27’ by setting their value to ‘30’ −

UPDATE CUSTOMERS
SET AGE = 30 WHERE AGE IN (25, 27);

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Use the following query to display the updated records in the CUSTOMERS table −

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

MySQL NOT IN operator

To negate a condition, we use the NOT operator. The MySQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause.

In other words, the absence of a list from an expression will be checked.

Syntax

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2,...);

Example

Now, we are trying to display all the records from the CUSTOMERS table, where the AGE is NOT equal to ”25”, ”23” and ”22” −

SELECT * FROM CUSTOMERS
WHERE AGE NOT IN (25, 23, 22);

Output

The output is obtained as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Comparing Values Using IN Operator

We can also use the IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

In the below query, we are trying to select the rows with the values containing SALARY column −

SELECT * FROM CUSTOMERS
WHERE 2000 IN (SALARY);

Output

The following output is obtained −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

MySQL Subquery with IN operator

We can use a subquery with the IN operator to return records from a single column. This means that more than one column in the SELECT column list cannot be included in the subquery specified.

Syntax

The basic syntax of the IN operator to specify a query is as follows −

WHERE column_name IN (subquery);

Example

In the query given below we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

SELECT * FROM CUSTOMERS
WHERE NAME IN (
   SELECT NAME FROM CUSTOMERS
   WHERE SALARY > 2000
);

Output

The following output is obtained −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

In Operator Using Client Program

We can execute IN operator using a client program, in addition to executing it directly in the MySQL server.

Syntax

Following are the syntaxes of the IN Operator using various programming languages −

To use IN operator in MySQL table through PHP program, we need to execute the SQL statement with IN using the function named query() provided by as mysqli connector −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE COLUMN_NAME IN (VALUE1, VALUE2, ...)";
$mysqli->query($sql);

To use IN operator in MySQL table through JavaScript program, we need to execute the SQL statement with IN using the function named query() provided by mysql2 connector −

sql= "SELECT columns FROM table_name
WHERE column_name IN (value1, value2, value3, ...)";
con.query(sql);

To use IN operator in MySQL table through Java program, we need to execute the SQL statement with IN using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT COLUMN(S) FROM TABLE_NAME
WHERE COLUMN_NAME IN(VALUE1, VALUE2, VALUE3,...)";
statement.executeQuery(sql);

To use IN operator in MySQL table through Python program, we need to execute the SQL statement with IN using the function named execute() provided by MySQL Connector/Python −

in_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name IN (value1, value2, value3, ...)"
cursorObj.execute(in_query);

Example

Following are the implementations of this operation in various programming languages −

$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 tutorials_tbl WHERE tutorial_author IN(''John'', ''Sanjay'', ''Mahesh'')";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 3, Title: JAVA Tutorial, Author: Sanjay, S_date 2007-05-21
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26

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 table
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using IN Operator
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages IN ( 250, 220, 170 );"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    id: 1,
    name: ''John'',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: ''Ram'',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: ''Jack'',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: ''Jill'',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InOperator {
    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 = "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))";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //now lets insert some records
            String sql1 = "INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //display records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records before update: ");
            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);
            }
            //let use IN operator with where clause to update records
            String sql3 = "UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27)";
            st.executeUpdate(sql3);
            //display table records after update
            String sql4 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("Table records before update: ");
            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 created successfully...!
Records inserted successfully....!
Table records before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00
Table records before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.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''
)
# Creating a cursor object
cursorObj = connection.cursor()
in_query = f"""SELECT * FROM customers
WHERE id IN (2, 4, 6)"""
cursorObj.execute(in_query)
# Fetching all 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 −

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))

mysql-useful-functions.htm

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

August 17, 2024

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

MySQL – OR Operator

Table of content

MySQL OR Operator

MySQL does not have a built-in Boolean data type. Instead, Boolean values are represented using numeric data types, where zero is used as false and any non-zero value is used as true.

The MySQL OR operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:

A AND B

Here, A and B are operands.

The OR operator will return true (1) only if either A or B, or both, is non-zero and not Null.
If both A and B are false, the OR operator will return false (0).
If either A or B is NULL, the OR operator will return NULL.

The following table below demonstrates the possible outcomes of using the OR operator to combine true (1), false (0), and null values:

	1	0	NULL
1	1	1	1
0	1	0	NULL
NULL	1	NULL	NULL

Example

The logical OR operator will return true (1) if both A and B are not NULL, and if either A or B is non-zero.

SELECT 1 OR 1, 1 OR 0, 0 OR 1;

Output

The output for the program above is produced as given below −

1 OR 1	1 OR 0	0 OR 1
1	1	1

Example

The OR operator returns false (0) if both A and B are false (0).

SELECT 0 OR 0;

Output

When we execute the above query, the output is obtained as follows −

0 OR 0
0

Example

If A is true (1) and B is NULL, the OR operator will return 1.

If A is false (0) and B is NULL, the OR operator will return NULL.

If both A and B are NULL, the OR operator will return NULL.

SELECT 1 OR NULL, 0 OR NULL, NULL or NULL;

Output

On executing the given program, the output is displayed as follows −

1 OR NULL	0 OR NULL	NULL OR NULL
1	NULL	NULL

OR operator with WHERE

MySQL”s logical OR operator can be used along with the WHERE clause to return the rows that meet any of the specified conditions.

When the OR operator is used, at least one of the conditions must be true for a row to be included in the result set. If none of the conditions are true, an empty set is returned.

Syntax

Following is the syntax of the OR operator with WHERE clause in MySQL −

SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 OR condition2 OR condition3 ...;]

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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 INSERT INTO statement adds 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 retrieve 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

Now, we are selecting all the columns from the CUSTOMERS table where SALARY is greater than 5000 or ADDRESS = “Hyderabad”.

SELECT * FROM CUSTOMERS
WHERE SALARY > 5000
OR ADDRESS = "Hyderabad";

Output

The output for the program above is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example

The logical OR operator returns the records only if either of the conditions separated by OR is true.

In the following query, we are providing false values to both operands of the OR operator.

SELECT * FROM CUSTOMERS
WHERE NAME = "Mahesh" OR AGE = 42;

Output

As there are no records present in the CUSTOMERS table with NAME “Mahesh” or AGE is 42, it returns an empty set as an output.

Empty set (0.00 sec)

Multiple OR Operators

We can use MySQL”s logical OR operator multiple times to combine multiple conditions. By using multiple OR operators, any rows that meet at least one of the conditions will be included in the result set.

Example

In the following query, we are returning all the records from the CUSTOMERS table where the NAME of the customer ends with ”k”, or SALARY is greater than 5000, or AGE is less than 25.

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''%k'' OR SALARY > 5000 OR AGE < 25;

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
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

OR with UPDATE statement

The MySQL”s logical OR operator can be used along with the UPDATE statement to update records of a table based on multiple conditions.

Syntax

Following is the syntax of the OR operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 OR condition2 OR ...;

Example

In the following query, we are updating the SALARY of CUSTOMERS whose ADDRESS is ”Hyderabad” or whose age is greater than 26

UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ADDRESS = "Hyderabad" OR AGE > 26;

Output

The output for the query above is produced as given below −

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

Execute the below query to verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

The output for the program above is produced as given below −

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

OR with DELETE Statement

The MySQL”s logical OR operator can be used along with the DELETE statement to remove records from a table based on multiple conditions.

Syntax

Following is the syntax of OR operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE condition1 OR condition2 OR condition3 ...

Example

In the following query, we are trying to DELETE records from the CUSTOMERS table where the age is less than 25 or the SALARY is less than or equal to 10000.

DELETE FROM CUSTOMERS
WHERE AGE < 25 OR SALARY <= 10000;

Output

Query OK, 5 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above operation is successful or not −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

OR Operator Using a Client Program

Besides using MySQL queries to perform the OR operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the OR Operator on a MySQL table through PHP program, we need to execute SELECT statement with OR operator using the mysqli function query() as follows −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE CONDITION1 OR CONDITION2 OR CONDITION3...";
$mysqli->query($sql);

To perform the OR Operator on a MySQL table through Node.js program, we need to execute SELECT statement with OR operator using the query() function of the mysql2 library as follows −

sql= " SELECT column1, column2, ..., columnN FROM table_name 3
[WHERE condition1 OR condition2 OR condition3 ...]";
con.query(sql);

To perform the OR Operator on a MySQL table through Java program, we need to execute SELECT statement with OR operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
[WHERE CONDITION1 OR CONDITION2 OR CONDITION3...]";
statement.executeQuery(sql);

To perform the OR Operator on a MySQL table through Python program, we need to execute SELECT statement with OR operator using the execute() function of the MySQL Connector/Python as follows −

or_query = "SELECT column1, column2, ... FROM table_name
WHERE condition1 OR condition2 OR ..."
cursorObj.execute(or_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 tutorials_tbl WHERE tutorial_id = 2 OR tutorial_id = 4 OR tutorial_id = 6
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 2, Title: PHP Tut, Author: New Author, S_date 2023-08-12
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26

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 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using OR Operator
  sql = "SELECT * FROM CUSTOMERS WHERE SALARY > 5000 OR ADDRESS = ''Vishakapatnam"
  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: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrOperator {
  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 SALARY > 5000 OR ADDRESS = "Vishakapatnam"";
            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 + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 4, Name: Chaital, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Query to retrieve tutorials
or_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > 5000 OR ADDRESS = "Vishakapatnam""""
cursorObj.execute(or_query)
# Fetch all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.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

August 17, 2024

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

MySQL – AND Operator

Table of content

MySQL AND Operator

In MySQL, there isn”t a built-in Boolean type. Rather, the Boolean values are represented using numeric data types, where zero is considered false and any non-zero value is considered true.

The MySQL AND operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:

A AND B

Here, A and B are operands.

The AND operator will return true (1) only if both A and B are non-zero and not Null.
If either A or B is false, the AND operator will return false (0).
If either A or B is NULL, the AND operator will return NULL.

The following table below demonstrates the possible outcomes of using the AND operator to combine true, false, and null values:

	1	NULL
1	1	NULL
0	0	0
NULL	NULL	NULL

Example

The logical AND operator returns 1 if both A and B are non-zero and NOT NULL −

SELECT 1 AND 1;

Output

The output for the program above is produced as given below −

1 AND 1
1

Example

The logical AND operator returns 0 if either A or B is zero, or if both A and B are zero.

SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL;

Output

When we execute the above query, the output is obtained as follows −

1 AND 0	0 AND 1	0 AND 0	0 AND NULL
0	0	0	0

Example

The logical AND operator returns NULL if at least one operand is non-zero or both operands are NULL −

SELECT 1 AND NULL, NULL AND NULL;

Output

On executing the given query, the output is displayed as follows −

1 AND NULL	NULL AND NULL
NULL	NULL

AND Operator with WHERE

The MySQL AND operator can be used with the WHERE clause to retrieve only the rows that meet all the specified conditions. When the AND operator is used, both conditions must be true for a row to be included in the result set. Else, it returns an empty set.

Syntax

Following is the syntax of the AND operator with WHERE clause in MySQL −

SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 AND condition2 AND condition3 ...;]

Example

Firstly, let us create a MySQL table named CUSTOMERS using the below query −

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

The following query inserts 7 rows 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 from 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

Now, let us select all the columns from the CUSTOMERS table where the ADDRESS is ”Hyderabad” and AGE is 22.

SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Hyderabad" AND AGE = 22;

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Hyderabad	4500.00

Example

The logical AND operator returns the records only if all the conditions separated by AND are true.

In the following query, we are providing a false value to one of the AND operands.

SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Kerala" AND AGE = 27;

Output

As the ADDRESS column in the CUSTOMERS table doesn”t contain the value ”Kerala”, it returns an empty set as an output.

Empty set (0.00 sec)

Multiple AND Operators

In MySQL, we can use multiple AND operators in a query to combine multiple conditions or expressions together. Conditions combined with these multiple ”AND” operators are evaluated from left to right. If any of the conditions evaluate to false, the entire condition will be false and the record will not be included in the result set.

Example

In the following query, we are selecting all records from the CUSTOMERS table where the NAME starts with “k”, AGE is greater than or equal to 22, and SALARY is less than 3742.

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''k%'' AND AGE >= 22 AND SALARY < 3742;

Output

When we execute the program above, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

AND with UPDATE statement

In MySQL, we can use the AND operator in an UPDATE statement to update records from a table based on provided multiple conditions.

Syntax

Following is the syntax of the AND operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 AND condition2 AND ...;

Example

In the following query, we are updating the SALARY of CUSTOMERS whose ID is 5 and ADDRESS is ”Hyderabad” −

UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ID = 6 AND ADDRESS = "Hyderabad";

Output

The output for the query above is produced as given below −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

As we can see the CUSTOMERS table below, the salary of customer with ID 5 has updated −

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	15000.00
7	Muffy	24	Indore	10000.00

AND with DELETE Statement

In MySQL, we can use the AND operator in a DELETE statement to remove records from a table based on multiple conditions.

Syntax

Following is the syntax of AND operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE condition1 AND condition2 AND condition3 ...

Example

In this query, we are deleting records from the CUSTOMERS table where the NAME is equal to ”Khilan” and ADDRESS is equal to ”Delhi” −

DELETE FROM CUSTOMERS
WHERE NAME = "Khilan" AND ADDRESS = "Delhi";

Output

Query OK, 1 row affected (0.01 sec)

Verification

Using the below query, we can verify whether the above operation is successful or not −

SELECT * FROM CUSTOMERS;

Output

As we can see the output below, the customer name with ”khilan” and address ”delhi” has been deleted successfully −

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

AND Operator Using a Client Program

Besides using MySQL queries to perform the AND operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the AND Operator on a MySQL table through PHP program, we need to execute SELECT statement with AND operator using the mysqli function query() as follows −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE CONDITION1 AND CONDITION2 AND CONDITION3...";
$mysqli->query($sql);

To perform the AND Operator on a MySQL table through Node.js program, we need to execute SELECT statement with AND operator using the query() function of the mysql2 library as follows −

sql= " SELECT column1, column2, ..., columnN FROM table_name 3
[WHERE condition1 AND condition2 AND condition3 ...]";
con.query(sql);

To perform the AND Operator on a MySQL table through Java program, we need to execute SELECT statement with AND operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
[WHERE CONDITION1 AND CONDITION2 AND CONDITION3...]";
statement.executeQuery(sql);

To perform the AND Operator on a MySQL table through Python program, we need to execute SELECT statement with AND operator using the execute() function of the MySQL Connector/Python as follows −

and_query = "SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2 AND ..."
cursorObj.execute(and_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 tutorials_tbl WHERE tutorial_id > 2 AND tutorial_id query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 3, Title: JAVA Tutorial, Author: Sanjay, S_date 2007-05-21
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 5, Title: Learn MySQL, Author: Abdul S, S_date 2023-07-26

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 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT * FROM CUSTOMERS WHERE ADDRESS = ''Vishakapatnam'' AND AGE = 27;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Andoperator {
  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 ADDRESS = "Vishakapatnam" AND AGE = 27";
            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 + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Query to retrieve tutorials
and_query = f"""SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Vishakapatnam" AND AGE = 27"""
cursorObj.execute(and_query)
# Fetching all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(5, ''Hardik'', 27, ''Vishakapatnam'', 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

August 17, 2024

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

MySQL – ANY Operator

Table of content

The operators in MySQL have the same meaning as that of operators in mathematics. They are keywords that are used in MySQL statements for performing comparisons or logical operations.

ANY Operator in MySQL

The MySQL ANY keyword can be used with a comparison operator (such as =, <, >, <=, >=, <>) to compare a value with a set of values returned by the subquery.

This operator will return true if the given condition is satisfied for any of the values in the set.
This operator will return false if none of the values in the specified set satisfy the given condition.

The ANY operator must be preceded by a standard comparison operator i.e. >, >=, , !=, and followed by a subquery.

Syntax

Following is the syntax of the ANY operator in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name operator ANY (subquery);

Where,

column_name is the name of a column to be compared with a subquery.
operator is a comparison operator such as =, <, >, <=, >=, or <>.
subquery is a SELECT statement that returns a single column of values.

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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 inserts 7 records into the above-created MySQL 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 below query to retrieve 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

ANY with “>” Operator

The MySQL ANY operator can be used with the comparison operator “>” (greater than) to verify whether a particular column value is greater than the column value of any of the other records returned by the subquery.

Example

In the following query, we are selecting all records from the CUSTOMERS table where the SALARY column is greater than any of the salaries associated with customers whose age is 22.

SELECT * FROM CUSTOMERS
WHERE SALARY > ANY
(SELECT SALARY FROM CUSTOMERS WHERE AGE = 22);

Output

The salary of customer with age 22 is 4500. The following are the customers whose salaries are greater than 4500 (age=22).

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

ANY with “<” Operator

We can use the comparison operator “<“ (less than) with the MySQL ANY operator to verify whether a particular column value is less than the column value of any of the records returned by the subquery.

Example

In this query, we are selecting all records from the CUSTOMERS table where the SALARY column is less than any of the salaries associated with customers whose age is 32.

SELECT * FROM CUSTOMERS
WHERE SALARY < ANY
(SELECT SALARY FROM CUSTOMERS WHERE AGE = 32);

Output

The salary of 32 aged customer is 2000. The only customer with salary less than 2000 is ”Khilan” −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00

ANY with “=” operator

We can use the MySQL ANY operator with the comparison operator “=” (equal to) to fetch the records from a table where a column value is equal to any value returned by a subquery.

Example

Here, we are trying to select all records from the CUSTOMERS table where the AGE column matches any of the AGE associated with customers named “Khilan”.

SELECT * FROM CUSTOMERS
WHERE AGE = ANY
(SELECT AGE FROM CUSTOMERS WHERE NAME = "Khilan");

Output

The age of ”khilan” is 25. Another customer whose age is equal to 25 is ”Chaitali”.

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00

ANY with “<>” Operator

We can use the MySQL ANY operator with “<>” (not equal to) comparison operator to fetch the records from a table where a column value is not equal to any value returned by a subquery.

Example

In this query, we are selecting all records from the CUSTOMERS table where the ADDRESS column does not match any of the addresses associated with customers named “Ramesh”.

SELECT * FROM CUSTOMERS
WHERE ADDRESS <> ANY
(SELECT ADDRESS FROM CUSTOMERS WHERE NAME = "Ramesh");

Output

The address of ”Ramesh” is Ahmedabad. Following are the customers whose address is not equal to Ahmedabad.

ID	NAME	AGE	ADDRESS	SALARY
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

ANY with “<=” Operator

The MySQL ANY operator returns true if a value is less than or equal to any value in a specified set when used with the “<=” comparison operator.

Example

Here, we are selecting all records from the CUSTOMERS table where the AGE column is less than or equal to any age value in the AGE column where the SALARY is equal to 10000.

SELECT * FROM CUSTOMERS
WHERE AGE <= ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY = 10000);

output

The age of customer whose salary is 10000 is 24. So, the following are the customers whose age is less than 24 −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

ANY with “>=” Operator

The MySQL ANY operator returns true if a value is greater than or equal to any value in a specified set when used with the “>=” comparison operator.

Example

In this query, we are selecting all records from the CUSTOMERS table where the ”AGE” is greater than or equal to any value in the result set obtained by selecting ”AGE” from ”CUSTOMERS” where ”SALARY” is equal to 10,000.

SELECT * FROM CUSTOMERS
WHERE AGE >= ANY
(SELECT AGE FROM CUSTOMERS WHERE SALARY = 10000);

Output

The output for the program above is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

ANY Operator Using a Client Program

Besides using MySQL queries to perform the ANY operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the ANY Operator on MySQL table through a PHP program, we need to execute SELECT statement with ANY operator using the mysqli function query() as follows −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE COLUMN_NAME OPERATOR ANY (SUBQUERY)";
$mysqli->query($sql);

To perform the ANY Operator on MySQL table through a Node.js program, we need to execute SELECT statement with ANY operator using the query() function of the mysql2 library as follows −

sql= " SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name operator ANY (subquery)";
con.query(sql);

To perform the ANY Operator on MySQL table through a Java program, we need to execute SELECT statement with ANY operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name operator ANY (subquery)";
statement.executeQuery(sql);

To perform the ANY Operator on MySQL table through a Python program, we need to execute SELECT statement with ANY operator using the execute() function of the MySQL Connector/Python as follows −

any_query = SELECT column1, column2, ... FROM table_name
WHERE column_name comparison_operator ANY (subquery);
cursorObj.execute(any_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 tutorials_tbl WHERE tutorial_author = ANY (SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_id > 3)";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 5, Title: Learn MySQL, Author: Abdul S, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26

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 table
   sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,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);

   //Using ANY Operator
   sql = "SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 29);"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.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 AnyOperator {
  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 SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 30)";
            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: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.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()
subquery = """SELECT SALARY FROM CUSTOMERS WHERE AGE = 29"""
any_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > ANY ({subquery})"""
cursorObj.execute(any_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 −

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.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

August 17, 2024

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

MySQL – NOT Operator

Table of content

MySQL NOT Operator

MySQL NOT is a logical operator that allows us to exclude specific conditions or expressions from a WHERE clause. This operator is often used when we need to specify what NOT to include in the result table rather than what to include.

Suppose we take the example of the Indian voting system, where people under 18 are not allowed to vote. In such a scenario, we can use the NOT operator to filter out minors while retrieving information about all eligible voters. This helps us create an exception for minors and only display details of those who are eligible to vote.

The NOT operator is always used in a WHERE clause so its scope within the clause is not always clear. Hence, a safer option to exactly execute the query is by enclosing the Boolean expression or a subquery in parentheses.

Syntax

Following is the syntax of the NOT operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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 statement to insert 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 from 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

In the following query, We are selecting all the records from the CUSTOMERS table where the ADDRESS is NOT “Hyderabad”.

SELECT * FROM Customers
WHERE NOT ADDRESS = ''Hyderabad

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
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

NOT with IN Operator

We can use the MySQL logical NOT operator along with the IN keyword to eliminate the rows that match any value in a given list.

Example

Using the following query, we are fetching all the records from the CUSTOMERS table where NAME is NOT “Khilan”, “Chaital”, and “Muffy”.

SELECT * FROM CUSTOMERS
WHERE NAME NOT IN ("Khilan", "Chaital", "Muffy");

Output

If we execute the above query, the result is produced as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.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

NOT with IS NULL Operator

We can use the MySQL logical NOT operator along with the IS NULL keyword to select rows in a specified column that do not have a NULL value.

Example

In this query, we are selecting all the records from the CUSTOMERS table where the ADDRESS column is not null.

SELECT * FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;

Output

The output will be displayed as −

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

NOT with LIKE Operator

We can use the MySQL logical NOT operator along with the LIKE keyword to select the rows that do not match a given pattern.

Example

In the query below, we are fetching all the records from the CUSTOMERS table where the NAME column does not start with the letter K.

SELECT * FROM CUSTOMERS
WHERE NAME NOT LIKE ''K%

Output

The output will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

NOT with BETWEEN Operator

MySQL”s NOT operator can be used with the BETWEEN keyword to return rows outside a specified range or interval of time.

Example

In the following example, we are selecting all the records from the CUSTOMERS table where the AGE is not between 25 and 30.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 25 AND 30;

Output

When we execute the query above, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT with UPDATE Statement

The UPDATE statement in MySQL can be used along with the NOT operator in the WHERE clause to update rows that do not meet a specific condition.

Syntax

Following is the syntax of the NOT operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE NOT condition ... ;

Example

In the following query, we are updating the SALARY of the CUSTOMERS to a value of 12000 where the AGE is not between 25 and 30.

UPDATE CUSTOMERS
SET SALARY = 12000
WHERE AGE NOT BETWEEN 25 AND 30;

Output

The output will be displayed as −

Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Verification

Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

The output for the query above is produced as given below −

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

NOT with DELETE Statement

The DELETE statement in MySQL can be used along with the NOT operator in the WHERE clause to delete rows that do not meet a specific condition.

Syntax

Following is the syntax of NOT operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE NOT condition ... ;

Example

In the following query, we are deleting records from the CUSTOMERS table where the SALARY is not between 10000 and 15000.

DELETE FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 10000 AND 15000;

Output

Query OK, 3 rows affected (0.01 sec)

Verification

Using the below query, we can verify whether the above operation is successful or not −

SELECT * FROM CUSTOMERS;

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	12000.00
3	Kaushik	23	Kota	12000.00
6	Komal	22	Hyderabad	12000.00
7	Muffy	24	Indore	12000.00

NOT Operator Using a Client Program

Besides using MySQL queries to perform the NOT operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the NOT operator on a MySQL table through PHP program, we need to execute SELECT statement with NOT operator using the mysqli function query() as follows −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
$mysqli->query($sql);

To perform the NOT operator on a MySQL table through Node.js program, we need to execute SELECT statement with NOT operator using the query() function of the mysql2 library as follows −

sql=" SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
con.query(sql);

To perform the NOT operator on a MySQL table through Java program, we need to execute SELECT statement with NOT operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
statement.executeQuery(sql);

To perform the NOT operator on a MySQL table through Python program, we need to execute SELECT statement with NOT operator using the execute() function of the MySQL Connector/Python as follows −

not_query = SELECT column1, column2, ... FROM table_name
WHERE NOT condition"
cursorObj.execute(not_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 NOT ADDRESS = ''Hyderabad";
$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 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 10000.000000
Id 6, Name: Komal, Age: 29, Address Vishakapatnam, Salary 7000.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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using NOT Operator
  sql = "SELECT * FROM Customers WHERE NOT ADDRESS = ''Hyderabad"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotOperator {
  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 NOT ADDRESS = ''Hyderabad''";
            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: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.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()
not_query = f"""
SELECT * FROM Customers
WHERE NOT ADDRESS = ''Hyderabad
"""
cursorObj.execute(not_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 −

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.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

August 17, 2024

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

August 17, 2024

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

MySQL – NOT EQUAL Operator

Table of content

MySQL NOT EQUAL Operator

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator.

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: The comparison is case-sensitive by default when using this operator with text values.

Syntax

Following is the syntax of the NOT EQUAL operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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

The below query uses INSERT INTO statement to add 7 records into 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 retrieve 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

NOT EQUAL with String Values

In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific value.

Example

In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not “Khilan”.

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

Output

The output of the above code is as shown 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
7	Muffy	24	Indore	10000.00

NOT EQUAL with GROUP BY Clause

MySQL”s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

In this query, we are counting the number of records with distinct ”ID” values for each ”AGE” in the ”CUSTOMERS” table. We are excluding records where ”AGE” is equal to ”22”, and grouping the results based on the ”AGE” column.

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE  ''22'' GROUP BY AGE;

Output

COUNT(ID)	AGE
1	32
2	25
1	23
1	27
1	24

NOT EQUAL with Multiple Conditions

Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Here, we are going to select all the customers whose salary is either “>2000” or “=2000”. At the same time, the customer must not be from “Bhopal”.

SELECT * FROM CUSTOMERS
WHERE ADDRESS  ''Bhopal'' AND (SALARY>''2000'' OR SALARY=''2000'');

Output

When we execute the query above, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Negating a Condition Using NOT EQUAL

The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition.

Example

The following query retrieves all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” −

SELECT * FROM CUSTOMERS
WHERE NOT SALARY != ''2000

Output

When the query gets executed it will generate the following output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

NOT EQUAL Operator Using a Client Program

Besides using MySQL queries to perform the NOT EQUAL operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the NOT EQUAL Operator on a MySQL table through a PHP program, we need to execute SELECT statement with NOT EQUAL Operator using the mysqli function query() as follows −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
$mysqli->query($sql);

To perform the NOT EQUAL Operator on a MySQL table through a Node.js program, we need to execute SELECT statement with NOT EQUAL Operator using the query() function of the mysql2 library as follows −

sql= "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
con.query(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Java program, we need to execute SELECT statement with NOT EQUAL Operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
statement.executeQuery(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Python program, we need to execute SELECT statement with NOT EQUAL Operator using the execute() function of the MySQL Connector/Python as follows −

not_equal_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value"
cursorObj.execute(not_equal_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 NAME  ''Muffy''";
$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 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 4000.000000
Id 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 3, Name: kaushik, Age: 23, Address Hyderabad, Salary 11000.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 10000.000000
Id 6, Name: Komal, Age: 29, Address Vishakapatnam, Salary 7000.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);

  //Using NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''4000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 29,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''7000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotEqualOperator {
  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 NAME  ''Muffy''";
            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: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
not_equal_query = f"""
SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy
"""
cursorObj.execute(not_equal_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.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

August 17, 2024

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

MySQL – Intersect Operator

Table of content

In mathematical set theory, the intersection of two sets is a set that contains only those elements that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets.

If we perform the intersection operation on both sets using the INTERSECT operator, it displays the common rows from both tables. This operator removes the duplicate rows from the final result set.

MySQL INTERSECT Operator

In MySQL, the INTERSECT operator is used to return the records that are identical/common between the result sets of two SELECT (tables) statements.

However, the INTERSECT operator works only if both the SELECT statements have an equal number of columns with same data types and names.

Syntax

Following is the syntax of INTERSECT operator in MySQL −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

Example

First of all, let us create a table named STUDENTS using the following query −

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement.

INSERT INTO STUDENTS VALUES
(1, ''Vijay'', ''Cricket'', 18),
(2, ''Varun'', ''Football'', 26),
(3, ''Surya'', ''Cricket'', 19),
(4, ''Karthik'', ''Cricket'', 25),
(5, ''Sunny'', ''Football'', 26),
(6, ''Dev'', ''Cricket'', 23);

The table is created as follows −

ID	NAME	HOBBY	AGE
1	Vijay	Cricket	18
2	Varun	Football	26
3	Surya	Cricket	19
4	Karthik	Cricket	25
5	Sunny	Football	26
6	Dev	Cricket	23

Now, let us create another table with name ASSOCIATES using the following query −

CREATE TABLE ASSOCIATES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement −

INSERT INTO ASSOCIATES VALUES
(1, ''Naina'', ''Maths'', 24, ''Cricket''),
(2, ''Varun'', ''Physics'', 26, ''Football''),
(3, ''Dev'', ''Maths'', 23, ''Cricket''),
(4, ''Priya'', ''Physics'', 25, ''Cricket''),
(5, ''Aditya'', ''Chemistry'', 21, ''Cricket''),
(6, ''Kalyan'', ''Maths'', 30, ''Football'');

The table is created as follows −

ID	NAME	SUBJECT	AGE	HOBBY
1	Naina	Maths	24	Cricket
2	Varun	Physics	26	Football
3	Dev	Maths	23	Cricket
4	Priya	Physics	25	Cricket
5	Aditya	Chemistry	21	Cricket
6	Kalyan	Maths	30	Football

Now, we return the common records from both the tables using the following query −

SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;

Output

The output is obtained as follows −

NAME	AGE	HOBBY
Varun	26	Football
Dev	23	Cricket

INTERSECT with BETWEEN Operator

The MySQL INTERSECT operator can be used with the BETWEEN operator to find the rows that exist within the specified range.

Example

In the following query, we are retrieving the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 −

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE AGE BETWEEN 20 AND 30;

Output

On executing the given program, the output is displayed as follows −

NAME	AGE	HOBBY
Varun	26	Football

INTERSECT with IN Operator

In MySQL, we can use the INTERSECT operator with IN operator to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values.

Example

In the following query, we are trying to return the common records from both tables. In addition; we are using th IN operator to retrieve the records whose hobby is “Cricket”.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN(''Cricket'')
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE HOBBY IN(''Cricket'');

Output

The output for the program above is produced as given below −

NAME	AGE	HOBBY
Dev	23	Cricket

INTERSECT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string value.

We can use the LIKE operator with the INTERSECT operator in MySQL to find the common rows that match the specified pattern.

Example

In the following query, we are using the wildcard ”%” with the LIKE operator to fetch the names with ”v” from the common names of both tables.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE ''v%''
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE NAME LIKE ''v%

Output

Let us compile and run the program, to produce the following result −

NAME	AGE	HOBBY
Varun	26	Football

Intersect Operator Using Client Program

In addition to executing the Intersect Operator in MySQL server, we can also execute the INTERSECT operator on a table using a client program.

Syntax

Following are the syntaxes of the Intersect Operator in MySQL table in various programming languages −

To execute the Intersect operator in MySQL table through a PHP program, we need to execute INTERSECT statement using the query() function of mysqli connector.

$sql = "SELECT column1, column2,..., columnN FROM table1, table2,...,
tableN INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
$mysqli->query($sql);

To execute the Intersect operator in MySQL table through a JavaScript program, we need to execute INTERSECT statement using the query() function of mysql2 connector.

sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
con.query(sql);

To execute the Intersect operator in MySQL table through a Java program, we need to execute INTERSECT statement using the executeQuery() function of JDBC type 4 driver.

String sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
statement.executeQuery(sql);

To execute the Intersect operator in MySQL table through a Python program, we need to execute INTERSECT statement using the execute() function provided by MySQL Connector/Python.

intersect_query = SELECT column1, column2,..., column
FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., column
FROM table1, table2,..., tableN
cursorObj.execute(intersect_query);

Example

Following are the implementations of this operation in various programming languages −

$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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("NAME %s, AGE %d, HOBBY %s",
      $row["NAME"],
      $row["AGE"],
      $row["HOBBY"],);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
NAME Varun, AGE 26, HOBBY Football
NAME Dev, AGE 23, HOBBY Cricket

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 STUDENTS table
  sql = "CREATE TABLE STUDENTS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,HOBBY VARCHAR(20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, ''Vijay'', ''Cricket'',18),(2, ''Varun'',''Football'', 26),(3, ''Surya'', ''Cricket'',19),(4, ''Karthik'',''Cricket'', 25),(5, ''Sunny'',''Football'', 26),(6, ''Dev'', ''Cricket'',23);"
  con.query(sql);

  //Creating ASSOCIATES table
  sql = "CREATE TABLE ASSOCIATES(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,SUBJECT VARCHAR(20) NOT NULL,AGE INT NOT NULL,HOBBY VARCHAR(20) NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, ''Naina'',''Maths'', 24, ''Cricket''),(2, ''Varun'',''Physics'', 26, ''Football''),(3, ''Dev'',''Maths'', 23, ''Cricket''),(4, ''Priya'',''Physics'', 25, ''Cricket''),(5,''Aditya'', ''Chemistry'', 21, ''Cricket''),(6,''Kalyan'', ''Maths'', 30, ''Football'');"
  con.query(sql);

  //Using INTERSECT Operator
  sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { NAME: ''Varun'', AGE: 26, HOBBY: ''Football'' },
  { NAME: ''Dev'', AGE: 23, HOBBY: ''Cricket'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IntersectOperator {
  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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String hobby = rs.getString("HOBBY");
              System.out.println("Name: " + name + ", Age: " + age + ", Hobby: " + hobby);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Name: Varun, Age: 26, Hobby: Football
Name: Dev, Age: 23, Hobby: Cricket

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
intersect_query = f"""
SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;
"""
cursorObj.execute(intersect_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 −

(''Varun'', 26, ''Football'')
(''Dev'', 23, ''Cricket'')

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

August 17, 2024

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

MySQL – Between Operator

Table of content

MySQL Between Operator

The BETWEEN operator in MySQL is a logical operator provided by SQL, that is used to restrict the range from which the data values are to be retrieved. The retrieved values can be integers, characters, or dates.

You can use BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.

Let us understand in a better way by using the following example table −

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here,

value1 is the beginning value of the range.
value2 is the ending value of the range (inclusive).

Example

First of all, let us create a table named CUSTOMERS using the following query −

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

Let us insert some values into this table using the following INSERT query −

INSERT INTO CUSTOMERS 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 );

The table created is as follows −

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

Now, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 −

SELECT * FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 25;

Output

When we execute the above query, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL BETWEEN with IN Operator

The BETWEEN operator and the IN operator can be used together in a MySQL query, to select values that are within a specified range and also match with specified values.

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
AND column_name IN (list_of_values);

Example

In this example, we are selecting all the customers whose salary is between 4000 and 10000. In addition; we are only retrieving the customers who are living in MP and Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY BETWEEN 4000 AND 10000
AND ADDRESS IN (''Hyderabad'', ''Bhopal'');

Output

The following is obtained −

ID	NAME	AGE	ADDRESS	SALARY
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

MySQL BETWEEN with UPDATE statement

The UPDATE statement in MySQL is used to modify existing data in a database table. Using the BETWEEN operator in an UPDATE statement to update values within the specified range.

Example

Let us update the salaries of the customers whose age lies between 25 to 30 using the following query −

UPDATE CUSTOMERS
SET SALARY = 10000
WHERE AGE BETWEEN 20 AND 25;

Verification

Let us verify whether the salaries are updated or not using the following query −

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

BETWEEN operator with DELETE statement

We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.

Example

Now, let us delete the customers whose age is between 18 and 20 using the DELETE command.

DELETE FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 22;

Verification

Let us verify whether the specified aged employees are deleted or not using the following query −

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
7	Muffy	24	Indore	10000.00

MySQL NOT BETWEEN Operator

The NOT BETWEEN operator in MySQL is a negation of the BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.

Syntax

Following is the syntax of the NOT BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

Consider the CUSTOMERS table to retrieve the details of customers whose age is not between 20 and 25 (numeric data) using the following query.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 20 AND 25;

Output

Following is the output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

NOT BETWEEN operator with IN operator

Like the BETWEEN operator, we can also use the NOT BETWEEN operator in combination with the IN operator. This is to select values that fall outside a range and also do not match with the specified values.

Example

In the following query, we are selecting the customers whose salary is NOT between 1000 and 5000. In addition; we are not retrieving the employees who are living in Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1000 AND 5000
AND ADDRESS NOT IN (''Bhopal'');

Output

Following is the output −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

Between Operator Using Client Program

We can also apply the BETWEEN operator on a MySQL table using a client program.

Syntax

Following are the syntaxes of the Between Operator in MySQL table in various programming languages −

To execute the Between Operator in MySQL through a PHP program, we need to execute the SQL query with BETWEEN statement using the mysqli function named query() as −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2";
$mysqli->query($sql);

To execute the Between Operator in MySQL through a JavaScript program, we need to execute the SQL query with BETWEEN statement using the mysql2 function named query() as −

sql = " SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2";
con.query(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the JDBC type 4 driver function named executeQuery() as −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2";
statement.executeQuery(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the MySQL Connector/Python function named execute() as −

between_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2"
cursorObj.execute(between_query);

Example

Following are the implementations of this operation in various programming languages −

$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 SALARY BETWEEN 4000 AND 10000";
$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 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address , 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 IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using BETWEEN Clause
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages BETWEEN 170 AND 300;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    id: 1,
    name: ''John'',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: ''Ram'',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: ''Jack'',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: ''Jill'',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 5,
    name: ''Zara'',
    work_date: 2007-06-05T18:30:00.000Z,
    daily_typing_pages: 300
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BetweenClause {
    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";
            rs = st.executeQuery(sql);
            System.out.println("Table records before update: ");
            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);
            }
            String sql1 = "UPDATE CUSTOMERS SET SALARY = 10000 WHERE AGE BETWEEN 20 AND 25";
            st.executeUpdate(sql1);
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records after update: ");
            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 before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.00
Table records after update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.0

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
between_query = f"""
SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000;
"""
cursorObj.execute(between_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.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

August 17, 2024

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

MySQL – IS NULL Operator

Table of content

NULL values in a MySQL table fields indicate that no (or unknown) values are present in them. These values are different from zeroes or invalid values.

In MySQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (filtering non-null values) operators.

MySQL IS NULL Operator

The IS NULL operator in MySQL is used to check whether a value in a column is NULL. Using the IS NULL operator with a conditional clause allows us to filter records that contain NULL values in a particular column.

We can also use this operator with SELECT, UPDATE, and DELETE SQL statements.

Syntax

Following is the syntax of IS NULL in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IS NULL;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

In the following query, we are using the INSERT statement to insert values to the table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', NULL),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', NULL, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', NULL),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', NULL, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table is created as follows −

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

IS NULL with SELECT statement

The MySQL IS NULL operator can be used with the SELECT statement to filter the records with NULL values.

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is null.

SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;

Output

On executing the above query, it will generate an output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	NULL	Kota	20000.00
6	Komal	NULL	Hyderabad	20000.00

IS NULL with COUNT() function

We can use the MySQL IS NULL operator with the COUNT() function to count the number of records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL with COUNT() function in MySQL −

SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NULL;

Example

The following query returns the count of records have a blank field (NULL) in ADDRESS column of the CUSTOMERS table.

SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NULL;

Output

On executing the above query, it will generate an output as shown below −

COUNT(*)
2

IS NULL with UPDATE statement

In MySQL, we can use the IS NULL operator with the UPDATE statement to update records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the UPDATE statement in MySQL –

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NULL;

Example

In the following query, we are updating the blank (NULL) records of the SALARY column to a value of 9000.

UPDATE CUSTOMERS
SET SALARY = 9000
WHERE SALARY IS NULL;

Verification

To check whether the table has been updated or not, execute the SELECT query to display the table.

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

IS NULL with DELETE statement

In MySQL, we can use the IS NULL operator with the DELETE statement to delete records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the DELETE statement in MySQL –

DELETE FROM table_name
WHERE column_name(s) IS NULL;

Example

In the following query, we are trying to delete the blank (NULL) records present in the ADDRESS column of CUSTOMERS table.

DELETE FROM CUSTOMERS
WHERE AGE IS NULL;

Verification

To check whether the table has been changed or not, execute the SELECT query to display the table.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

IS NULL Operator Using Client Program

In addition to executing the IS NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.

Syntax

Following are the syntaxes of the IS NULL Operator in MySQL table in various programming languages −

To execute the IS NULL Operator in MySQL through a PHP program, we need to execute the SQL query with IS NULL operator using the mysqli function named query() as −

$sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL";
$mysqli->query($sql);

To execute the IS NULL Operator in MySQL through a JavaScript program, we need to execute the SQL query with IS NULL operator using the mysql2 function named query() as −

sql= " SELECT column_name1, column_name2, ...FROM table_name
WHERE column_name IS NULL";
con.query(sql);

To execute the IS NULL Operator in MySQL through a Java program, we need to execute the SQL query with IS NULL operator using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL";
statement.executeQuery(sql);

To execute the IS NULL Operator in MySQL through a Python program, we need to execute the SQL query with IS NULL operator using the function named execute() provided by MySQL Connector/Python −

is_null_query = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL"
cursorObj.execute(is_null_query);

Example

Following are the implementations of this operation in various programming languages −

$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 AGE IS NULL";
$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 3, Name: kaushik, Age: 0, Address Hyderabad, Salary 2000.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.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),AGE INT,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'',NULL),(2,''Khilan'', 25, NULL, 1500.00),(3,''kaushik'', NULL, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',NULL, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, NULL, 10000.00);"
  con.query(sql);

  //Using IS NULL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: null,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: null,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IsNullOperator {
  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 AGE IS NULL";
            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: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
is_null_query = f"""
SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;
"""
cursorObj.execute(is_null_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 −

(3, ''kaushik'', None, ''Hyderabad'', Decimal(''2000.00''))
(6, ''Komal'', None, ''Vishakapatnam'', Decimal(''4500.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

August 17, 2024

Category: mysql

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

MySQL – IN Operator