x

SQL – AND, OR operators

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

sql-table19


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:

sql-table20

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:

sql-table21

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:

sql-table22

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.

Prev     Next



Like it? Please Spread the word!