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

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

Create Views Update Views Drop Views Rename Views

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

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

Unique Key Primary Key Foreign Key Composite Key Alternate Key

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

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

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

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

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

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

AND Operator

MySQL Tutorial

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

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:
﻿
1
0
NULL
1
1
0
NULL
0
0
0
0
NULL
NULL
0
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 
PHPNodeJSJavaPython
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);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = 'SELECT  * FROM 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();
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        
﻿
﻿
﻿
﻿
﻿
﻿

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

AND Operator

MySQL AND Operator

Example

Output

Example

Output

Example

Output

AND Operator with WHERE

Syntax

Example

Output

Example

Output

Multiple AND Operators

Example

Output

AND with UPDATE statement

Syntax

Example

Output

Verification

AND with DELETE Statement

Syntax

Example

Output

Verification

Output

AND Operator Using a Client Program

Syntax

Example

Output