MySQL - AND Operator

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:

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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

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

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

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

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

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

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

(5, 'Hardik', 27, 'Vishakapatnam', Decimal('10000.00'))