Relational Operator

Informix Guide to SQL: Syntax
Segments

Relational Operator

A relational operator compares two expressions quantitatively. Use the Relational Operator segment whenever you see a reference to a relational operator in a syntax diagram.

Syntax

Each operator shown in the syntax diagram has a particular meaning.


Relational Operator	Meaning
<	Less than
<=	Less than or equal to
>	Greater than
=	Equal to
>=	Greater than or equal to
<>	Not equal to
!=	Not equal to

Usage

For DATE and DATETIME expressions, greater than means later in time.

For INTERVAL expressions, greater than means a longer span of time.

For CHAR, VARCHAR, and LVARCHAR expressions, greater than means after in code-set order.

Locale-based collation order is used for NCHAR and NVARCHAR expressions. So for NCHAR and NVARCHAR expressions, greater than means after in the locale-based collation order. For more information on locale-based collation order and the NCHAR and NVARCHAR data types, see the Informix Guide to GLS Functionality.

Using Operator Functions in Place of Relational Operators

Each relational operator is bound to a particular operator function, as shown in the table below. The operator function accepts two values and returns a boolean value of true, false, or unknown.


Relational Operator	Associated Operator Function
<	lessthan()
<=	lessthanorequal()
>	greater than()
>=	greaterthanorequal()
=	equal()
<>	notequal()
!=	notequal()

Connecting two expressions with a binary operator is equivalent to invoking the operator function on the expressions. For example, the following two statements both select orders with a shipping charge of $18.00 or more. The >= operator in the first statement implicitly invokes the greaterthanorequal() operator function.

SELECT order_num FROM orders 

	WHERE ship_charge >= 18.00

SELECT order_num FROM orders

	WHERE greaterthanorequal(ship_charge, 18.00)

The database server provides the operator functions associated with the relational operators for all built-in data types. When you develop a user-defined data type, you must define the operator functions for that type for users to be able to use the relational operator on the type.

Collating Order for English Data

If you are using the default locale (U.S. English), the database server uses the code-set order of the default code set when it compares the character expressions that precede and follow the relational operator.

On UNIX, the default code set is the ISO8859-1 code set, which consists of the following sets of characters:

The ASCII characters have code points in the range of 0 to 127.

This range contains control characters, punctuation symbols, English-language characters, and numerals.

The 8-bit characters have code points in the range 128 to 255.

In Windows NT, the default code set is Microsoft 1252. This code set includes both the ASCII code set and a set of 8-bit characters.

The following table shows the ASCII code set. The Num column shows the ASCII code numbers, and the Char column shows the ASCII character corresponding to each ASCII code number. ASCII characters are sorted according to their ASCII code number. Thus lowercase letters follow uppercase letters, and both follow numerals. In this table, the caret symbol (^) stands for the CTRL key. For example, ^X means CTRL-X.


Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char
0	^@	20	^T	40	(	60	<	80	P	100	d	120	x
1	^A	21	^U	41	)	61	=	81	Q	101	e	121	y
2	^B	22	^V	42	*	62	>	82	R	102	f	122	z
3	^C	23	^W	43	+	63	?	83	S	103	g	123	{
4	^D	24	^X	44	,	64	@	84	T	104	h	124	\|
5	^E	25	^Y	45	-	65	A	85	U	105	i	125	}
6	^F	26	^Z	46	.	66	B	86	V	106	j	126	~
7	^G	27	esc	47	/	67	C	87	W	107	k	127	del
8	^H	28	^\	48	0	68	D	88	X	108	l
9	^I	29	^]	49	1	69	E	89	Y	109	m
10	^J	30	^^	50	2	70	F	90	Z	110	n
11	^K	31	^_	51	3	71	G	91	[	111	o
12	^L	32		52	4	72	H	92	\	112	p
13	^M	33	!	53	5	73	I	93	]	113	q
14	^N	34	"	54	6	74	J	94	^	114	r
15	^O	35	#	55	7	75	K	95	_	115	s
16	^P	36	$	56	8	76	L	96	\Q	116	t
17	^Q	37	%	57	9	77	M	97	a	117	u
18	^R	38	&	58	:	78	N	98	b	118	v
19	^S	39	'	59	;	79	O	99	c	119	w

Support for ASCII Characters in Nondefault Code Sets

Most code sets in nondefault locales (called nondefault code sets) support the ASCII characters. If you are using a nondefault locale, the database server uses ASCII code-set order for any ASCII data in CHAR and VARCHAR expressions, as long as the nondefault code set supports these ASCII characters.

Related Information

For a discussion of relational operators in the SELECT statement, see the Informix Guide to SQL: Tutorial.

For a discussion of the GLS aspects of relational operators, see the Informix Guide to GLS Functionality.

Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char
0	^@	20	^T	40	(	60	<	80	P	100	d	120	x
1	^A	21	^U	41	)	61	=	81	Q	101	e	121	y
2	^B	22	^V	42	*	62	>	82	R	102	f	122	z
3	^C	23	^W	43	+	63	?	83	S	103	g	123	{
4	^D	24	^X	44	,	64	@	84	T	104	h	124	\|
5	^E	25	^Y	45	-	65	A	85	U	105	i	125	}
6	^F	26	^Z	46	.	66	B	86	V	106	j	126	~
7	^G	27	esc	47	/	67	C	87	W	107	k	127	del
8	^H	28	^\	48	0	68	D	88	X	108	l
9	^I	29	^]	49	1	69	E	89	Y	109	m
10	^J	30	^^	50	2	70	F	90	Z	110	n
11	^K	31	^_	51	3	71	G	91	[	111	o
12	^L	32		52	4	72	H	92	\	112	p
13	^M	33	!	53	5	73	I	93	]	113	q
14	^N	34	"	54	6	74	J	94	^	114	r
15	^O	35	#	55	7	75	K	95	_	115	s
16	^P	36	$	56	8	76	L	96	\Q	116	t
17	^Q	37	%	57	9	77	M	97	a	117	u
18	^R	38	&	58	:	78	N	98	b	118	v
19	^S	39	'	59	;	79	O	99	c	119	w

Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char	Num	Char
0	^@	20	^T	40	(	60	<	80	P	100	d	120	x
1	^A	21	^U	41	)	61	=	81	Q	101	e	121	y
2	^B	22	^V	42	*	62	>	82	R	102	f	122	z
3	^C	23	^W	43	+	63	?	83	S	103	g	123	{
4	^D	24	^X	44	,	64	@	84	T	104	h	124	\|
5	^E	25	^Y	45	-	65	A	85	U	105	i	125	}
6	^F	26	^Z	46	.	66	B	86	V	106	j	126	~
7	^G	27	esc	47	/	67	C	87	W	107	k	127	del
8	^H	28	^\	48	0	68	D	88	X	108	l
9	^I	29	^]	49	1	69	E	89	Y	109	m
10	^J	30	^^	50	2	70	F	90	Z	110	n
11	^K	31	^_	51	3	71	G	91	[	111	o
12	^L	32		52	4	72	H	92	\	112	p
13	^M	33	!	53	5	73	I	93	]	113	q
14	^N	34	"	54	6	74	J	94	^	114	r
15	^O	35	#	55	7	75	K	95	_	115	s
16	^P	36	$	56	8	76	L	96	\Q	116	t
17	^Q	37	%	57	9	77	M	97	a	117	u
18	^R	38	&	58	:	78	N	98	b	118	v
19	^S	39	'	59	;	79	O	99	c	119	w

Informix Guide to SQL: SyntaxSegments