Coercion vs Casting in standard SQL

Question

What is the difference between Coercion and Casting in standard SQL? And what are the use case of those?

Answer 1

The word conversion refers to either implicitly or explicitly changing a value from one data type to another, e.g. a 16-bit integer to a 32-bit integer.

Coercion

The word coercion is used to denote an implicit conversion. A supertype is a common type to which two or more expressions can be coerced.

Casting

The word cast typically refers to an explicit type conversion (as opposed to an implicit conversion) and uses the CAST() function, regardless of whether this is a re-interpretation of a bit-pattern or a real conversion.

So, coercion is implicit, cast is explicit, and conversion is any of them.

Use Case

Generally takes place when in an expression more than one data type is present. In such condition type conversion (type promotion) takes place to avoid loss of data. All the data types of the variables are upgraded to the data type of the variable with largest data type.

If you are trying to assign a value of some type to a location of a different type, you can generate a value of the new type that has a similar meaning to the original. This is coercion. Coercion lets you use the new type by creating a new value that in some way resembles the original. Some coercions may discard data (e.g. converting the int 0x12345678 to the short 0x5678), while others may not (e.g. converting the int 0x00000008 to the short 0x0008, or the long 0x0000000000000008).

Recall that values can have multiple types. If your situation is slightly different, and you only want to select a different one of the value’s types, casting is the tool for the job. Casting simply indicates that you wish to operate on a particular type that a value includes.