The Operators in the SQL Language - SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

Chapter 5. The Operators in the SQL Language

Operators are reserved words or characters that you can use for your SQL commands. Generally, operators are used in the WHERE section of your commands to conduct operations (e.g. comparisons, mathematical operations, etc.).

You can use an operator to specify a condition in your SQL statements. In some cases, you may utilize an operator as a conjunction if your commands involve multiple conditions.

This chapter will discuss the four types of operators supported by SQL:

· Logical Operators

· Arithmetic Operators

· Comparison Operators

· Operators that can nullify conditions

The Logical Operators

These are the logical operators that you can use in the SQL computer language:

· IN– You can use this operator to compare a value against your specified literal values.

· OR– This operator combines various conditions in the WHERE section of your SQL commands.

· AND– This operator allows you to include multiple conditions in the WHERE clause of your SQL commands.

· ALL– This operator compares a value against values that are inside a different value set.

· ANY– This operator uses a condition to perform comparisons.

· LIKE– This operator uses wildcard operators to compare values against similar ones.

· UNIQUE– This operator checks the uniqueness of your entries. To accomplish this, the UNIQUE operator scans the entire table and searches for redundant data.

· EXISTS– This operator searches for rows that meet specified criteria.

· BETWEEN– This operator searches for values that are inside a certain range. When using BETWEEN, you should indicate the highest value and the lowest value.

· IS NULL– This operator compares a value against a NULL value.

The Arithmetic Operators

To help you understand these operators, let’s use two sample variables: x = 1; y = 2.

· “+” – You should use this operator to perform addition. For instance, x + y = 3.

· “-” – You must use this operator to perform subtraction. It will subtract the value of the right operand from that of the left operand. For example, y– x = 1.

· “*” – You should use this operator when performing multiplication. Here’s an example: x * y = 2.

· “/” – You should use this operator when performing division. For example: y / x = 2.

The Comparison Operators

Let’s assume that x = 2 and y = 4.

· “=” – This operator checks the equality of two values. If the values are equal, the condition is true. For example: (x = y) is not true.

· “!=” – This operator checks the equality of two values. If the values are unequal, the condition is true. For example: (y != x) is true.

· “<>” – This operator is the same as“!=”. For example: (x <> y) is true.

· “>” – This operator checks if the left operand’s value is greater than that of the right operand. If it is, the condition is true. For instance: (y > x) is true.

· “<” – This operator checks whether the left operand’s value is less than that of the right operand. If it is, the condition is true. For instance: (x < y) is true.

· “>=” – This operator checks if the left operand’s value is greater than or equal to that of the right operand. If it is, the condition is true. For example: (y >= x) is true.

· “<=” – This operator checks if the left operand’s value is lesser than or equal to that of the right operand. If it is, the condition is true. For instance: (x <= y) is true.

The Operator that can Nullify Conditions

· NOT– This operator can reverse the function of the logical operator you’ll use it with. For example: NOT IN, NOT EXISTS, NOT BETWEEN, etc.