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.

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

Table: EMPLOYEE

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:

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:

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' 

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.

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:

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.

Example 3: List all suppliers in Paris, France.

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

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.

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;

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

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:

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:

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.

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:

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’;

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

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:

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:

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

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.

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.

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:

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:

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:

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