SQL-J Language Reference
Page 58 of 121

WHERE clause

A WHERE clause is an optional part of a SelectExpression, DELETE statement, or UPDATE statement. The WHERE clause lets you select rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result, or, in the case of a DELETE statement, deleted, or, in the case of an UPDATE statement, updated.

Syntax

WHERE Boolean expression

SQL-J extends the SQL-92 WHERE clause to allow any boolean expression. Most of the general expressions listed in Table 1-9, "Table of Expressions", can result in a boolean value. In addition, SQL-J allows the following as expressions in a WHERE clause:

  • the TRUE and FALSE constants
  • method invocations that return the Java boolean or java.lang.Boolean types
  • boolean columns
  • subqueries that return boolean expressions

For example, the following are valid SQL-J WHERE clauses:

WHERE TRUE
WHERE tab.userTypeColumn.methodReturningBoolean(17, abcd)
WHERE tab.booleanColumn
WHERE (CLASS COM.ACMEWeb.WebPage).staticBooleanMethod()
WHERE tab.userTypeColumn->booleanField
WHERE (CLASS COM.ACMEWeb.WebPage)->staticBooleanField

In addition, there are the more common boolean expressions. Specific SQL-J boolean operators listed in Table 1-10, "SQL-J Boolean Operators", take one or more operands; the expressions return a boolean value.

WHERE clause Examples

WHERE DATE'1993-01-01' < DATE'1996-01-01' -- returns true
WHERE 'This value is here' LIKE '%value_is%' -- evaluates to true

-- find the flights where no business-class seats have
-- been booked
SELECT *
FROM FlightAvailability
WHERE business_seats_taken IS NULL
OR business_seats_taken = 0

SELECT *
FROM HotelBookings
WHERE arrival BETWEEN DATE'1999-01-01' AND CURRENT_DATE

-- update only specific rows
UPDATE FlightAvailability
SET business_seats_taken = ((business_seats_taken IS NULL) ?
0: business_seats_taken) +1
WHERE flight_id = 'AA1290'
and segment_number = 1

SELECT Part
FROM Orders O, Suppliers OS
WHERE O.SupplierName = OS.Company
AND EXISTS (SELECT *
FROM Suppliers S
WHERE S.Company <> OS.Company
AND S.Part = OS.Part)