MySQL – AND Operator
Table of content
- MySQL AND Operator
- AND Operator with WHERE
- Multiple AND Operators
- AND with UPDATE statement
- AND with DELETE Statement
- AND Operator Using a Client Program
”;
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 −
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''))
”;