Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

2.1. Grouping Is Not Equality

Go back to step c in the last section. A grouping is not exactly defined by equality. Equality is a comparison between two scalar values, and it follows the rules for that data type.

First of all, consider how equality of strings is defined in SQL. The shorter string is padded out with blanks on the right side until it is the same length as the longer string. The characters are matched position for position, so that ‘Smith’ = ‘Smith’ and so forth. Given a subset of different length strings that all test equal under that rule, which one represents the “Smith group” in the working table?

Second, consider floating-point numbers. Two FLOATs can actually be different but test equal if they are within a certain difference ...