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.
Related Links
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.
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | Microsoft SQL Server 2012 | 130 | 2006 | Security | Hanumanthan |
2 | Jump Start MySQL | 105 | 2014 | Administration | Azaghu Varshith |
3 | Professional Oracle | 178.69 | 2015 | Security | Padmavathi |
4 | Learing Oracle SQL & Pl/sql | 155 | 2009 | Database | Vinoth Kumar |
5 | SQL Visual Quickstart | 84.22 | 2014 | Programming | Siva Kumar |
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 |
---|---|---|---|---|---|
4 | 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 |
---|---|---|---|---|---|
2 | Jump Start MySQL | 105 | 2014 | Administration | Azaghu Varshith |
3 | Professional Oracle | 178.69 | 2015 | Security | Padmavathi |
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 |
---|---|---|---|---|---|
1 | Microsoft SQL Server 2012 | 130 | 2006 | Security | Hanumanthan |
5 | 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 |
---|---|---|---|---|---|
1 | Microsoft SQL Server 2012 | 130 | 2006 | Security | Hanumanthan |
3 | Professional Oracle | 178.69 | 2015 | Security | Padmavathi |
5 | SQL Visual Quickstart | 84.22 | 2014 | Programming | Siva Kumar |
Related Links