Comparisons and Logical Operators
Introduction
Starcounter implements operators that can be used for filtering in WHERE
clauses. They can, for example, check if a value is equal to another value or if a value matches a specific pattern.
Relational Operators
Equality
The comparison predicates "equal" (x = y
) and "not equal" (x <> y
) are supported for all data types. See for example query below.
SELECT e FROM Employee e WHERE e.FirstName = 'Bob'
Starcounter SQL is case insensitive, so "Bob" will also match with "bob", "BOB" and so on.
Comparisons
The comparison predicates "less than" (x < y
), "greater than" (x > y
), "less than or equal" (x <= y
) and "greater than or equal" (x >= y
) are implemented for the data types String
, DateTime
and all numerical types. See example below.
SELECT e FROM Employee e WHERE e.LastName >= 'Smith'
Since a DateTime
value represents a timestamp it is often necessary to compare it with a DateTime
range. The query below returns all employees with a HireDate
between '2006-11-01 00:00:00.000'
and '2006-11-01 23:59:59.999'
.
SELECT e.FirstName, e.HireDate FROM Employee e
WHERE e.HireDate >= DATE '2006-11-01'
AND e.HireDate < DATE '2006-11-02'
Logical Operators
There are three logical operators in Starcounter SQL, AND
, OR
, and NOT
:
SELECT e FROM Employee e
WHERE e.FirstName = 'Bob'
AND e.LastName = 'Smith'
SELECT e FROM Employee e
WHERE e.FirstName = 'Bob'
OR e.FirstName = 'John'
SELECT e FROM Employee e
WHERE NOT e.FirstName = 'Bob'
IS NULL
The comparison predicates "is null" (x IS NULL
) and "is not null" (x IS NOT NULL
) are implemented for all data types. See for example query below.
SELECT e FROM Employee e WHERE e.Manager IS NULL
LIKE
The comparison predicate "like" (x LIKE y [ESCAPE z]
) is implemented for the data type String
. In the specified pattern (y
) the underscore character ('_'
) match any single character in the string, and the percent character ('%'
) match any sequence (possibly empty) of characters in the string. See for example query below.
SELECT e FROM Employee e WHERE e.FirstName LIKE 'B_b%'
The optional third argument to the LIKE
predicate is an "escape character", for use when a percent or underscore character is required in the pattern without its special meaning. This is exemplified in query below.
SELECT s FROM Share s WHERE s.Unit LIKE '\%' ESCAPE '\\'
IS
The comparison predicate IS
checks if an object can be cast to a given type. It has similar semantics to Type.IsAssignableFrom
. Consider, for example, the following code:
[Database]
public class Person
{
public string FirstName { get; set; }
}
[Database]
public class Teenager : Person
{
}
[Database]
public class Child : Teenager
{
}
class Program
{
static void Main()
{
Db.Transact(() =>
{
new Person { FirstName = "Bob" };
new Teenager { FirstName = "Johnny" };
new Child { FirstName = "Elsa" };
});
}
}
Here, the IS
operator can be used to determine inheritance in the data model. For instance, to retrieve all instances that can be cast to Teenager
, a query like this would be used:
SELECT p FROM Person p WHERE p IS Teenager
It returns the Child
and Teenager
instances "Johnny" and "Elsa" because they can both be cast as Teenager
.
Last updated