Operators are reserved words primarily used in SQL to perform various operations on data, like addition (+), subtraction (-), or comparison (==).

Conjunctive operators, specifically used in boolean logic, combines two conditions in an SQL statement. The most common conjunctive operators are: AND (&&), which returns true if both conditions are true, and OR (||), which returns true if at least one condition is true.

The SQL AND Operator

The SQL AND returns true or 1, if both its operands evaluates to true. We can use it to combine two conditions in the WHERE clause of an SQL statement.

Syntax

The basic syntax of the SQL AND operator with a WHERE clause is as follows −

WHERE [condition1] AND [condition2];

Where, condition1, condition2 are the conditions we want to apply to the query.

You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all the specified conditions (separated by the AND operator) must be TRUE.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using the CREATE TABLE statement as shown below −

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

Following query inserts values into this table 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 obtained is as shown below −

Following is an example which would fetch the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −

SELECT ID, NAME, SALARY FROM CUSTOMERS 
WHERE SALARY > 2000 AND AGE < 25;

Output

This would produce the following result −

Multiple AND Operators

You can also use multiple ”AND” operators in an SQL query to combine multiple conditions (or, expressions) together. Conditions combined with the ”AND” operators are evaluated from left to right. If any of the condition evaluate to false, the entire compound condition will be false and the record will not be included in the result set.

Syntax

Following is the syntax −

WHERE [condition1] AND [condition2]...AND [conditionN];

Example

In the following query, we are selecting all records from the CUSTOMERS table where the name of the customer starts with ”K”, the age of the customer is greater than or equal to 22, and their salary is less than 3742 −

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

Output

Following is the result produced −

AND with Other Logical Operators

The “AND” operator can be used in combination with other logical operators to filter records from a database table.

When using multiple logical operators in SQL, the order of operations is important. Parentheses can be used to control the order of operations and ensure that the conditions are evaluated in the correct order.

Additionally, using too many logical operators or complex expressions can negatively impact query performance, so it”s important to carefully consider the design of the WHERE clause when working with large datasets.

Example

In here, we are combining the AND operator with the NOT operator to create a NAND operation. The ”NAND” operation returns true if at least one of the input conditions is false, and false if both input conditions are true.

In the following query, we are selecting all records from the CUSTOMERS table where the condition (salary is greater than 4500 and the age is less than 26) is false. The “NOT” operator negates the entire condition, and the “AND” operator combines two conditions −

SELECT * FROM CUSTOMERS 
WHERE NOT (SALARY > 4500 AND AGE < 26);

Output

Following is the output of the above query −

AND with UPDATE Statement

We can use the AND operator in the WHERE clause of the UPDATE statement to modify the rows in a table that meet certain criteria.

Syntax

Following is the syntax of using the AND operator with the UPDATE statement −

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

Where, table_name is the name of the table we want to update, column1, column2, etc. are the columns we want to modify, and value1, value2, etc. are the new values we want to set for those columns.

Example

In the following query, we are updating the salary of all the customers whose age is greater than 27 and updating it to ”55000” using UPDATE statement −

UPDATE CUSTOMERS SET SALARY = 55000 WHERE AGE > 27;

Output

We get the following result. We can observe that the salary of 1 customer has been modified −

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

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the salary of ”Ramesh” has been updated to ”55000” because his age is 32 i.e. greater than 27.

The SQL OR Operator

The OR operator returns true if at least one its operands evaluates to true, and false otherwise. We can combine two conditions in an SQL statement”s WHERE clause using the OR operator.

Syntax

The basic syntax of the OR operator with a WHERE clause is as follows −

WHERE [condition1] OR [condition2];

Where, condition1, condition2 are the conditions we want to apply to the query. Each condition is separated by the OR operator.

You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, at least of the conditions separated by the OR operator must be TRUE.

Example

The following query fetches the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years −

SELECT ID, NAME, SALARY FROM CUSTOMERS 
WHERE SALARY > 2000 OR AGE < 25;

Output

This would produce the following result −

Multiple OR Operators

In SQL, it is common to use multiple OR operators to combine multiple conditions or expressions together. While 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 selecting all records from the CUSTOMERS table where either the name of the customer ends with ”l”, or the salary of the customer is greater than 10560, or their age is less than 25 −

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

Output

Following is the result obtained −

OR with AND Operator

We can also use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria.

Syntax

Following is the syntax for using the AND and OR operators together −

WHERE (condition1 OR condition2) AND condition3;

Where, condition1, condition2, and condition3 represent the conditions that we want to combine with the AND and OR operators. The parentheses group the first two conditions and combine them with the OR operator. The result of that operation is combined with the third condition using the AND operator.

Example

In the following query, we are retrieving all rows from the “CUSTOMERS” table where the age of the customer is equal to 25 or the salary is less than 4500 and the name is either Komal or Kaushik. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −

SELECT * FROM CUSTOMERS 
WHERE (AGE = 25 OR SALARY < 4500) 
AND (NAME = ''Komal'' OR NAME = ''Kaushik'');

Output

This would produce the following result −

OR with DELETE Statement

We can also use the OR operator with the DELETE statement to delete rows that meet any one of the (multiple) conditions.

Syntax

Following is the syntax of using OR operator with DELETE statement −

DELETE FROM table_name
WHERE column1 = ''value1'' OR column2 = ''value2

Example

In the following query, we are deleting the records from the CUSTOMERS table where either the age of the customer equals 25 or their salary is less than 2000 −

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

Output

We get the following result −

Query OK, 2 rows affected (0.01 sec)

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −