SQL AND & OR Operators
The SQL AND & OR both are comparative logical operators.
The SQL AND & OR statements are used to filter rows or records from query result based on single or multiple condition.
The SQL AND operator displays a record if both or all conditions are true.
The SQL OR operator displays a record if all or atleast one conditions are true.
These operators are used to create multiple comparisons with different operators at once.
SQL AND & OR Operator Syntax
For AND only condition:
SELECT column_name1, column_name2 FROM table_name
WHERE [condition1] AND [condition2];
For OR only condition:
SELECT column_name1, column_name2 FROM table_name
WHERE [condition1] OR [condition2];
For AND & OR both condition:
SELECT column_name1, column_name2 FROM table_name
WHERE ([condition1] AND [condition2]) OR [condition3];
or
SELECT column_name1, column_name2 FROM table_name
WHERE ([condition1] OR [condition2]) AND [condition3];
Note: You can combine multiple conditions using both operators.
When combining these conditions in a sql statement,
it is very important to provide round brackets or parenthesis so that the database engine knows what order to operate each condition.
Search Keys
- sql and operator
- sql and or
- sql where and where
- in and not in sql
- sql and statement
- sql greater than and less than
- sql in and not in
- sql and command
- sql server and operator
- pl sql and operator
- sql and query
- sql and condition
- using or in sql
- sql multiple where
- sql multiple conditions
- sql multiple where conditions
- sql where 2 conditions
- sql command and
Sample Database Table - Books
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
1 |
Programming With MySQL |
180 |
2011 |
Database |
Devi Mai |
2 |
Microsoft SQL Server 2012 |
130 |
2006 |
Security |
Hanumanthan |
3 |
Simply SQL |
100 |
2014 |
Optimization |
Dharan |
4 |
Jump Start MySQL |
105 |
2014 |
Administration |
Azaghu Varshith |
5 |
MySql Concurrency |
168.27 |
2012 |
Security |
Devi Mai |
6 |
SQL Programming & Database Design |
99.99 |
2007 |
Database |
Harish Karthik |
7 |
Art Of SQL |
165 |
2009 |
Performance |
Devi Mai |
8 |
Professional Oracle |
178.69 |
2015 |
Security |
Padmavathi |
9 |
Learing Oracle SQL & Pl/sql |
155 |
2009 |
Database |
Vinoth Kumar |
10 |
SQL Visual Quickstart |
84.22 |
2014 |
Programming |
Siva Kumar |
11 |
Oracle Database 11G New Features |
190 |
2010 |
Programming |
Hari Krishnan |
12 |
Programming With Oracle |
195 |
2010 |
Programming |
Azaghu Varshith |
SQL AND Operator Example
The following SQL SELECT statement selects all the books from the domainname "Database" AND bookprice greater then "100", in the "Books" table:
SELECT * FROM Books
WHERE
DomainName = 'Database' AND BookPrice > 100;
The result of above query is:
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
1 |
Programming With MySQL |
180 |
2011 |
Database |
Devi Mai |
9 |
Learing Oracle SQL & Pl/sql |
155 |
2009 |
Database |
Vinoth Kumar |
SQL OR Operator Example
The following SQL SELECT statement selects all the books from the authorname "Azaghu Varshith" OR "Padmavathi", in the "Books" table:
SELECT * FROM Books
WHERE
AuthorName = 'Azaghu Varshith' OR AuthorName = 'Padmavathi';
The result of above query is:
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
4 |
Jump Start MySQL |
105 |
2014 |
Administration |
Azaghu Varshith |
8 |
Professional Oracle |
178.69 |
2015 |
Security |
Padmavathi |
12 |
Programming With Oracle |
195 |
2010 |
Programming |
Azaghu Varshith |
Combining SQL AND & OR Operator Example
You can also combine AND and OR operators using round brackets or parenthesis to form complex expressions.
The following SQL statement selects all books from the book year less than "170" AND the domain name must
be equal to "Programming" OR "Security", in the "Books" table:
SELECT * FROM Books
WHERE
BookPrice < 170 AND (DomainName = 'Programming' OR DomainName = 'Security');
The result of above query is:
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
2 |
Microsoft SQL Server 2012 |
130 |
2006 |
Security |
Hanumanthan |
5 |
MySql Concurrency |
168.27 |
2012 |
Security |
Devi Mai |
10 |
SQL Visual Quickstart |
84.22 |
2014 |
Programming |
Siva Kumar |
Note: Don't forget to provide round brackets on conditions based on your need.
It is very important to provide round brackets or parenthesis so that the database engine knows what order to operate each condition.
If we did not provide the round brackets, it will change the condition execution order.
SELECT * FROM Books
WHERE
BookPrice < 170 AND DomainName = 'Programming' OR DomainName = 'Security';
The result of above query is:
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
2 |
Microsoft SQL Server 2012 |
130 |
2006 |
Security |
Hanumanthan |
5 |
MySql Concurrency |
168.27 |
2012 |
Security |
Devi Mai |
8 |
Professional Oracle |
178.69 |
2015 |
Security |
Padmavathi |
10 |
SQL Visual Quickstart |
84.22 |
2014 |
Programming |
Siva Kumar |