SQL - AND and OR Conjunctive Operators

---

An operator is a reserved word or a character used primarily in SQL to manipulate and retrieve data from a database object. They are used with SQL queries in a WHERE clause to perform binary or unary operation(s) based on the operator used. In a unary operation, the operation is performed with a single operator and a single operand; whereas in a binary operation, the operation is performed with a single operator and two operands.

These operators are used to specify conditions in an SQL statement with the purpose of filtering data or to serve as conjunctions for multiple conditions in a statement.

The SQL AND & OR are logical operators, that serve as conjunctive operators, used to combine multiple conditions in an SQL statement with the purpose of filtering data in a database table.

These operators provide a means to make multiple comparisons with different operators in the same SQL statement.

The AND Operator

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. All of these conditions must be true for the overall condition to be true.

Syntax

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

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

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

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 conditions separated by the AND must be TRUE.

Example

Assume we have created a table with name CUSTOMERS in SQL database using 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);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

Using the following SELECT query, you can verify if the records are properly inserted into the table −

SELECT * from CUSTOMERS;
+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

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 −

+----+-------+----------+
| ID | NAME  | SALARY   |
+----+-------+----------+
|  6 | Komal |  4500.00 |
|  7 | Muffy | 10000.00 |
+----+-------+----------+

Multiple AND Operators

You can also 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 compound 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 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 −

+----+---------+-----+---------+---------+
| ID | NAME    | AGE | ADDRESS | SALARY  |
+----+---------+-----+---------+---------+
|  2 | Khilan  |  25 | Delhi   | 1500.00 |
|  3 | kaushik |  23 | Kota    | 2000.00 |
+----+---------+-----+---------+---------+

AND with other Logical Operators

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

It's important to note that 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 −

+----+---------+-----+-----------+---------+
| 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 | MP        | 4500.00 |
+----+---------+-----+-----------+---------+

AND with UPDATE statement

The "AND" operator can be used with the "UPDATE" statement to modify rows in a table that meet certain specified 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 trying to update 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 −

(1 row affected)

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.

To display the Customers table, use the following query −

select * from customers;

The table is displayed as follows −

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

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 OR Operator

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. It returns true if at least one of the conditions is true, and false if all conditions are false.

Syntax

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

WHERE [condition1] OR [condition2]...OR [conditionN]

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, the only any ONE of the conditions separated by the OR 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 −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

Multiple OR operators

In SQL, it is common to use multiple 'OR' operators to combine multiple conditions or expressions together. When using multiple "OR" operators, any rows that meet at least one of the conditions will be returned.

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 −

+----+---------+-----+---------+---------+
| ID | NAME    | AGE | ADDRESS | SALARY  |
+----+---------+-----+---------+---------+
|  3 | kaushik |  23 | Kota    | 2000.00 |
|  6 | Komal   |  22 | MP      | 4500.00 |
|  7 | Muffy   |  24 | Indore  |10000.00 |
+----+---------+-----+---------+---------+

OR with AND operator

We can 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 trying to retrieve all rows from the "CUSTOMERS" table where the age 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 −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+   

OR with DELETE statement

We can also use the OR operator with the DELETE statement to delete rows that meet any one of 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 trying to delete 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 −

(2 rows affected)

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.

To display the Customers table, use the following query −

select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad | 55000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+