HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Results of using a comparison operator in the ORDER BY clause

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
theorderoperatorcomparisonusingresultsclause

Problem

There is a table x:

select * from x;

+------+------+
| a    | b    |
+------+------+
| aaa  | 999  |
| bbb  | 888  |
| ccc  | 777  |
+------+------+


What do the following queries do?

select * from x order by a  b;
select * from x order by a <> b, b;


What is the name for this operator?
I have not found anything in the MySQL documentation on this topic.

Solution

This is common comparison operators.

When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL.

These 3 values are related as

NULL < FALSE < TRUE


This relation is used for sorting.

For example:

select * from x order by a<b;


If a and/or b is NULL then the result of ordering expression (comparison result) is NULL, and such records will be at the beginning of the output.

If a less then b then the result of ordering expression is TRUE and such records will be at the end of the output.

The records where a is greater or equal to b will be in the middle.

Code Snippets

NULL < FALSE < TRUE
select * from x order by a<b;

Context

StackExchange Database Administrators Q#269316, answer score: 6

Revisions (0)

No revisions yet.