This explains how a simple query works against a single table with and without aggregation. Probably the three significant pieces of information presented are: (a) the order of execution for the clauses of a SELECT
statement; how to work with null values as column returns values and as comparison values in the WHERE
clause; and (c) how to compare various data types in Oracle, MySQL, and SQL Server.
The SELECT
clause isn’t actually read first. The FROM
clause is read first. When that clause is read, any table aliases assigned in the FROM
clause are substituted for the full table name. This means when a query has two columns that use the same name, you can type the short alias instead of the full table name. That’s because the alias replaces the table name for the balance of the statement parsing.
Column references don’t require prepending a column with the table or view alias when they’re unique in the result set. Using the aliases does add clarity in any query. You do need to prepend table aliases or the full table names when two or more columns returned by the query have the same column names. You typically encounter this situation when you’re joining two or more tables on primary and foreign key columns that use the same column name(s). Failure to identify these duplicated column names by using the table or alias name causes the SQL parser to trigger an exception. Oracle’s error states that they’re ambiguously defined in the result set.
While the column aliases use the optional AS
keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH
statement (ANSI SQL: 1999) – blogged on the WITH
statement here.
Queries are viewed in two phases. One is selection and the other is projection.
Selection comes in two phases, identifying the sources or tables in the FROM
clause, and then filtering the sources based on comparisons in the WHERE
clause. The WHERE
clause filters the result set and determines how many rows are returned for a query or other DML statement. It is essential for you to understand how to perform comparisons in the WHERE
clause, manage null values in those comparisons, and handle null values returned in the SELECT
clause.
Null Values ↓
Null values can occur in two principle places in DML statements. They can be in filtering clauses, like the WHERE
clause. They can also be returned as column values in a SELECT
clause from a query. This section describes how to work with null values in both places and covers three valued logic in databases.
WHERE
Clause ↓
You filter data through the WHERE
clause. The WHERE
clause provides the ability to compare a column value against another column, literal value, or list of columns or values. You compare scalar data types by using the =
(equal), >=
, >
, <=
, or <
traditional comparison operators, and the IN
, =ANY
, =SOME
, =ALL
operators when the left operand is a scalar data type and the right operand is a collection (the collection can also be a subquery return set). If the two scalar data type values are equal or meet the inequality statement criteria, then the statement resolves to true and when they’re not false or unknown (unknowns arise from three-valued logic).
When the comparison involves unbalanced data types, like a left operand scalar data type and right operand collection, true is returned when the value is found within the set of values, or in the case of the =ALL
operator when the value is found to be equivalent to all values in the collection. False is returned from the unbalanced comparison when the value isn’t found in a set of values, and unknown is returned when the collection contains only null values.
Alternatively, you can use the WHERE NOT
to check whether a statement is false. The alternative to a false is true or unknown. Unknown occurs because one of the two values is a null value. Technically, the WHERE
clause is affirmative case and known as the f(x) (function of x), while the WHERE NOT
clause is the disqualifying case and also known as the f-c(x) (functional complement of x).
This section demonstrates techniques to compare values in the WHERE
clause where the left or right operand contains a null. That’s necessary because a null isn’t equal to anything. Therefore, comparison of something to a null with mathematical operators returns an unknown, not a true or false value. That leaves you with one of three possible values. This logical model is known as three-valued logic.
Three-valued logic ↓
Three-value logic is only a conundrum until somebody explains it. There’s a nice explaination that I’ve borrowed from the Oracle Database 11g PL/SQL Workbook. Click on the Three-valued Logic title if you’re interested in understanding it.
“Three-valued logic means basically that if you find something is true when you look for truth, it is true. By the same token, when you check whether something is false and it is, then it is false. The opposite case isn’t proved. That means when something isn’t true, you can’t assume it is false, and vice versa.
The third case is that if something isn’t true, it can be false or null. Likewise, if something isn’t false, it can be true or null. Something is null when a Boolean variable is defined but not declared or when an expression compares something against another variable that is null.”
Where not true?
This statement demonstrates that something isn’t equal to a null. However, it doesn’t tell us that something equals a null. Note that the FROM dual
clause for Oracle isn’t required in MySQL or SQL Server.
SELECT 'True' FROM dual WHERE 'Something' = NULL; |
SELECT 'True' FROM dual WHERE 'Something' = NULL;
We prove a contrary position that something doesn’t equal a null, like
SELECT 'True' FROM dual WHERE NOT 'Something' = NULL; |
SELECT 'True' FROM dual WHERE NOT 'Something' = NULL;
Or you use the <>
(not equals) operator, like
SELECT 'True' FROM dual WHERE 'Something' <> NULL; |
SELECT 'True' FROM dual WHERE 'Something' <> NULL;
Both return no rows found because something isn’t equal nothing and something isn’t not equal to something when that something is an unknown, like a null value. Just to be absolutely certain, you might try a combining the logical approaches like the following statement. It also returns no rows.
SELECT 'True' FROM dual WHERE 'Something' = NULL AND 'Something' <> NULL; |
SELECT 'True' FROM dual WHERE 'Something' = NULL AND 'Something' <> NULL;
The only way to resolve this question is to use another comparison operator because standard mathematical comparison symbols for equality and inequality don’t work in a three-valued logic world. You check whether a column value is null by using the IS
operator or whether a column value isn’t null by using the IS NOT
operator. These operators are limited to comparing to scalar variables, which means neither of the operands may holds a reference to a data stream.
You use the following syntax to check if a column value contains a null value:
WHERE some_column IS NULL |
WHERE some_column IS NULL
You use the following syntax to check if a column value doesn’t contain a null value:
WHERE some_column IS NOT NULL |
WHERE some_column IS NOT NULL
Null Value Functions ↓
Unfortunately, there isn’t a convenient way to substitute a value for a null value in the SELECT
clause (result set of a query). Individual database management systems provide functions that perform this task. They are implementation dependent and generally non-portable.
Oracle
The NVL(evaluation_input,substitution_value)
function is Oracle’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.
While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.
SELECT NVL('Something','Nothing') FROM dual; |
SELECT NVL('Something','Nothing') FROM dual;
This query returns the first call parameter value, Something
, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.
SELECT NVL(NULL,'Nothing') FROM dual; |
SELECT NVL(NULL,'Nothing') FROM dual;
The NULL
call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.
MySQL
The IFNULL(evaluation_input,substitution_value)
function is MySQL’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.
While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.
SELECT IFNULL('Something','Nothing'); |
SELECT IFNULL('Something','Nothing');
This query returns the first call parameter value, Something
, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.
SELECT IFNULL(NULL,'Nothing'); |
SELECT IFNULL(NULL,'Nothing');
The NULL
call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.
SQL Server
The first critical thing you should understand about Microsoft™ SQL Server is that it dislikes null columns. In fact, the behavior of SQL Server 2008 varies based on internal DBA parameter settings. Some SQL Server DBAs would tell you never design a table with a null allowed column, which is plainly absurd from a practical design standpoint. You have to recognize that DBAs want to control things in a reasonable and consistent way, which is easiet with null values if you disallow them.
If you have ANSI_NULLS
set to ON
you have a three-valued logic model. On the contrary, when ANSI_NULLS
is set to OFF
you have a two-valued logic model. In a three-valued logic model, if it’s not true doesn’t mean false but if’s not false doesn’t mean it’s true because both outcomes are unknown. Whereas, it can only be true or false in a two-valued logic model.
The ISNULL(evaluation_input,substitution_value)
function is Oracle’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.
While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.
SELECT ISNULL('Something','Nothing'); |
SELECT ISNULL('Something','Nothing');
This query returns the first call parameter value, Something
, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.
SELECT ISNULL(NULL,'Nothing'); |
SELECT ISNULL(NULL,'Nothing');
The NULL
call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.
Comparison Operators ↓
As mentioned in the “Null Values” discussion earlier, you can filter data through the WHERE
clause. The WHERE
clause provides the ability to compare a column value against another column, literal value, or list of columns or values. You compare scalar data types by using the =
(equal), >=
, >
, <=
, or <
traditional comparison operators, and the IN
, = ANY
, = SOME
, = ALL
operators when the left operand is a scalar data type and the right operand is a collection (the collection can also be a subquery return set). If the two scalar data type values are equal or meet the inequality statement criteria, then the statement resolves to true and when they’re not false or unknown (unknowns arise from three-valued logic).
You may also use the LIKE
operator, which lets you determine if the left operand is like a skeleton of the right operand. Skeleton describes a framework, like your bones, and means that something meatier covers it. The skeleton in the case of a LIKE
operator is a partial string with wildcard operators. ANSI SQL:89 forward supports the _
(underscore) as a single character wildcard, and the %
as a multiple character wild card.
Number equality, inequality, and range ↓
This shows you how to use these comparison operators with numbers in Oracle, MySQL, and SQL Server.
The comparison of numbers is the easiest because it works with simple equality and inequality statements. The following horizontally scrolling window demonstrates equality and inequalities.
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; |
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); |
-- 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; |
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 |
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; |
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; |
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 |
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; |
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; |
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; |
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; |
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); |
-- 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; |
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); |
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; |
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); |
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; |
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; |
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; |
SELECT 'True Statement' AS OUTPUT WHERE 1.5 BETWEEN 1 AND 2;
String equality, inequality, and range ↓
This shows you how to use these comparison operators with strings in Oracle, MySQL, and SQL Server.
Comparing strings in databases should be straightforward but it isn’t. The reason is that not all databases make comparisons with the same operators the same way. The =
(equals) comparison operator is the basis for equality and inequality comparisons, and it compares strings based on case sensitive or insensitive rules. For example, Oracle and PostgreSQL compare case sensitive strings with the =
operator, whereas, MySQL and SQL Server compare case insensitive strings.
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'; |
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'; |
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'); |
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'); |
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'; |
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'); |
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'; |
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'; |
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'; |
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'); |
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; |
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'; |
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'); |
-- 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; |
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] |
+------------------------------------------------+ |
+------------------------------------------------+
| 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; |
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] |
+-----------------------------------------------+ |
+-----------------------------------------------+
| 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'; |
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 |
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 |
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'; |
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'; |
SELECT 'True Statement' WHERE 'THE' BETWEEN 'THE' AND 'THEY';
Lookup Operators ↓
This shows you how to use the lookup operators find if a single value appears in a set of values, or a structure appears in a set of structures.
The idea of matching the value of a column or set of columns from one row against a list of potential values is accomplished with a lookup behavior. You have the IN
, =ANY
, =SOME
, =ALL
operators to perform lookup behaviors. The IN
, =ANY
, =SOME
operators act like a sequence of OR
operators; whereas the =ALL
operator acts like a sequence of AND
operators. The examples show how to match them against hard coded lists of values but you could just as easily substitute subqueries for the lists of values.
IN
Operator
The IN
operator has one of two behaviors. One is to lookup a scalar variable in a list of scalar variables, and the other is to lookup a structure in a list of the same structure. A structure is a collection of comma delimited variables.
Scalar Lookup
This looks up a scalar variable in a list of scalar variables and returns the True Statement because it finds the string Lancelot
in the list of values.
SELECT 'True Statement' FROM dual WHERE 'Lancelot' IN ('Arthur','Galahad','Lancelot'); |
SELECT 'True Statement' FROM dual WHERE 'Lancelot' IN ('Arthur','Galahad','Lancelot');
The IN
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot'; |
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot';
Structure Lookup
This looks up a structure in a list of structures and returns the True Statement because it finds the pair of string values: Harry Potter and the Order of the Phoenix
, PG-13
. Note that these structures are implicitly created by the SQL statement, and likewise the contents of the list could be the result of a SELECT
clause predicate.
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Order of the Phoenix','PG-13')
IN (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Order of the Phoenix','PG-13')
IN (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG'));
The IN
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Order of the Phoenix','PG-13') =
('Harry Potter and the Half Blood Prince','PG'));
=ANY Operator
The =ANY
operator has one of two behaviors. One is to lookup a scalar variable in a list of scalar variables, and the other is to lookup a structure in a list of the same structure. A structure is a collection of comma delimited variables.
Scalar Lookup
This looks up a scalar variable in a list of scalar variables and returns the True Statement because it finds the string Lancelot
in the list of values.
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =ANY ('Arthur','Galahad','Lancelot'); |
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =ANY ('Arthur','Galahad','Lancelot');
The =ANY
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot'; |
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot';
Structure Lookup
This looks up a structure in a list of structures and returns the True Statement because it finds the pair of string values: Harry Potter and the Half Blood Prince
, PG
. Note that these structures are implicitly created by the SQL statement, and likewise the contents of the list could be the result of a SELECT
clause predicate.
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Half Blood Prince','PG')
=ANY (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Half Blood Prince','PG')
=ANY (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG'));
The =ANY
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Half Blood Prince','PG') =
('Harry Potter and the Half Blood Prince','PG'));
=SOME Operator
The =SOME
operator has one of two behaviors. One is to lookup a scalar variable in a list of scalar variables, and the other is to lookup a structure in a list of the same structure. A structure is a collection of comma delimited variables.
Scalar Lookup
This looks up a scalar variable in a list of scalar variables and returns the True Statement because it finds the string Lancelot
in the list of values.
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =SOME ('Arthur','Galahad','Lancelot'); |
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =SOME ('Arthur','Galahad','Lancelot');
The =SOME
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot'; |
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Arthur'
OR 'Lancelot' = 'Galahad'
OR 'Lancelot' = 'Lancelot';
Structure Lookup
This looks up a structure in a list of structures and returns the True Statement because it finds the pair of string values: Harry Potter and the Socerer's Stone
, PG
. Note that these structures are implicitly created by the SQL statement, and likewise the contents of the list could be the result of a SELECT
clause predicate.
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Socerer''s Stone','PG')
=SOME (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Socerer''s Stone','PG')
=SOME (('Harry Potter and the Socerer''s Stone','PG')
,('Harry Potter and the Chamber of Secrets','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Goblet of Fire','PG-13')
,('Harry Potter and the Order of the Phoenix','PG-13')
,('Harry Potter and the Half Blood Prince','PG'));
The =SOME
operator prevents a set of OR logic conditions, like the following equivalent.
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Half Blood Prince','PG')); |
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Socerer''s Stone','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Chamber of Secrets','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Goblet of Fire','PG-13')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Order of the Phoenix','PG-13')
OR ('Harry Potter and the Socerer''s Stone','PG') =
('Harry Potter and the Half Blood Prince','PG'));
=ALL Operator
The =ALL
operator has one of two behaviors. One is to lookup a scalar variable in a list of scalar variables, and the other is to lookup a structure in a list of the same structure. A structure is a collection of comma delimited variables.
Scalar Lookup
This looks up a scalar variable in a list of scalar variables and returns the True Statement because it finds ONLY the string Lancelot
in the list of values.
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =ALL ('Lancelot','Lancelot','Lancelot'); |
SELECT 'True Statement' FROM dual WHERE 'Lancelot' =ALL ('Lancelot','Lancelot','Lancelot');
The =ALL
operator prevents a set of AND logic conditions, like the following equivalent. It only works when all the comparisons are true, which makes it an inclusive validation.
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Lancelot'
AND 'Lancelot' = 'Lancelot'
AND 'Lancelot' = 'Lancelot'; |
SELECT 'True Statement'
FROM dual
WHERE 'Lancelot' = 'Lancelot'
AND 'Lancelot' = 'Lancelot'
AND 'Lancelot' = 'Lancelot';
Structure Lookup
This looks up a structure in a list of structures and returns the True Statement because it finds ONLY the pair of string values: Harry Potter and the Prisoner of Azkaban
, PG
. Note that these structures are implicitly created by the SQL statement, and likewise the contents of the list could be the result of a SELECT
clause predicate.
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Prisoner of Azkaban','PG')
=ALL (('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')); |
SELECT 'True Statement' FROM dual
WHERE ('Harry Potter and the Prisoner of Azkaban','PG')
=ALL (('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Prisoner of Azkaban','PG')
,('Harry Potter and the Prisoner of Azkaban','PG'));
The =AND
operator prevents a massive set of AND logic conditions, like the following equivalent.
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
AND ('Harry Potter and the the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
AND ('Harry Potter and the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')); |
SELECT 'True Statement' FROM dual
WHERE (('Harry Potter and the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
AND ('Harry Potter and the the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG')
AND ('Harry Potter and the Prisoner of Azkaban','PG') =
('Harry Potter and the Prisoner of Azkaban','PG'));
After learning how to filter single tables, you need to learn how to join tables into combined result sets. The filters in the WHERE
clause may be applied against columns from either table in a join.
Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM
clause as part of a ON
or USING
subclause when queries use key words like JOIN
et cetera. You find join statements in the WHERE
clause when the queries list tables as comma separated elements in a FROM
clause. This is a complete page on SQL joins.
Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT
clause.
While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.
Aggregation typically happens after selection but before projection. While columns in the SELECT
clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY
clause.
Sorting by a column is done through the ORDER BY
clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY
clause.
In the MySQL example for IFNULL the paragraph says that this is Oracles implementation.
Peter Sumsion
1 Feb 10 at 3:55 pm
Great catch. I’m proof reading the copy again tonight. This part is actually moving to the other page. Right now it’s in both places.
michaelmclaughlin
1 Feb 10 at 4:09 pm
Hey, I was just reading about the =ALL and in the 3rd paragraph in that section it was accidentally called =AND.
Rachelle Haynes
3 Apr 10 at 10:20 pm
Thanks, it’s fixed now.
michaelmclaughlin
5 Apr 10 at 7:57 pm
At the top, should this:
“Column references don’t require prepending a column with the table or view alias when they’re not unique in the result set.”
be this:
“Column references don’t require prepending a column with the table or view alias when they’re unique in the result set.” (removed the not)
It sounds like the original is saying that you don’t need to prepend columns with a table name even if they are the same. Or is this statement saying:
“Even if columns have the same results, you don’t have to prepend each column with the table or view alias as long as the column names in each table are unique”
Chris Staber
15 Oct 10 at 9:54 am
Is there any differences between Lower() and Upper() functions?
Atsuko Takanabe
16 Oct 10 at 4:49 pm
One promotes to the ASCII uppercase and the other demotes to ASCII lowercase. Other than that, not really.
michaelmclaughlin
18 Dec 10 at 11:51 pm
There was a typographical error addressed in a comment above, regarding =AND being published instead of =ALL. On the same line, conditions is spelled “conditoins.”
Then in the fifth paragraph the same =AND also exists along with another “conditoins.”
Michael
2 Feb 11 at 10:12 am
The =ALL operator doesn’t make sense to me, where does it have a useful application? Also the =ALL has the same definition as the definitions of IN, =ANY, =SOME.
Joshua Floyd
28 Feb 11 at 5:13 pm
In the NULL SQL Functions under the Oracle section it makes mention of data types that implicitly convert. Do you have any examples of these? Thanks in advance.
Jacob
18 May 11 at 10:28 am
When I took this class, I was so confused when I encountered
=ALL
. It just seemed so useless.But then I recently discovered the ANY, SOME, and ALL keywords don’t have to be used with “=”. You could say
>ALL
or<ALL
. Suddenly that keyword became useful.If your students now think similarly to me, they'd probably be interested to know that.
Brandon MacGregor
2 Apr 13 at 9:26 am
Brandon, Thanks for the note!
michaelmclaughlin
18 Mar 14 at 11:23 am
conditoins = conditions
Gerry
18 Oct 14 at 4:24 pm
Joshua, I haven’t found a use for the =ALL operator too often but it’s role occurs while trying to find duplicate data sets. These typically occur because of a coding error.
michaelmclaughlin
22 Oct 14 at 1:59 am
You can check the Oracle document, like Datatype Comparison Rules.
michaelmclaughlin
22 Oct 14 at 2:29 am
Chris, Thanks! It’s fixed.
michaelmclaughlin
25 Oct 14 at 6:46 pm