SQL operators

 

ALL
Syntax
ALL [ ( SELECT-Command ) ]
Description:
ALL is used to select all records of an SELECT statement.
Example:
SELECT *
FROM tkunden
WHERE rating > 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
Syntax
ANY ( SELECT-Command )
Description:
ANY and SOME are the same. Werden verwendet um eine logische logical construct auf alle Datensätze rechts von dem Operator anzuwenden.
Example:
SELECT *
FROM tkunden
WHERE rating > 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 – checks when an field is between two values
Syntax
BETWEEN Value1 AND Value2
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 – checks the existance of an result of an sub-SELECT
Syntax
EXISTS ( SELECT-Command )
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<&gtouter.knum ); Here are all customers selected, which have more than one corresponding salesman. IN – one of a set of values
Syntax
IN ( { Value [,…] } | SELECT-Command )
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 – ist empty
Syntax
Fieldname IS [ NOT ] NULL
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
Syntax
LIKE Formatstring
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
Example:
SELECT *
FROM tkunden
WHERE kname LIKE ‘G%’;
Select all customers whith an beginning ‘G’ in the name.
MATCHES (Informix)
Syntax
MATCHES Formatstring
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;
[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
Syntax
SOME ( SELECT-Command )
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 > 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 in SQL
AND
Syntax
logical construct1 AND logical construct2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 10 AND preis <>
Select all orders, where the price of the ordered produkt is bigger than 10 and smaller then 100.
NOT
Syntax
NOT logical construct
Description:
Logical negation, makes from FALSE an TRUE value and vice versa.
Biggest execution priority of all logical operators.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE NOT preis=0;
Select all orders, where the price of the ordered product is not zero.
OR
Syntax
logical construct 1 OR logical construct 2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 10 AND preis <> 1000;
Select all orders, where the price of the ordered product is bigger than 10 and smaller than 100, and such, where the price bigger than 1000 is.
= – equal
Syntax
Formula1 = Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis = 10 ;
Select all orders, where the price is equal to 10.
> – greater
Syntax
Formula1 > Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 0;
Select all orders, there the price of the ordered product bigger than 0 is.
>= – greater or equal
Syntax
Formula1 >= Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis >= 300;
Select all orders, there the price of the ordered product bigger or equal to 300 is.
< – smaller
Syntax
Formula1 < Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis <>
Select all orders, there the price of the ordered product smaller than 300 is.
<= – smaller or equal
Syntax
Formula1 <= Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis <= 300; Select all orders, there the price of the ordered product smaller or equal to 300 is. <> – not equal
Syntax
Formula1 <> Formula2
Description:
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.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis <> 0;
Select all orders, there the price of the ordered product not equal to 0 is.
Set Operators
Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.
Operator Returns
UNION All rows selected by either query.
UNION ALL All rows selected by either query, including all duplicates.
INTERSECT All distinct rows selected by both queries.
MINUS All distinct rows selected by the first query but not the second.

 


 

Operator

Bedeutung

Priorität

+-

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

 

Comments

comments

Leave a Comment

Time limit is exhausted. Please reload CAPTCHA.