Prev Next
AND, OR operators in SQL are used with SELECT, UPDATE and DELETE statements/queries to select, update and delete only particular records in a table those meet the condition given in WHERE clause and conditions given in AND, OR operators. I.e. it filters records from a table as per the condition. Syntax for SQL AND, OR operators are given below.
SQL Syntax for AND, OR operators:
Syntax for SQL AND operator | SELECT column_name1, column_name2, etc FROM table_name WHERE [condition1] AND [condition2] AND [condition3] etc; |
Syntax for SQL OR operator | SELECT column_name1, column_name2, etc FROM table_name WHERE [condition1] OR [condition2] OR [condition3] etc; |
Syntax for combination of SQL AND & OR operators | SELECT column_name1, column_name2, etc FROM table_name WHERE [condition1] AND [condition2] OR [condition3] etc; |
Where [condition] should be in the following format.
[column_name] [Operator] [Value];
Where,
[column_name] – Any one of the column names in the table.
[Operator] – Any one of the following (>, <, =, >=, <=, NOT, LIKE etc)
[Value] – User defined value.
Note: AND, OR operators in where clause can be used in any order and in any combination as per the requirement.
Please consider the following table with few records as given below.
Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Example1: How to use AND operator in WHERE clause in SELECT queries:
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE City=‘Vizag’ AND Student_name=’Thiyagarajan’;
SQL query Output:
Example2: How to use OR operator in WHERE clause in SELECT queries:
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE City=‘Vizag’ OR Student_name=’Raju’;
SQL query Output:
Example3: How to use both AND, OR operator in a WHERE clause in SELECT queries:
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE Age > 27 OR City=‘Vizag’ AND Student_name=’ Thiyagarajan’;
SQL query Output:
Note: If you have more than one combination of OR, AND operators, it is best practice to arrange OR operators at the beginning of where clause and then arranging AND operators.