SQL AND & OR Operators

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 OR operators are used to sql where and where, sql greater than and less than, sql server and operator, multiple where, multiple conditions, and sql multiple where conditions.

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


SQL AND | OR operators are using sql or vs in operator performance, sql where not in list, sql not in select, sql not equal operator, sql where not equal, and combine AND and OR operators..

SQL AND Operator and Not Statement

Example 1: In a single SQL query, you can use the AND, OR, and NOT operators.

Table: EMPLOYEE

ID EMP_NAME AGE LOCATION SALARY
123 Daryl 41 Mumbai 120000
124 Jon 40 Delhi 80000
125 Saru 43 Pune 110000
126 Hemant 39 Shimla 110000
127 Devesh 42 Goa 90000

The following SQL statement will retrieve information on employees who are over 40 years old and whose workplace is either "Mumbai" or "Pune."

SELECT * FROM EMPLOYEE
WHERE AGE>40 AND (LOCATION='Mumbai' OR NOT LOCATION='Pune');

Result:

ID EMP_NAME AGE LOCATION SALARY
123 Daryl 41 Mumbai 120000
127 Devesh 42 Goa 90000

SQL AND Operator

There is no constructed Boolean type in MySQL. Alternatively, it considers zero to be FALSE and non-zero numbers to be TRUE.

The AND operator is a logical operator that yields 1, 0, or NULL when two or more Boolean expressions are combined:

A AND B

The operands of this expression are A and B. They could be numbers or phrases.

If A and B are both non-zero and not NULL, the logical AND operator yields 1. If either operand is zero, it outputs 0; alternatively, NULL is returned.

If both A and B are non-zero and NOT NULL, the logical AND operator yields 1. For instance, For example:

SELECT 1 AND 1;

Output:

1 AND 1
1

SQL AND Operator for Dates

When matching dates, use the operator and insert two dates between single quotes.

Example 1:

select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date >= '2011/02/25' and Date <= '2011/02/27'

Example 2: records from March 2015 to Feb 2016.

SELECT FROM table_name WHERE dte_field 
BETWEEN '2015-03-01' AND LAST_DAY('2016-02-01')

Example 3: Let's now select a set of records that span two dates. This is the SQL for it.

SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31' 

SQL AND Operator in Where Clause

When combining multiple conditions in the WHERE clause, the SQL AND operator is used. The result set is only run if all of the filter requirements are fulfilled. As a result, only the outcome will be filtered if both conditions are met. More than one AND can be used as part of the WHERE clause to combine various conditions.

Syntax:

SELECT column(s) FROM table_name WHERE condition1 AND condition2;

To combine two conditions, we can use the AND operator, as indicated in the syntax given.

Example 1: We will now try to understand one AND operator through some example.

Let’s consider the following Student table for example purpose.

RollNo StuName StuGender StuAge StuPercent
1 George M 14 85
2 Monica F 13 88
3 Jessica F 14 84
4 Tom M 13 78

Scenario: Get the percentage of students whose age is more than 13 years and gender is male.

SELECT StuPercent FROM Student WHERE StuAge>13 AND StuGender = "M";

Output:

StuPercent
85

In the example above, we have used one AND operator to combine two conditions, StuAge is greater than 13 and StuGender is equal to “M”.

Example 2: Let's look at a real-world example: if we wanted to retrieve a list of all the movies in category 2 that were released in 2008, we'd use the script seen below.

SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;

Executing the above script in MySQL workbench against the “myflixdb” produces the following results.

Movie_id Title Director Year_released Category_id
2 Forgetting Sarah Marshal Nicholas Stoller 2008 2

Example 3: List all suppliers in Paris, France.

SELECT CompanyName, ContactName, City, Country, Phone, Fax
FROM Supplier
WHERE Country = 'France' AND City = 'Paris'

SQL AND Operator - True and False

AND is a logical operator that enable multiple Boolean expressions to be combined. It only returns TRUE if both expressions are TRUE, and FALSE if either is FALSE; alternatively, it provides UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

Syntax:

The following illustrates the syntax of the AND operator:

