Open In App
Related Articles

AND and OR operators in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

In SQL, the AND & OR operators are used for filtering the data and getting precise results based on conditions. The SQL AND & OR operators are also used to combine multiple conditions. These two operators 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.

  • The AND and OR operators are used with the WHERE clause.
  • These two operators are called conjunctive operators.

AND Operator

This operator displays only those records where both conditions condition1 and condition2 evaluates to True. 

Syntax

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

table_name: name of the table

condition1,2,..N: first condition, second condition and so on

OR Operator

This operator displays the records where either one of the conditions condition1 and condition2 evaluates to True. That is, either condition1 is True 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

AND vs OR

AND operator is used when all the conditions are true.
OR operator is used if any one of the conditions are true.

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

student table

Student Table

Example:

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

Query

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

Output

ROLL_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
4SURESHDelhiXXXXXXXXXX18

Example:

Take another example, to fetch all the records from the Student table where NAME is Ram and Age is 18. 

Query

SELECT * FROM Student WHERE Age = 18 AND NAME = 'Ram';

Output

ROLL_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18

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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
3SUJITROHTAKXXXXXXXXXX20

Example:

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

Query

SELECT * FROM Student WHERE NAME = 'Ram' OR Age = 20;

Output

ROLL_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
3SUJITROHTAKXXXXXXXXXX20

Combining AND and OR

We can combine AND and OR operators in the below manner to write complex queries. 

Syntax

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

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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18

Conclusion

  • 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, the circumstances that are separated by AND are examined first, followed by the conditions that are separated by OR.
  • You can employ the AND and OR operators inside of other conditions because they can both be nested.


Last Updated : 30 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads