Database Tutorial

Oracle

This shows you how to write equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol compares numbers of the same data type. It also lets you compare different numeric data types provided the database knows how to cast them implicitly to a common data type. Numeric comparisons like these occur between primary and foreign keys.

SELECT 'Truth' AS OUTPUT FROM dual WHERE 1 = 1;

That looks straightforward because it is. After all, any number entered as a number shares the same data type with the other numbered entered. What happens when the numbers have different data types? Here’s a script that will let us verify the behavior.

-- A quick little block to conditionally drop the table when it exists.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'COMPARATIVE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
  END LOOP;
END;
/
 
-- Create a table with different numeric data types.
CREATE TABLE comparative
( int_column           INTEGER
, float_column         FLOAT
, number_column        NUMBER
, binary_double_column BINARY_DOUBLE
, binary_float_column  BINARY_FLOAT );
 
-- Insert values for the various types, note the 'd' for binary double and 'f' for binary float.
INSERT INTO comparative VALUES ( 1, 1.1, 1.1, 1d, 1.1f);
INSERT INTO comparative VALUES ( 1, 2.1, 2.1, 2d, 2.1f);

You can write a query comparing columns with the same value but different data types. For example, here’s a couple comparing an integer and IEEE-754 double and an Oracle NUMBER and IEEE-754 float.

1
2
3
4
5

SELECT 'True Statement' AS OUTPUT
,       int_column AS "INTEGER"
,       binary_double_column AS "BINARY_DOUBLE"
FROM    comparative
WHERE   int_column = binary_double_column;

The results returned are:

OUTPUT            INTEGER BINARY_DOUBLE
-------------- ---------- -------------
True Statement          1      1.0E+000

This query only returns one of the two rows because it filters on the match. The match is made by implicitly casting the data type and then comparing values. It only returns the row where the value of the INT_COLUMN is equal to the value of the BINARY_DOUBLE_COLUMN.

1
2
3
4
5

SELECT 'True Statement' AS OUTPUT
,       int_column AS "INTEGER"
,       binary_double_column AS "FLOAT"
FROM    comparative
WHERE   int_column = float_column;

The comparison of these rows is always unmet because one column contains a whole number and the other number contains a whole number and decimal component. The number 1 isn’t equal to the number 1.1 or 2.1.

You can find the dissimilar numbers by using negation in the WHERE clause, like this:

1
2
3
4
5

SELECT   'True Statement' AS OUTPUT
,         int_column AS "INTEGER"
,         binary_double_column AS "FLOAT"
FROM      comparative
WHERE NOT int_column = float_column;

It returns both columns because the values in the compared columns don’t match:

OUTPUT            INTEGER      FLOAT
-------------- ---------- ----------
True Statement          1   1.0E+000
True Statement          1   2.0E+000

SQL> UPDATE comparative
  2  SET    int_column = binary_float_column;

When you query the table after the UPDATE statement, you note that the value in the int_column is still one. The decimal equivalent of the float_column value was truncated.

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators to see which number literal or numeric column value is larger or smaller.

SELECT 'True Statement' FROM dual WHERE 2 > 1;

As with the implicit behavior noted above, inequalities likewise enjoy implicit data type casting without loss of precision.

Range Comparisons

You can use the BETWEEN operator with the AND operator to perform a range comparison. It determines whether the left operand value to the BETWEEN operator is between the two range values separated by the AND operator. This is an inclusive range variable comparison, which means the initial number can be equal to the either range value or anywhere in between. While this example uses ordinary numeric literals, column values can be substituted.

SELECT 'True Statement' FROM dual WHERE 5 BETWEEN 1 AND 7;

As with the implicit behavior noted above, range comparisons likewise enjoy implicit data type casting without loss of precision.

MySQL

This shows you how to write equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol compares numbers of the same data type. It also lets you compare different numeric data types provided the database knows how to cast them implicitly to a common data type. Numeric comparisons like these occur between primary and foreign keys.

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 1 = 1;

Here’s a quick set of data that allows you to test implicit type casting during numeric casting operations.

-- Conditionally drop the table.
DROP TABLE IF EXISTS comparative;
-- Create a comparative numeric data type table.
CREATE TABLE comparative
( int_column   INT UNSIGNED
, float_column FLOAT UNSIGNED );
-- Insert a row of data.
INSERT INTO comparative VALUES (1,1.1);

This statement fails to return a row because 1 doesn’t equal 1.1 regardless of data type.

SELECT 'True Statement' AS OUTPUT
FROM    comparative
WHERE   int_column = float_column;

This statement succeeds because it casts the 1.1 float data type to an integer, which says discard everything to the right of the decimal point.

SELECT 'True Statement' AS OUTPUT
FROM    comparative
WHERE   int_column = CAST(float_column AS UNSIGNED INTEGER);

It would fail if you tried to cast it to an ordinary integer because your override casting operation would fail to match the other column’s data type. The only complexity in MySQL is to remember whether you require a signed or unsigned data type before you explicitly cast one side of a comparison operation.

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators to see which number literal or numeric column value is larger or smaller.

SELECT 'True Statement' FROM dual WHERE 2 > 1;

Range Comparisons

Range comparisons are very simple with numbers. Here’s a quick example with explicit casting of the bounding range:

SELECT 'True Statement' AS OUTPUT
FROM    comparative
WHERE   float_column BETWEEN 1 AND CAST(2 AS SIGNED INT);

SQL Server

This shows you how to write equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol compares numbers of the same data type. It also lets you compare different numeric data types provided the database knows how to cast them implicitly to a common data type. Numeric comparisons like these occur between primary and foreign keys.

SQL> SELECT 'Truth' AS OUTPUT WHERE 1 = 1;

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators to see which number literal or numeric column value is larger or smaller.

SELECT 'True Statement' WHERE 2 > 1;

Range Comparisons

Range comparisons are very simple with numbers. Here’s a quick example searching for a float amid two integer boundaries:

SELECT 'True Statement' AS OUTPUT WHERE   1.5 BETWEEN 1 AND 2;

Oracle

This shows you how to work with both equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol compares case sensitive strings. The following query returns nothing because a title case string isn’t equal to an uppercase string (databases, as they should be).

SQL> SELECT 'True Statement' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

The query only resolves when the strings are case sensitive matches, like this:

SQL> SELECT 'True Statement' AS OUTPUT FROM dual WHERE 'String' = 'String';

You can force Oracle to compare case insenstivie strings by using the the LOWER or UPPER functions. You must apply them to both operands compared by the = operator. The following shows you how to perform a case-insensitive match with the LOWER function in the WHERE clause:

SELECT 'True Statement' FROM dual WHERE LOWER('String') = LOWER('STRING');

Alternatively, you can use the UPPER function like:

SELECT 'True Statement' FROM dual WHERE UPPER('String') = UPPER('STRING');

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators with case sensitive strings too:

SELECT 'True Statement' FROM dual WHERE 'String2' > 'STRING1';

The LOWER or UPPER functions let you also perform case insensitive comparisons. The following example uses the UPPER function:

SELECT 'True Statement' FROM dual WHERE UPPER('String2') > UPPER('STRING1');

Range Comparisons

Range comparisons aren’t straight forward in Oracle if you expect them to perform as natural language comparisons. Natural language comparisons treat uppercase letters as before lowercase letters, which differs from their ASCII numeric values. An uppercase A is ASCII 65, an uppercase Z is ASCII 90, and their lowercase equivalents start with ASCII 97 and end with ASCII 122. Oracle works on a case sensitive match between those inclusive range numbers.

This means you can do a range map with uppercase letters against two uppercase strings, or a lowercase match between two lowercase strings. You can’t perform mixed case searches.

SELECT 'True Statement' FROM dual WHERE UPPER('the') BETWEEN 'THE' AND 'THEY';

Alternatively, you can search for a lowercase string with lowercase ranges.

SELECT 'True Statement' FROM dual WHERE LOWER('THE') BETWEEN 'the' AND 'they';

MySQL

This shows you how to work with both equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (a case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol. You may also chuckle that you need the DUAL pseudo table to make this work when there’s a WHERE clause, covered in this blog post.

mysql> SELECT 'True Statement' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

You can make it case sensitive by using the strcmp function, like this:

mysql> SELECT 'True Statement' AS OUTPUT FROM dual WHERE strcmp('String','STRING');

You can also use collation to effect a case sensitive match. The syntax is a bit difficult because it doesn’t fit well in generic SQL terms, but it works. This is required over calling the CAST function because you can’t cast a string to a binary string in MySQL.

mysql> SELECT 'True Statement' AS OUTPUT
    -> FROM dual WHERE 'String' COLLATE latin1_bin = 'STRING' COLLATE latin1_bin;

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators with strings too, but by themselves, you can only perform inquality evaluations for case insensitive comparisons:

SELECT 'True Statement' FROM dual WHERE 'String2' > 'STRING1';

You can overcome this native limitation by using collation. Collation is the process of leveraging the character set to perform an operation. In this case you use the COLLATE keyword to translate a variable length string into a variable length binary string. Since this evaluates a few scenarios a small script seems helpful. You should run this first.

-- Drop table where it exists.
DROP TABLE IF EXISTS alpha;
-- Create two column table.
CREATE TABLE alpha_omega
( char1 VARCHAR(1)
, char2 VARCHAR(1));
-- Insert three rows into table.
INSERT INTO alpha_omega VALUES ('A','a'),('A','A'),('a','A');

Now you can see that a capital letter A isn’t equal to a lowercase letter a:

mysql> SELECT CONCAT('Character [',char1,'] <> [',char2,']')
    -> FROM   alpha_omega
    -> WHERE  char1 COLLATE latin1_bin <> char2 COLLATE latin1_bin;

It returns the two rows where they’re unequal.

+------------------------------------------------+
| CONCAT('Character [',char1,'] <> [',char2,']') |
+------------------------------------------------+
| Character [A] <> [a]                           |
| Character [a] <> [A]                           |
+------------------------------------------------+

If you check where char1 is greater than char2, you’ll find that’s only the case when the char1 letter is lowercase. That’s because uppercase letters come first in ASCII sequence numbers, A is ASCII 65 through Z which is ASCII 91. Lowercase letters follow the uppercase letters, starting with ASCII 92.

mysql> SELECT CONCAT('Character [',char1,'] > [',char2,']')
    -> FROM   alpha_omega
    -> WHERE  char1 COLLATE latin1_bin > char2 COLLATE latin1_bin;

As noted, it returns the lowercase letter on the left as greater than the uppercase letter. Unfortunately, natural sorting would expect the capital or uppercase letter to be greater than the lowercase letter.

+-----------------------------------------------+
| CONCAT('Character [',char1,'] > [',char2,']') |
+-----------------------------------------------+
| Character [a] > [A]                           |
+-----------------------------------------------+

When you want to perform a natural language comparison of strings, you must invert the inequality operator. That will place capital letters ahead of lowercase letters.

Range Comparisons

Range comparisons are straight forward if you want to work with case insensitive matches but much more complex. Actually, in MySQL they’re not possible without too much work. This sections shows you how to perform a case insensitive range match for the uppercase word THE between the THE and THEY range boundary words. Note that the lower bound value is a match for the search word, which demonstrates that range searches are use an inclusive range.

SELECT 'True Statement' FROM dual WHERE UCASE('the') BETWEEN 'THE' AND 'THEY';

SQL Server

This shows you how to work with both equality and inequality comparison operators.

Equality Comparisons

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (another case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol.

1> SELECT 'Truth' AS Output
2> WHERE  'String' = 'STRING';
3> GO

You can make SQL Server resolve case sensitive strings by casting both of the strings to VARBINARY data types, like this:

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('String' AS VARBINARY);
3> GO

Inequality Comparisons

You can use the >=, >, <=, or < traditional inequality comparison operators with strings too, but only for case insensitive comparisons:

SELECT 'True Statement' WHERE 'STRING2' > 'STRING1';

Range Comparisons

Range comparisons are generally case insensitive in SQL Server.

SELECT 'True Statement' WHERE 'THE' BETWEEN 'THE' AND 'THEY';