boolean_expression AND boolean_expression

Any eligible Boolean expression that returns TRUE, FALSE, or UNKNOWN is a valid boolean expression.

Let us see the output of the AND operator in various use cases.

Expression 1 Expression 2 Expression 3
FALSE FALSE FALSE
FALSE TRUE FALSE
TRUE FALSE FALSE
TRUE TRUE FTRUE

The AND operator's result in many scenarios

Let's look at how to apply this operator in a SELECT statement. Examine the SQL statement below:

SELECT TOP (1000) [AddressID]
 ,[AddressLine1]
 ,[AddressLine2]
 ,[City]
 ,[StateProvince]
 ,[CountryRegion]
 ,[PostalCode]
FROM [BackupDatabase].[SalesLT].[Address] WHERE CountryRegion ='United Kingdom' AND StateProvince = 'London'

You can see only those records were returned where both the provided conditions were TRUE.

Condition 1: CountryRegion = “United Kindom” i.e. TRUE

Condition 2: StateProvince = “Landon” i.e. TRUE

While dealing with SQL Server tables, you may have discovered how the AND operator works.

Example 3: Use the logical AND operator to pick rows that must fulfill all of the specified constraints. For instance, if you wish to locate employees with salaries between 27000 and 30000 in the Employee table, you can use the AND operator as indicated in the query below.

SELECT * FROM Employee WHERE Salary >= 27000 AND Salary <= 30000;

SQL AND | OR Conditions Combined Together

The AND and OR conditions in SQL Server (Transact-SQL) can be mixed in a SELECT, INSERT, UPDATE, or DELETE statement.

It's necessary to use parenthesis when merging these conditions so the database knows what order to analyze them in. (Just like in Math class when you were studying the order of operations!)

Syntax:

In SQL Server, the AND and OR conditions are combined in this syntax:

WHERE condition1
AND condition2
...
OR condition_n;

Example 1: Consider the following SELECT statement, which combines the AND and OR criteria.

SELECT *
FROM employeeTable
WHERE (last_name = 'Jason' AND first_name = 'Kate')
OR (emp_id = 75);

This AND & OR example would return all employeeTable with the surnames 'Jason' and 'Kate,' as well as all employees with the emp_id 75. The sequence in which the AND and OR criteria are executed is determined by the parenthesis. Just as you learnt in math class about the order of operations!

Example 2:

Table: EMPLOYEE

ID EMP_NAME AGE LOCATION SALARY
123 Daryl 41 Mumbai 120000
124 Jon 40 Delhi 80000
125 Saru 43 Pune 110000
126 Hemant 39 Shimla 110000
127 Devesh 42 Goa 90000

The following SQL statement will retrieve the information for employees who are over 40 years old and work whether in "Mumbai" or "Pune".

SELECT * FROM EMPLOYEE
WHERE AGE>40 AND (LOCATION='Mumbai' OR LOCATION='Pune');

Result:

ID EMP_NAME AGE LOCATION SALARY
123 Daryl 41 Mumbai 120000
125 Saru 43 Pune 110000

Example 3: To make even more sophisticated SQL statements, add the AND and OR conditions (you may need to use parentheses to form complex expressions). Imagine the following scenario: we want to locate all employees who live in London AND are over the age of 30 OR employees who live in Masco regardless of age.

The syntax for this multiple condition SQL statement would like like the following:

SELECT *
FROM tableEmployee
WHERE (EmployeeTown = 'Berlin' AND EmpAge > 30) OR EmpTown = 'Mosco'

This time, we see that the statement will return two records; the record for Janet Fuller (who lives in Berlin AND whose age is greater than 30), and also the record for Harry Web who lives in Mosco.

Output:

EmployeeName EmployeeAddress EmployeeTown EmployeePostcode EmployeeAge
Harry Webb 1002 Trinity Road Sth Mosco SN2 1JH 22
Janet Fuller 14 Garrett Hill Berlin SW1 40

Before evaluating conditions that utilise the logical OR, SQL Server analyses conditions that employ the logical AND. Always use parenthesis if you choose to be confident in how SQL Server will handle database queries with multiple operators.


SQL AND OR Operator Precedence

The AND operator in SQL Server takes priority over the OR operator (just like a multiplication operation takes precedence over an addition operation).

Example 1: The employee list is displayed using the SQL Server SELECT command below:

(First condition) have the role ‘DBA’.

Or:

(Second condition) have the role ‘Manager’ and whose salary is higher than 8000.

SELECT *
FROM employees
WHERE    Job_Title = 'DBA'
OR
Job_Title = 'Manager' AND Salary > 8000;

Example 2: And has precedence over Or, so, even if a <=> a1 Or a2, Where a And b is not the same as Where a1 Or a2 And b, because that would be Executed as Where a1 Or (a2 And b) and if you're doing the following to make them all the same (using parentheses to override rules of precedence) Where (a1 Or a2) And b :

Here's an example to illustrate:

Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0

Select Case When @x = 1 OR @y = 1 And @z = 1 Then 'T'
Else 'F'
End

Otherwise

Select Case When(@x = 1 OR @y = 1) And @z = 1 Then 'T'
Else 'F'
End

Output:

F

Example 3: how the operator precedence affect its execution:

SELECT ename, DOB
FROM emp
WHERE ename = ‘SCOTT’ OR ename = ‘CHRIS’
AND DOB >= ‘1998-08-01’;

Because AND takes precedence over OR, this query will look for rows that have the following values: ename SHERIN AND DOB greater than '1998-08-01' OR ename COAL.

Let's try adding brackets to the same query and see what happens. The following is the new query:

SELECT ename, DOB
FROM emp
WHERE (ename = ‘SHERIN’ OR ename = ‘COAL’)
AND DOB >= ‘1998-08-01’;

SQL OR Operator

The OR operator is the inverse of the AND operator. When several conditions are merged using the OR operator, all records from the database that meet any of the supplied conditions are obtained.

The OR operator tells DB2 to return rows that satisfy one or both of the conditions.

Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition_1 OR condition_2 ...;

Example 1: SQL OR Example

Table: EMPLOYEE

ID EMP_NAME AGE LOCATION SALARY
90 David 30 Pune 10000
91 Steve 31 Delhi 9000
92 Ajeet 29 Gurgaon 11000
93 Rahul 33 Noida 19000
94 Pappu 35 Pune 9900

Let's build some SQL statements to get the details of the employees who are either "more than 30" or have a "Pune" address.

SELECT *
FROM EMPLOYEE
WHERE AGE > 30 OR ADDRESS = 'Pune';

Result:

ID EMP_NAME AGE LOCATION SALARY
90 David 30 Pune 10000
91 Steve 31 Delhi 9000
93 Rahul 33 Noida 19000
94 Pappu 35 Pune 9900

Explanation:

  • Because the second criteria (ADDRESS = 'Pune') is met, row number 1 is included in the results.
  • Because they match the first requirement (AGE > 30), rows 2 and 4 are included in the results.
  • Because it fits both conditions, the fifth row is included in the results.

Example 2: When numerous conditions are joined using the OR operator, all rows that meet any of the stated conditions will be given. To apply this to our members table, run the following query:

SELECT *
FROM members
WHERE Location = 'New York' OR LastName = 'Hanks'

Example 3:

SELECT ProductId, ProductName, Price
FROM Product
WHERE Category = 'Accessories' OR Price > 10;

This SQL statement retrieves the ProductId, ProductName and Price for any products of either the Category = 'Accessories' or Price > 10.

Result:

ProdectId ProductName Price
7001 Mouse 75.00
7003 Keyboard 36.00
7007 Mousepad 5.00

SQL OR Operator in Where Clause

If any of the conditions divided by the OR operator value is True, the record from the table is shown. It is also referred to as the conjunctive operator, and is used in conjunction with the WHERE clause.

With SELECT, UPDATE, INSERT, and DELETE statements, the WHERE clause with an OR operator can be used.

When we use AND to merge two or more conditions, the result will contain records that meet all of the stated criteria.

However, in the case of the OR operator, any record in the resultset must satisfy at least one of the constraints provided.

Syntax:

The OR operator with a WHERE clause has the following basic syntax:

SELECT col1, col2, colN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

The OR operator can be used to combine N criteria. Any ONE of the conditions divided by the OR must be TRUE for the SQL statement to take action, whether something is a transaction or a query.

For example:

WHERE ID = 1 OR ID = 15 OR ID = 20;

I'm utilising the sto_employees table using the OR operator. I utilised the ID column with the OR operator for several conditions in the SELECT statement with WHERE clause, as follows:

Example 1:

SELECT id, emp_Name, emp_age, emp_salary
FROM sto_employees
WHERE id=2 OR id=5 OR id=8 OR id=20;

Example 2: Example of OR operator:

Consider the following Emp table

Eid Name Age Salary
401 Anu 22 5000
402 Shane 29 8000
403 Rohan 34 12000
404 Scott 44 10000
405 Tiger 35 9000
SELECT * FROM Emp WHERE salary > 10000 OR age > 25;

The above query will return records where either salary is greater than 10000 or age is greater than 25.

Eid Name Age Salary
402 Shane 29 8000
403 Rohan 34 12000
404 Scott 44 10000
405 Tiger 35 9000

Example 3: Let's understand the below example which explains how to execute OR logical operator in SQL query:

This example consists of an Employee_details table, which has three columns Employee_Id, Employee_Name, Employee_Salary, and Employee_City.

Employee Id Employee Name Employee Salary Employee City
201 Abhay 25000 Chennai
202 Ankit 45000 Chandigarh
203 Bheem 30000 Chennai
204 Ram 25000 Chennai
205 Sumit 40000 Kolkata

If we want to access all the records of those employees from the Employee_details table whose salary is 25000 or the city is Chennai. For this, we have to write the following query in SQL:

SELECT * FROM Employee_details WHERE Employee_Salary = 25000 OR Employee_City = 'Chennai';

Here, SQL OR operator with WHERE clause shows the record of employees whose salary is 25000 or the city is Chennai.

Example 4: Consider the CUSTOMERS table having the following records:

ID Name Age Address Salary
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Chennai 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

The following code block has a query, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years.

SELECT ID, NAME, SALARY 
FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25;

Result:

ID Name Salary
3 kaushik 2000.00
4 Chaitali 6500.00
5 Hardik 8500.00
6 Komal 4500.00
7 Muffy 10000.00

SQL OR Operator - True False

The OR logical operator in SQL Server allows users to add two Boolean expressions. When one of the requirements is TRUE, the logical OR yields TRUE; otherwise, it returns FALSE. Otherwise, UNKNOWN is returned (an operator that has one or two NULL expressions returns UNKNOWN).

Syntax:

The following illustrates the syntax of the SQL OR operator:

SELECT column1,
	column2,
        ...
FROM
	table1
WHERE
	expression1
OR expression2;

Example 1: To get data of 'customer_code', 'customer_name', 'customer_city', 'customer_country' and 'grade' from the 'customer' with following conditions either 'customer_country' is ’UK’, or 'grade' of the 'customer' is 3, the following SQL statement can be used :

SELECT customer_code,customer_name,
customer_city,customer_country,grade
FROM customer
WHERE customer_country = 'UK' OR grade = 3;

Output:

CUST_CODE CUST_NAME CUST_CITY CUST_COUNTRY GRADE
C00001 Micheal New York UK 2
C00020 Albert New York UK 3
C00010 Charles Hampshiar UAE 3
C00012 Steven San Jose UK 1
C00009 Ramesh Mumbai India 3

SQL OR with Case Statement

CASE expressions support comparison operators as well as AND and/or OR operators between Boolean expressions. The simple CASE expression can't handle Boolean expressions because it just looks for equivalent values.

There are two forms of CASE expression, 'searched' and 'simple'.

Example : You can't use an OR with a 'simple' CASE expression, but you can with the 'searched' form:

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID = 2 Or
         OrderID = 3 Then 'Customer2'
    Else 'Unknown Customer'
End