Operators

What are operators? How can i use them?

SQL specific Operators

Here you find SQL-specific operators, which are not or are not so used in other computer languages.

* - All Fields

Syntax

    *

Description:

    Is an alias for all fields of the selected table.

    Example:

SELECT *
 FROM torder

Selects all fields in all records of the table torder.

ALL - All Records

Syntax

Description:

    ALL is used to select all records of an SELECT statement.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt ALL ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers where rating is biger than the rating of all customers living in Berlin.

ANY - Some of Records

Syntax

Description:

    ANY and SOME are the same. They are used to apply a logical contruct to all records on the right side of the operator.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt ANY ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers with rating bigger than rating of at least one of living in Berlin.

BETWEEN - Between Two Values

Syntax

Description:

    WW BETWEEN W1 AND W2 tells the DBMS , that WW must be between W1 and W2.

    Example:

SELECT vnum,anz,preis
 FROM torder
 WHERE preis BETWEEN 100 AND 500

Here are the fields vnum and preis from the table torder selected, where the field preis have an value between 100 and 500.

EXISTS - Records Exist

Syntax

Description:

    EXISTS is used with subquerys (sub-SELECTs) and have the value 'TRUE', when the subselect returns data. When no data is returned then this operator have the value 'FALSE'.

    Example:

SELECT DISTINCT knum
 FROM tkunden outer
 WHERE EXISTS
 ( SELECT * FROM tkunden inner
   WHERE inner.vnum=outer.vnum AND inner.knum&lt&gtouter.knum );

Here are all customers selected, which have more than one corresponding salesman.

IN - One of a Set of Values

Syntax

Description:

    WW IN ( Value [,...] ) tells the DBMS , that WW must be one of the values in the specifyed valuelist.

    Example:

SELECT *
 FROM torder
 WHERE preis IN (100,200,300,400,500);

Here are all records from the table torder selected, where preis have one of the values in the specifyed list.

IS NULL - Is an Empty Field

Syntax

Description:

    Returns 'TRUE' if the field is empty and 'TRUE' if the field have a value.

    Example:

SELECT *
 FROM tkunden
 WHERE stadt IS NOT NULL;

Here are all customers selected, where the field stadt is not empty.

LIKE - Search String Matching

Syntax

Description:

    LIKE is used only with CHAR and VARCHAR field types. Folowing rules apply to the format string :
    _ (underline) means all characters are allowed at this place;
    % (percent) means all character combinations (more than one character too) are allowed at this place (wildcard)

    Example:

SELECT *
 FROM tkunden
 WHERE kname LIKE 'G%';

Select all customers whith an beginning 'G' in the name.

MATCHES - Search String Matching

Syntax (Informix)

Beschreibung:

    MATCHES is used only with CHAR and VARCHAR field types. Folowing rules apply to the format string :
    ? (query) means all characters are allowed at this place;
    * (star) means all character combinations (more than one character too) are allowed at this place (wildcard);
    [characterset] the square brackets with the specifyed characterset allow only only a specifyed subset of characters at this place.

    Example:

SELECT *
 FROM tkunden
 WHERE kname MATCHES '?[AaOoUu]*';

Select all customers whith an 'A', 'a', 'O', 'o', 'U' or 'u' as the second character in the field kname.

SOME - Some of Records

Syntax

Description:

    SOME and ANY mean the same. They are used to apply a logicat constuct to all records at the rigth side of the operator. It's true when the logical construct has the value true on at least one record at the right side of the operator.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt SOME ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers, where rating is bigger than the rating of at least one customer in Berlin.

Logical Operators

Logical contsructs consists of other logical constructs, formulas and values, which are connected through logical operators. For example, comparisons with equal, unequal and so on.

OperatorDescriptionExampleDescripltion
AND Means that the values at both sides of the operator must be TRUE, otherwise this operator returns FALSE. The execution priority is (in SQL) bigger than OR, but smaller than NOT. preis > 10 AND preis < 100 Price is bigger than 10 and smaller then 100.
NOT Logical negation, makes from FALSE an TRUE value and vice versa. Biggest execution priority of all logical operators. NOT preis=0 Price is not zero.
OR Means that at least one of the values at both sides of the operator must be TRUE, otherwise this operator returns FALSE. This operator is executed after NOT and AND. preis > 10 AND preis < 100 OR preis > 1000 Price must be bigger than 10 and smaller than 100 or bigger than 1000.
= Is TRUE when the values at both sides of the operator are equal. The execution priority is bigger than these of NOT, AND and OR. preis = 10 Price is equal to 10.
> Is TRUE when the value at the left side of the operator is greater than the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis > 0 Price is bigger than 0.
>= Is TRUE when the value at the left side of the operator is greater or equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis >= 300 Price is bigger than or equal to 300.
< Is TRUE when the value at the left side of the operator is smaller than the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis < 300 Price is smaller than 300.
<= Is TRUE when the value at the left side of the operator is smaller ot equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis <= 300 Price is smaller than or equal to 300 is.
<> Is TRUE when the value at the left side of the operator is not equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis <> 0 Price is not equal to 0.

Mathematical Operators

In formulas values and fields may be connected through mathematical operators.

OperatorMeaningExampleDescription
-Negativ sign-0.01Minus 0,01
+Positiv sign (optional)+.9Plus 0,9
*Multiplication2*Field3The content fo the field Field3 twice
/DivisionProzent3/100The content of the field Prozent3 divided through 100
+AdditionFeldA + (-0.9)Sum of FeldA and -0,9
-SubtraktionFeldA - 0.01FeldA minus 0,01

Operator Precedence

All operators are executed in a defined priority:

OperatorDecriptionPriority
+- sing 0
* / multiply, divide 1
+ - add, substact 2
= equal 3
<> not equal 3
> greater 3
< smaller 3
>= greater or equal 3
<= smaller or equal 3
[NOT] BETWEEN ...
AND ...
[not] between ... and 3
IS [NOT] NULL [not] NULL-value 3
[NOT] IN [not] in the list 3
NOT Negation 4
AND logical and 5
OR logical or 6