Combining Predicates and Use of Predicates

Because CONTAINS and FREETEXT are SQL predicates, they can be used anywhere that SQL predicates are supported. In particular, they can be combined with each other and with other predicates, such as equality, LIKE, and BETWEEN, to specify extensive search conditions.

The WHERE clause in the following query uses both a CONTAINS predicate and a comparison predicate. It obtains the title and publication year of all the books in the titles table in the pubs database, where the book costs less than $20.00 and text in the notes column indicates that the book is about ice hockey.

SELECT title, DatePart(year, pubdate) 

  FROM pubs

  WHERE price < 20.00

    AND CONTAINS (notes, ' "ice hockey" ') 

The following query uses a CONTAINS predicate within a subquery. It obtains the titles of all the books in the titles table for the publisher who is located close to the flying saucer in Moonbeam, Ontario. This information about the publisher is known to exist in the pr_info column in the pub_info table, and it is also known that there is only one such publisher.

SELECT T.title, P.pub_name 

  FROM publishers P,

       Titles T

  WHERE P.pub_id = T.pub_id

    AND P.pub_id = (SELECT pub_id

                      FROM pub_info

                      WHERE CONTAINS 

                              (pr_info, 

                               ' moonbeam AND

                                 ontario AND

                                 "flying saucer" 

                               ') )