# 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.

```sql
SELECT e FROM Employee e WHERE e.FirstName = 'Bob'
```

{% hint style="warning" %}
Starcounter SQL is case insensitive, so "Bob" will also match with "bob", "BOB" and so on.
{% endhint %}

### 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.

```sql
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'`.

```sql
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`:

```sql
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'
```

{% hint style="info" %}
The logical operators `NOT` and `OR` usually imply that indexes can't be used in the execution of the query, and therefore these operators should be used restrictively.
{% endhint %}

## 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.

```sql
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.

```sql
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.

```sql
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`](https://msdn.microsoft.com/en-us/library/system.type.isassignablefrom.aspx). Consider, for example, the following code:

```csharp
[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:

```sql
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`.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.starcounter.io/2.3.2/guides/sql/comparisons-and-logical-operators.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.