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

Search Keys

  • sql server and
  • t sql bitwise and
  • or sql
  • or operator
  • sql in operator performance
  • sql not null
  • sql where not null
  • sql not equal to
  • sql is not
  • t sql not in
  • sql where not in list
  • sql not in select
  • sql not operator
  • tsql not equal
  • sql not equal operator
  • sql not between
  • sql where not equal
  • sql query not equal