AND and OR Operator in SQL

SQL AND and OR operators are used for data filtering and getting precise results based on conditions. They are used with the WHERE clause and are also called conjunctive operators.

AND and OR operators in SQL can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.

SQL AND Operator

The AND operator in SQL only displays those records where all conditions are true, i.e. both condition1 and condition2 are True. 

Syntax

SELECT * FROM table_name WHERE condition1 AND condition2 and …condition;

Here,

• table_name: name of the table
• condition1,2,..N: first condition, second condition, and so on.

SQL OR Operator

The OR Operator in SQL displays the records where any one condition is true, i.e. either condition1 or condition2 is True.

Syntax

SELECT * FROM table_name WHERE condition1 OR condition2 OR… conditionN;

• table_name: name of the table
• condition1,2,..N: first condition, second condition, and so on

SQL AND and OR Operator Examples

Let’s look at some examples of AND and OR operators in SQL and understand their working.

Now, we consider a table database to demonstrate AND & OR operators with multiple cases.

Student Table

SQL AND Operator Example

If suppose we want to fetch all the records from the Student table where Age is 18 and ADDRESS is Delhi.

Query

SELECT * FROM Student WHERE Age = 18 AND ADDRESS = 'Delhi';

Output

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
4	SURESH	Delhi	XXXXXXXXXX	18

SQL OR Operator Example

To fetch all the records from the Student table where NAME is Ram or NAME is SUJIT. 

Query

SELECT * FROM Student WHERE NAME = 'Ram' OR NAME = 'SUJIT';

Output

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
3	SUJIT	ROHTAK	XXXXXXXXXX	20

Combining AND and OR Operators in SQL

Combining AND and OR Operators in SQL allows the creation of complex conditions in queries. This helps in filtering data on multiple conditions. 

Syntax

SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);

Combining SQL AND and OR Operators Example

Take an example to fetch all the records from the Student table where Age is 18 NAME is Ram or RAMESH.

Query

SELECT * FROM Student WHERE Age = 18 AND (NAME = 'Ram' OR NAME = 'RAMESH');

Output

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18

Important Points About SQL AND & OR Operator

• The SQL AND operator is used to combine multiple conditions, where all the conditions must be true for the row to be included in the result set
• The OR operator is used to combine multiple conditions, where at least one of the conditions must be true for the row to be included in the result set.
• Any kind of condition, including equality, inequality, comparison, and logical operators, can be utilized with the AND and OR operators.
• The AND operator is more important than the OR operator. In other words, when both are used in the same SQL statement, the AND operator will be executed first. To change the order of evaluation, parentheses can be used.
• You can employ the AND and OR operators inside of other conditions because they can both be nested.