Operators
Order of Precedence
| Precedence | Operator | Name |
|---|---|---|
| 1 | ~ | Bitwise NOT |
| 2 | * | Multiplication |
| 2 | / | Division |
| 2 | % | Modulus |
| 3 | - | Negative |
| 3 | + | Addition |
| 3 | + | Concatenation |
| 3 | || | Concatenation |
| 3 | - | Subtraction |
| 3 | & | Bitwise AND |
| 3 | ^ | Bitwise Exclusive OR |
| 3 | | | Bitwise OR |
| 4 | = | Comparison |
| 4 | > | Greater Than |
| 4 | < | Less Than |
| 4 | >= | Greater Than Or Equal |
| 4 | <= | Less Than Or Equal |
| 4 | <>, != | Not Equals |
| 4 | !> | Not Greater Than |
| 4 | !< | Not Less Than |
| 5 | NOT | Logical Not |
| 6 | AND | Logical And |
| 7 | ALL | All Set Operator |
| 7 | ANY | Any Set Operator |
| 7 | BETWEEN | Between Set Operator |
| 7 | IN | In Set Operator |
| 7 | LIKE | Pattern Operator |
| 7 | OR | Logical Or |
| 7 | SOME | Some Set Operator |
| 8 | = | Assignment |
Operators Enumerated
Arithmetic Operators
The arithmetic operators should be familiar to everyone. They are the same math operators you learned in elementary school. Some of them are Addition (+), Subtraction (-), Multiplication (*), Division (/), and Negation (-).
Comparison Operators
Comparison operators compare two values. The biggie that you will use often is the equality operator which is an equals sign. The sign causes the two values on either side to be compared to see if they are the same value. When comparing values, remember the data types. You can confidently only compare two values of the same type. For example, if you try to compare the number one to the character '1' (1 = '1') the return value will be false.
If you need to compare two different types you must convert one of the values into the other other's type. This is called casting. We will investigate casting later.
The not equals operators are a combination of comparison and negation. They will return true only if the values are not the same. The official not equals operator is <>. But some databases also allow !=. This is actually more in keeping with other operators. The exclamation point is used to negate values.
The comparison operators also contain the inequality operators. They are the normal inequality operators you learned in school. They are less than (<), greater than (>), less than or equal (<=), and greater than or equal (>=). There are also two negated versions, not less than (!<), and not greater than (!>).
There is also a modulus operator (%). To understand modulus think back to early elementary school. Before children are taught floating point division they are taught to do division with a remainder. If teacher asks the student to divide 3 into 10 the expected response is 3 goes into 10 3 times with a remainder of 1. Modulo does the division and only keeps the remainder.
Modulo can be really nifty when dealing with values that are cyclical in nature. Lets look at a list of values and their modulo 6 values.
| Modulo | Value |
|---|---|
| 0 % 6 | 0 |
| 1 % 6 | 1 |
| 2 % 6 | 2 |
| 3 % 6 | 3 |
| 4 % 6 | 4 |
| 5 % 6 | 5 |
| 6 % 6 | 0 |
| 7 % 6 | 1 |
| 8 % 6 | 2 |
| 9 % 6 | 3 |
| 10 % 6 | 4 |
| 11 % 6 | 5 |
| 12 % 6 | 0 |
| 13 % 6 | 1 |
| 14 % 6 | 2 |
As you can see, the modulo value cycles from zero to six repetitively. This means you can generate a repeating set of values. If you feed random values into modulo you can restrict the output to a range of values. For example random numbers modulo 6 will emulate the roll of a six sided die. It is also used for clocks and degrees of a circle.
Bitwise Operators
Bitwise operators are mathematical operators for integers. The operators convert the integer to binary values and then operate on the binary value.
Bitwise not (~) converts all ones in the number to zeros and vice versa. For example, decimal 12 equals binary 1100. Not (~1100) results in 0011.
Bitwise and (&) compares two integers in binary. For each digit, if both numbers are 1 then the result will have a value of 1. For example, binary 1010 & 1100 results in 1000.
Bitwise or (|) does the same as and except the result is a 1 if either inputs are 1. An example is 1010 | 1100 = 1110. Exclusive or (^) is the same as or but one input must be 1 and the other value must be 0 for the output to be 1. 1010 ^ 1100 = 0110.
Quite frankly, when you are first learning SQL the only time you will need bitwise operators are for true and false values. True and false values are called booleans. In many cases booleans can be modified using bitwise operators. In SQLite databases booleans are actually stored as ones and zeros.
Strings
We already learned to use the CONCAT() function. An alternative is to use the concatenation operator (||) or the other concatenation operator (+). Be aware that some databases do not recognize the plus sign as a concatenation operator.
SELECT 'A' + 'B' AS C1, 'a' || 'b' AS C2;
LIKE
Then we have the pattern operator LIKE. The LIKE operator compares two strings to see if they are similar instead of the same. It is commonly used in WHERE statements. To use it create a pattern that describes the text you want to match. To do this you create a string with wildcard characters. The underscore character (_) will match to any one character. The percent sign wildcard (%) will match to any one or more characters. Consider a table with the following values.
| Value |
|---|
| abc123 |
| abc124 |
| abc111 |
| abc1111 |
| xyz123 |
| xyz1 |
SELECT Value from x WHERE Value LIKE 'abc12_';
This query will select both "abc123" and "abc124". The underscore in the pattern will match to '3' or '4'.
SELECT Value from x WHERE Value LIKE 'abc%';
This query will match to the first four values. The percent sign in the pattern will match to '123', '124', '111', and '1111'. The number of characters it will match to is unlimited. It will also match to 'abc0000000000000000000000000000000000'. We can also combine wildcards.
SELECT Value from x WHERE Value LIKE '___1%';
This query will select all of the records. The pattern starts with three underscores so it will match to any string with exactly three characters followed by a literal '1'. The trailing percent sign at the end of the pattern will match to any number of characters. It even matches to no characters. The last value matches to the underscores in the pattern with 'xyz'. It matches to the literal 1. An then it matches to the percent sign because it does not have any other characters.
There are other wildcards besides underscore and percent sign. But there is no standard for them. They may be available depending on the database you are querying.
Logical Operators
We have already been using logical operators. We have used AND, OR, and Not. In many of our WHERE statements. When these operators are used they cause the values on either side of them to be converted to boolean values. Then the boolean values are evaluated to create an overall boolean. The exception is the NOT operator which only affects the value to it's right. NOT simply flips the value from true to false and vice versa.
Set Operators
Set operators determine if a value is a member of a set of values (or not). The set operator we have already used is the IN operator. To use IN we need to supply the operator a list of values. We can specify the list explicitly or select them as a subquery.
SELECT * FROM person
WHERE person_first IN ('Bob', 'Dolly');
In this example we have an explicitly enumerated list of two values. But suppose we want to select the students from the person table who have a Pell grant on the award table. We start by writing a subquery that selects the award_ids from the awards that are for Pell funds. We use IN to check if the person_id is in that subquery data set.
SELECT * FROM person WHERE person_id IN ( SELECT award_id FROM award WHERE award_fund = 'PELL' );
| person_id | person_first | person_last |
|---|---|---|
| X00001 | Alex | Alexander |
| X00002 | Bob | Baker |
| X00003 | Charlene | Crystal |
| X00005 | Edgar | East |
| X00008 | Helen | Helene |
| X00010 | John | Jacobson |
The ANY operator is similar to the IN operator. It looks to see if the given value is in a data set.
SELECT * FROM person WHERE person_id = ANY ( SELECT award_id FROM award WHERE award_fund = 'PELL' );
The advantage of ANY is that you can use another operator instead of the equality operator (=). A synonym for ANY is SOME.
Another set operator is ALL. This works the same and ANY but all of the values in the set must resolve to true for the operator to result in true.
Between
Our final set operator is BETWEEN. This one is little different from the others. Instead of specifying a set you give BETWEEN a start and end value. It will then see if the test value is between the given values. It is inclusive of the both end points. The end points must be separated with the keyword AND.
SELECT award_id, award_fund, award_offered FROM award WHERE award_offered BETWEEN 100 AND 150;
| award_id | award_fund | award_offered |
| X00001 | PELL | 100.0 |
| X00001 | SEOG | 101.0 |
| X00001 | SUB | 102.0 |
| X00001 | UNSUB | 103.0 |
| X00010 | PELL | 100.0 |
| X00010 | SEOG | 110.0 |