The optional WHERE clause defines the search criteria for the selection, filtering the set of elements specified by the FROM clause.
Without a WHERE clause, the SELECT projection list receives the entire collection or set of collections as specified in the FROM clause.
The query processor searches the collection for elements that match the conditions specified in the WHERE clause conditions. If there is an index on an expression matched by the WHERE clause, then the query processor may use the index to optimize the search and avoid iterating over the entire collection.
A WHERE clause expression is a boolean condition that is evaluated for each element in the collection. If the expression evaluates to true for an element, the query processor passes that element on to the SELECT projection list. This example uses the WHERE clause to return the portfolio objects in the region that have a type xyz .
SELECT DISTINCT * FROM /portfolios WHERE "type" = 'xyz'
The next query returns the set of all portfolios with a type of xyz and active status.
SELECT DISTINCT * FROM /portfolios WHERE "type" = 'xyz' AND status = 'active'