Warning: call_user_func_array() expects parameter 1 to be a valid callback, function 'fop_enqueue_conditional_scripts' not found or invalid function name in /home/skillcu1/datanerds.io/wp-includes/class-wp-hook.php on line 324
SQL Logical Operators – Datanerds.io

SQL Logical Operators

SQL Logical Operators

  • Read
  • Discuss

An operator in SQL is a reserved word or a character that is used to query our database in a SQL expression. SQL Logical Operators are necessary for learning SQL as they act as a connector between two or more conditions in an SQL query.

Logic operators in SQL are used to check whether the condition is true. These operators  return a Boolean value of TRUE, FALSE, or UNKNOWN.

Most important logical operators that are used in advanced SQL are:

Important Logical Operators in SQL

1. AND Operator

The AND operator in SQL is used for data filtering and obtaining precise results based on conditions.

AND operator is used to combine two or more conditions and displays only those records in the result set where both the conditions condition1 and condition2 evaluates to True. 

Syntax for AND Operator

SELECT column_name
FROM table_name
WHERE condition1 AND condition2;

Example for AND Operator

If we want to find records of all those owners who own a pet and are living in Southfield, we can write following query:

SELECT o.OwnerID,o.Name as OwnerName,p.PetID
FROM Owners as o, Pets as p
WHERE o.OwnerID=p.OwnerID AND o.City='Southfield';

Output:

2. OR Operator

The OR operator in SQL is used for data filtering and obtaining precise results based on conditions.

OR operator is used to combine two or more conditions and displays only those records in the result set where either one of the two conditions, condition1 and condition2, evaluates to True. 

Syntax for OR Operator

SELECT column_name
FROM table_name
WHERE condition1 OR condition2;

Example for OR Operator

If we want to find records of all those owners who own a pet OR they are living in Southfield, we can write following query:

SELECT o.OwnerID,o.Name as OwnerName,p.PetID
FROM Owners as o, Pets as p
WHERE o.OwnerID=p.OwnerID OR o.City='Southfield';

Output:

3. BETWEEN Operator

The SQL BETWEEN operator allows you to select data within a range of values (inclusive).

This operator evaluates to TRUE when the column value is less than or equal to a high value and greater than or equal to a low value. The starting and ending value is included in the result set.

Syntax for BETWEEN Operator

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example for BETWEEN Operator

In the example below, records of all pets having age between 7 and 13 (including 7 and 13) will be in the result set

If we want to find records of all the procedures that were carried out between 1/17/2016 and 1/22/2016, we can write following query:

SELECT * FROM ProceduresHistory a
WHERE Date BETWEEN '1/17/2016' AND '1/22/2016';

Output:

4. IN Operator

IN operator in SQL allows you to test if the value/expression lies in the list of values. It is used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE or DELETE. You can also use NOT IN to exclude the rows in your list. 

Syntax for IN Operator

SELECT column_name
FROM table_name
WHERE column_name IN (list-of-values)

Example for IN Operator

If we want to find records of all those pets whose age in either 3,5,9 or 11, we can write following query:

SELECT * FROM Pets
WHERE Age IN (3,5,9,11);

Output:

5. Wildcard Operator

SQL wildcards are used to search for data within a table. Wildcard operators are used with the SQL LIKE operator.

Most important wildcards are:

%A substitute for zero or more characters
_A substitute for a single character
LIKE OperatorDescription
WHERE CustomerName LIKE ‘a%’Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a__%’Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’Finds any values that starts with “a” and ends with “o”

Syntax for Wildcard Operator

SELECT column_name
FROM table_name
WHERE column_name LIKE ‘abc%’;

Example for Wildcard Operator

If we want to find records of all those owners whose zip code starts with “49”, we can write following query:

SELECT column_name
FROM table_name
WHERE column_name LIKE ‘abc%’;

Output:

Leave a Reply


Posted

in

by

Tags:

Comments

Leave a Reply