SQL Operators

Describes common SQL operators

SQL operators allow you to control query selection criteria and the values returned. The following is a list of operators supported in PBL.

LIKE operator

The LIKE operator searches for strings that match a specific pattern. The percent sign "%" matches any string and the underscore "_" matches any single character. The following example returns any row where fname starts with "J":

for each e in
    SELECT *
    FROM employees
    WHERE fname LIKE "J%"
do
    // do something here
end

Concatenation operator (||)

In SQL statements, the || operator is used to concatenate two values of any type. For example:

for each e in
    SELECT lname || ", " || fname AS fullname
    FROM employees
do
    display "full name: " + e.fullname
end

Note: In PBL and Java style, the || symbol means "or" and it is used in conditional expressions. For further information, please see Logical operators.

IN operator

The IN operator matches a column value against a set of literal values:

column_name IN (<value1>, <value2>, ...)

For example:

for each e in
    SELECT lname, fname
    FROM employees
    WHERE salary IN (20000, 25000, 30000)
do
    display "name: " + e.lname
end

This statement is equivalent to the following:

for each e in
    SELECT lname, fname
    FROM employees
    WHERE salary = 20000 or salary = 25000 or salary = 30000
do
    display "name: " + e.lname
end

IS operator

The IS operator locates a record that does or does not have a null value for a particular column:

<column_name> IS [NOT] NULL

For example:

for each e in
    SELECT lname, fname
    FROM employees
    WHERE address IS NOT NULL
do
    display "name: " + e.lname + ", address: "
            + e.address
end

BETWEEN Operator

The BETWEEN operator allows you to select records that are between two values:

<column_name> [NOT] BETWEEN <value1> AND <value2>

The expression a BETWEEN b AND c is equivalent to a >= b AND a <= c. For example:

for each e in
    SELECT lname, fname
    FROM employees
    WHERE salary BETWEEN 20000 AND 30000
do
    display "name: " + e.name + ", salary: " 
            + e.salary
end