![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 | +----+----------+-----+-----------+----------+