TheWHEREclauseof aSELECTstatementspecifiestherowsthatyouwant tosee.Acomparisonconditionemploysspecific keywordsandoperatorsto definethesearchcriteria.
Forexample,youmight useoneof thekeywordsBETWEEN,IN,LIKE,or MATCHEStotestforequality,orthekeywordsISNULLtotestfornull values.YoucancombinethekeywordNOTwith anyofthese keywordsto specifytheoppositecondition.
numéro nom prénom
13601 Ålesund Sverre
13612 Azevedo Edouardo Freire 13600 da Sousa João Lourenço Antunes 13615 di Girolamo Giuseppe
13606 Dupré Michèle Françoise 13608 Étaix Émile
13607 Hammer Gerhard 13602 Hämmer le Greta 13604 LaForêt Jean-Noël 13610 LeMaître Héloïse 13613 Llanero Gloria Dolores 13603 Montaña José Antonio 13611 Oatfield Emily 13605 Ötker Hans-Jürgen 13614 Øverst Per-Anders 13609 Tiramisù Paolo Alfredo
Thefollowingtableliststherelationaloperatorsthatyoucanuseina WHERE clauseinplaceofa keywordtotestfor equality.
Operator Operation
= equals
!=or<> doesnotequal
> greaterthan
>= greaterthanorequalto
< less than
<= less thanorequal to
ForCHARexpressions,greater thanmeansafterinASCIIcollatingorder,where lowercaselettersareafteruppercase letters,and bothareafternumerals.See theASCIICharacterSetchartintheIBM Informix:GuidetoSQLSyntax.For DATEandDATETIMEexpressions,greaterthan meanslater intime,andfor INTERVALexpressions,itmeansoflongerduration.
YoucannotuseTEXTorBYTEcolumns tocreateacomparisoncondition, exceptwhenyouusetheISNULLorISNOTNULLkeywordstotest for NULLvalues.
DynamicServer
YoucannotspecifyBLOBorCLOBcolumnstocreatea comparisoncondition onDynamic Server,exceptwhen youusetheISNULLorISNOTNULL keywordstotest forNULLvalues.
EndofDynamic Server
Youcanusetheprecedingkeywordsand operatorsinaWHEREclauseto createcomparison-conditionqueriesthatperformthefollowingactions:
v Includevalues
v Excludevalues
v Finda rangeof values
v
Finda subsetofvalues
v IdentifyNULLvalues
Toperformvariabletextsearchesusingthefollowingcriteria,usethe precedingkeywordsand operatorsinaWHEREclausetocreate comparison-conditionqueries:
v Exact-textcomparison
v Restrictedsingle-characterwildcards
v
Variable-lengthwildcards
v Subscripting
Thefollowingsectioncontainsexamples thatillustratethese typesofqueries.
IncludingRows
Usetheequalsign(=)relationaloperatortoincluderowsina WHEREclause, asFigure2-42shows.
Figure2-42 returnsthesetof rowsthatFigure2-43shows.
ExcludingRows
Usetherelationaloperators!=or<> toexcluderowsina WHEREclause.
Figure2-44 assumesthatyouareselectingfromanANSI-compliantdatabase; thestatementsspecifytheownerorloginnameofthecreatorofthecustomer table.Thisqualifierisnotrequiredwhenthecreatorofthetableisthecurrent user,orwhenthedatabaseisnotANSIcompliant.However, youcaninclude thequalifierineither case.Fora detaileddiscussionofowner naming,seethe IBM Informix:GuidetoSQLSyntax.
SELECT customer_num, call_code, call_dtime, res_dtime FROM cust_calls
WHERE user_id = ’maryj’
Figure2-42.Query
customer_num call_code call_dtime res_dtime
106 D 1998-06-12 08:20 1998-06-12 08:25 121 O 1998-07-10 14:05 1998-07-10 14:06 127 I 1998-07-31 14:30
Figure2-43.QueryResult
SELECT customer_num, company, city, state FROM odin.customer
WHERE state != ’CA’
SELECT customer_num, company, city, state FROM odin.customer
WHERE state <> ’CA’
BothstatementsinFigure2-44 excludevaluesbyspecifyingthat,inthe customertablethattheuserodinowns,thevaluein thestatecolumnshould notbe equaltoCA,asFigure2-45 shows.
SpecifyingARangeofRows
Figure2-46 showstwowaystospecifyarangeofrowsin aWHEREclause.
EachstatementinFigure2-46specifies arangeforcatalog_numfrom10005 through10008,inclusive.Thefirst statementuseskeywords, andthesecond statementusesrelationaloperatorstoretrievetherows, asFigure2-47 shows. customer_num company city state
119
The Triathletes Club Cherry Hill NJ 120 Century Pro Shop Phoenix AZ 121 City Sports Wilmington DE 122 The Sporting Life Princeton NJ 123 Bay Sports Jacksonville FL 124
Putnum’s Putters Bartlesville OK 125
Total Fitness Sports Brighton MA 126
Neelie’s Discount Sp Denver CO 127
Big Blue Bike Shop Blue Island NY 128
Phoenix College Phoenix AZ
Figure2-45.QueryResult
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog
WHERE catalog_num BETWEEN 10005 AND 10008
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog
WHERE catalog_num >= 10005 AND catalog_num <= 10008
Althoughthecatalogtable includesa columnwiththeBYTEdatatype, that columnisnotincludedinthisSELECTstatementbecausetheoutputwould showonlythewords<BYTE value>bythecolumnname.Youcanwritean SQLAPIapplicationtodisplayTEXT andBYTE values.
ExcludingaRangeofRows
Figure2-48 usesthekeywordsNOTBETWEENtoexclude rowsthathavethe characterrange94000through 94999inthezipcodecolumn,asFigure2-49 shows.
catalog_num 10005 stock_num 3 manu_code HSK
cat_advert High-Technology Design Expands the Sweet Spot
catalog_num 10006 stock_num 3 manu_code SHM
cat_advert Durable Aluminum for High School and Collegiate Athletes
catalog_num 10007 stock_num 4 manu_code HSK
cat_advert Quality Pigskin with Joe Namath Signature
catalog_num 10008 stock_num 4 manu_code HRO
cat_advert Highest Quality Football for High School and Collegiate Competitions
Figure2-47.QueryResult
SELECT fname, lname, city, state FROM customer
WHERE zipcode NOT BETWEEN ’94000’ AND ’94999’ ORDER BY state
UsingaWHEREClausetoFindaSubsetofValues
Like“ExcludingRows” onpage2-24,Figure2-50assumestheuseofan ANSI-compliantdatabase.Theowner qualifierisinquotationmarksto preservethecasesensitivityoftheliteralstring.
EachstatementinFigure2-50retrievesrowsthatincludethesubsetofAZor NJinthestatecolumnoftheAleta.customer table,asFigure2-51shows. fname lname city state
Frank Lessor Phoenix AZ Fred Jewell Phoenix AZ Eileen Neelie Denver CO Jason Wallack Wilmington DE Marvin Hanlon Jacksonville FL James Henry Brighton MA Bob Shorter Cherry Hill NJ Cathy O’Brian Princeton NJ Kim Satifer Blue Island NY Chris Putnum Bartlesville OK
Figure2-49.QueryResult
SELECT lname, city, state, phone FROM ’Aleta’.customer
WHERE state = ’AZ’ OR state = ’NJ’ ORDER BY lname
SELECT lname, city, state, phone FROM ’Aleta’.customer
WHERE state IN (’AZ’, ’NJ’) ORDER BY lname
Figure2-50.Query
lname city state phone
Jewell Phoenix AZ 602-265-8754 Lessor Phoenix AZ 602-533-1817 O’Brian Princeton NJ 609-342-0054 Shorter Cherry Hill NJ 609-663-6079
YoucannottestTEXT orBYTEcolumns withtheINkeyword.
DynamicServer
Also,whenyouuseDynamicServer,youcannottest BLOBorCLOBcolumns withtheINkeyword.
EndofDynamic Server
InFigure2-52,anexampleof aqueryonanANSI-compliantdatabase,no quotationmarksexistaround thetableowner name.Whereas thetwo statementsinFigure2-50 searchedtheAleta.customertable,Figure2-52 searchesthetableALETA.customer,whichisadifferenttable,becauseofthe wayANSI-compliantdatabaseslookatowner names.
Figure2-52 addsthekeywordsNOTIN, sothesubsetchangestoexclude the subsetsAZandNJinthestatecolumn.Figure2-53showstheresultsinorder ofthestatecolumn.
SELECT lname, city, state, phone FROM Aleta.customer
WHERE state NOT IN (’AZ’, ’NJ’) ORDER BY state
IdentifyingNULLValues
UsetheISNULLorISNOT NULLoption tocheckforNULLvalues.ANULL valuerepresentseithertheabsenceofdataoranunknownvalue.ANULL valueisnotthesameasa zeroora blank.
Figure2-54 returnsallrowsthathaveanull paid_date,asFigure2-55 shows.
lname city state phone
Pauli Sunnyvale CA 408-789-8075 Sadler San Francisco CA 415-822-1289 Currie Palo Alto CA 415-328-4543 Higgins Redwood City CA 415-368-1100 Vector Los Altos CA 415-776-3249 Watson Mountain View CA 415-389-8789 Ream Palo Alto CA 415-356-9876 Quinn Redwood City CA 415-544-8729 Miller Sunnyvale CA 408-723-8789 Jaeger Redwood City CA 415-743-3611 Keyes Sunnyvale CA 408-277-7245 Lawson Los Altos CA 415-887-7235 Beatty Menlo Park CA 415-356-9982 Albertson Redwood City CA 415-886-6677 Grant Menlo Park CA 415-356-1123 Parmelee Mountain View CA 415-534-8822 Sipes Redwood City CA 415-245-4578 Baxter Oakland CA 415-655-0011 Neelie Denver CO 303-936-7731 Wallack Wilmington DE 302-366-7511 Hanlon Jacksonville FL 904-823-4239 Henry Brighton MA 617-232-4159 Satifer Blue Island NY 312-944-5691 Putnum Bartlesville OK 918-355-2074
Figure2-53.QueryResult
SELECT order_num, customer_num, po_num, ship_date FROM orders
WHERE paid_date IS NULL ORDER BY customer_num
FormingCompoundConditions
Toconnecttwoormorecomparisonconditions, orBooleanexpressions,usethe logicaloperatorsAND,OR,andNOT.ABooleanexpressionevaluatesastrue orfalseor,ifNULLvaluesareinvolved,asunknown.
InFigure2-56,theoperatorANDcombinestwocomparisonexpressionsinthe WHEREclause.
ThequeryreturnsallrowsthathaveNULLpaid_dateoraNOT NULL ship_date,asFigure2-57 shows.
UsingExact-TextComparisons
Thefollowingexamplesincludea WHEREclausethatsearchesforexact-text comparisonsbyusingthekeywordLIKEor MATCHESor theequal sign(=) relationaloperator. Unlikeearlierexamples, theseexamplesillustratehowto querya tablethatisnotinthecurrentdatabase.Youcanaccessa tablethatis order_num customer_num po_num ship_date
1004 106 8006 05/30/1998 1006 112 Q13557 1007 117 278693 06/05/1998 1012 117 278701 06/29/1998 1016 119 PC6782 07/12/1998 1017 120 DM354331 07/13/1998
Figure2-55.QueryResult
SELECT order_num, customer_num, po_num, ship_date FROM orders
WHERE paid_date IS NULL AND ship_date IS NOT NULL ORDER BY customer_num
Figure2-56.Query
order_num customer_num po_num ship_date
1004 106 8006 05/30/1998 1007 117 278693 06/05/1998 1012 117 278701 06/29/1998 1017 120 DM354331 07/13/1998
notinthecurrentdatabaseonlyifthedatabasethatcontainsthetablehasthe sameANSIcompliancestatusasthecurrentdatabase.Ifthecurrentdatabase isanANSI-compliantdatabase,thetable youwanttoaccessmust alsoreside inanANSI-compliantdatabase.Ifthecurrentdatabaseisnotan
ANSI-compliantdatabase,thetableyouwanttoaccessmustalso resideina databasethatisnotanANSI-compliantdatabase.
Althoughthedatabaseusedpreviouslyinthischapteristhedemonstration database,theFROM clauseinthefollowingexamples specifiesthemanatee table,createdbytheownerbubba,whichresidesinanANSI-compliant databasenamedsyzygy.Formoreinformationonhowtoaccesstablesthat arenotinthecurrentdatabase,seetheIBM Informix:GuidetoSQLSyntax.
EachstatementinFigure2-58retrievesall therowsthathavethesingle word helmetinthedescription column,asFigure2-59shows.
Theresultsmight looklikeFigure2-59.
UsingVariable-TextSearches
YoucanusethekeywordsLIKEand MATCHESforvariable-textqueries that arebasedonsubstringsearchesoffields.IncludethekeywordNOTto SELECT stock_no, mfg_code, description, unit_price
FROM syzygy:bubba.manatee WHERE description = ’helmet’ ORDER BY mfg_code
SELECT stock_no, mfg_code, description, unit_price FROM syzygy:bubba.manatee
WHERE description LIKE ’helmet’ ORDER BY mfg_code
SELECT stock_no, mfg_code, description, unit_price FROM syzygy:bubba.manatee
WHERE description MATCHES ’helmet’ ORDER BY mfg_code
Figure2-58.Query
stock_no mfg_code description unit_price
991 ABC helmet $222.00 991 BKE helmet $269.00 991 HSK helmet $311.00 991 PRC helmet $234.00 991 SPR helmet $245.00
indicatetheoppositecondition.ThekeywordLIKE istheANSIstandard, whereasMATCHESisanInformix extension.
Variable-textsearchstringscaninclude thewildcardslistedwith LIKEor MATCHESinthefollowingtable.
Keyword Symbol Meaning
LIKE % Evaluatestozeroormorecharacters LIKE _ Evaluatestoasinglecharacter
LIKE \ Escapesspecialsignificanceofnextcharacter MATCHES * Evaluatestozeroormorecharacters
MATCHES ? Evaluatestoasinglecharacter(exceptnull) MATCHES [] Evaluatestoasinglecharacterorrangeofvalues MATCHES \ Escapesspecialsignificanceofnextcharacter
YoucannottestTEXT orBYTEcolumns withtheLIKEorMATCHES keywords.
Also,whenyouuseDynamicServer,youcannottest BLOBorCLOBcolumns withtheLIKEorMATCHESkeywords.
Usinga Single-CharacterWildcard
ThestatementsinFigure2-60 illustratetheuseofasingle-characterwildcard inaWHEREclause.Further,theydemonstratea queryona tablethatisnot inthecurrentdatabase.Thestocktableisinthedatabasesloth.Besidesbeing outsidethecurrentdemonstration database,slothisona separatedatabase servercalledmeerkat.
Formoreinformation,seeChapter7,“AccessingandModifyingDatainan ExternalDatabase,”onpage7-1andtheIBM Informix:GuidetoSQLSyntax.
EachstatementinFigure2-60retrievesonlythoserowsforwhichthemiddle letterofthemanu_codeisR,asFigure2-61 shows.The comparison’_R_’(for LIKE)or’?R?’ (forMATCHES)specifies,fromlefttoright,thefollowing items:
v Anysingle character
v TheletterR
v Anysingle character
WHEREClauseSpecifyingaRangeofInitial Characters: Figure2-62selects onlythoserowswherethemanu_codebeginswith Athrough Handreturns therowsthatFigure2-63shows.Thetest’[A-H]’specifiesanysingleletter fromAthrough H,inclusive.Noequivalentwildcard symbolexistsforthe LIKEkeyword.
SELECT stock_num, manu_code, description, unit_price FROM sloth@meerkat:stock
WHERE manu_code LIKE ’_R_’ AND unit_price >= 100 ORDER BY description, unit_price
SELECT stock_num, manu_code, description, unit_price FROM sloth@meerkat:stock
WHERE manu_code MATCHES ’?R?’ AND unit_price >= 100
ORDER BY description, unit_price
Figure2-60.Query
stock_num manu_code description unit_price
205 HRO 3 golf balls $312.00 2 HRO baseball $126.00 1 HRO baseball gloves $250.00 7 HRO basketball $600.00 102 PRC bicycle brakes $480.00 114 PRC bicycle gloves $120.00 4 HRO football $480.00 110 PRC helmet $236.00 110 HRO helmet $260.00 307 PRC infant jogger $250.00 306 PRC tandem adapter $160.00 308 PRC twin jogger $280.00 304 HRO watch $280.00
WHEREClausewithVariable-LengthWildcard: Thestatementsin
Figure2-64 usea wildcardattheendofa stringtoretrievealltherowswhere thedescription beginswiththecharactersbicycle.
EitherstatementreturnstherowsthatFigure2-65 shows. SELECT stock_num, manu_code, description, unit_price
FROM stock
WHERE manu_code MATCHES ’[A-H]*’ ORDER BY description, manu_code
Figure2-62.Query
stock_num manu_code description unit_price
205 ANZ 3 golf balls $312.00 205 HRO 3 golf balls $312.00 2 HRO baseball $126.00 3 HSK baseball bat $240.00 1 HRO baseball gloves $250.00 1 HSK baseball gloves $800.00 7 HRO basketball $600.00 . . .
313 ANZ swim cap $60.00 6 ANZ tennis ball $48.00 5 ANZ tennis racquet $19.80 8 ANZ volleyball $840.00 9 ANZ volleyball net $20.00 304 ANZ watch $170.00
Figure2-63.QueryResult
SELECT stock_num, manu_code, description, unit_price FROM stock
WHERE description LIKE ’bicycle%’ ORDER BY description, manu_code
SELECT stock_num, manu_code, description, unit_price FROM stock
WHERE description MATCHES ’bicycle*’ ORDER BY description, manu_code
Thecomparison’bicycle%’or’bicycle*’specifiesthecharactersbicycle followedbyanysequenceofzero ormorecharacters.Itmatchesbicycle stem withstemmatchedbythewildcard.Itmatches tothecharactersbicycle alone,if arowexistswith thatdescription.
Figure2-66 narrowsthesearchbyaddinganothercomparisonconditionthat excludesa manu_codeofPRC.
ThestatementretrievesonlytherowsthatFigure2-67shows.
Whenyouselectfroma largetableanduseaninitialwildcard inthe
comparisonstring(suchas’%cycle’),thequeryoftentakeslongertoexecute. Becauseindexescannotbeused, everyrowissearched.
stock_num manu_code description unit_price
102 PRC bicycle brakes $480.00 102 SHM bicycle brakes $220.00 114 PRC bicycle gloves $120.00 107 PRC bicycle saddle $70.00 106 PRC bicycle stem $23.00 101 PRC bicycle tires $88.00 101 SHM bicycle tires $68.00 105 PRC bicycle wheels $53.00 105 SHM bicycle wheels $80.00
Figure2-65.QueryResult
SELECT stock_num, manu_code, description, unit_price FROM stock
WHERE description LIKE ’bicycle%’ AND manu_code NOT LIKE ’PRC’ ORDER BY description, manu_code
Figure2-66.Query
stock_num manu_code description unit_price
102 SHM bicycle brakes $220.00 101 SHM bicycle tires $68.00 105 SHM bicycle wheels $80.00
MATCHESandNon-DefaultLocales
Bydefault,Informixdatabaseservers usetheU.S.Englishlanguage environment,calledalocale,fordatabasedata.Thisdefaultlocaleusesthe ISO8859-1 codeset.TheU.S.EnglishlocalespecifiesthatMATCHESwilluse code-setorder.
Ifyourdatabaseusesa non-defaultlocale,a MATCHESclausethatspecifiesa rangeusesthecollationorderofthatlocaleforcharacterdatatypes(including
CHAR,NCHAR,VARCHAR,NVARCHAR,andLVARCHAR).Thisfeatureof
MATCHESrangesisanexceptiontothegeneralrulethatonlyNCHARand NVARCHARcolumnscanuselocale-specificcollation.Ifthelocaledoesnot specifyanyspecialcollationorder,however,thenMATCHESusesthecode-set order.
DynamicServer
YoucanusetheSETCOLLATIONstatementtospecifyadatabase localefor yoursessionthatisdifferentfromtheDB_LOCALEsetting. Seethe
IBM Informix:GuidetoSQLSyntaxforadescriptionofSETCOLLATION. EndofDynamic Server
InFigure2-69,therowsforÉtaix,Ötker,andØverstarenotselectedand listedbecause,withISO8859-1code-setorder,theaccentedfirstletter ofeach nameisnotintheE throughP MATCHESrangeforthenomcolumn.
SELECT numéro,nom,prénom FROM abonnés
WHERE nom MATCHES ’[E-P]*’ ORDER BY nom
Figure2-68.Query
numéro nom prénom
13607 Hammer Gerhard 13602 Hämmer Greta 13604 LaForêt Jean-Noël 13610 LeMaître Héloïse 13613 Llanero Gloria Dolores 13603 Montaña José Antonio 13611 Oatfield Emily
Formoreinformationonnon-Englishdataand locales,seetheIBM Informix: GLSUser'sGuide.
ProtectingSpecialCharacters
Figure2-70 usesthekeywordESCAPE withLIKE orMATCHESsoyoucan protectaspecial characterfrommisinterpretationasawildcardsymbol.
TheESCAPEkeyworddesignatesanescapecharacter(! inthisexample)that protectsthenext charactersothatitisinterpretedasdataandnotasa wildcard.Intheexample,theescapecharactercausesthemiddlepercent sign (%)tobe treatedasdata.By usingtheESCAPE keyword,youcansearchfor occurrencesofapercentsign(%)intheres_descrcolumnbyusingtheLIKE wildcardpercentsign(%).ThequeryretrievestherowthatFigure2-71shows.
UsingSubscripting inaWHEREClause
YoucanusesubscriptingintheWHEREclauseofaSELECTstatementto specifya rangeof charactersornumbersin acolumn,asFigure2-72 shows. SELECT * FROM cust_calls
WHERE res_descr LIKE ’%!%%’ ESCAPE ’!’
Figure2-70.Query
customer_num 116
call_dtime 1997-12-21 11:24 user_id mannyn
call_code I
call_descr Second complaint from this customer! Received two cases righthanded outfielder glove (1 HRO) instead of one case lefties. res_dtime 1997-12-27 08:19
res_descr Memo to shipping (Ava Brown) to send case of lefthanded gloves, pick up wrong case; memo to billing requesting 5% discount to placate customer due to second offense and lateness of resolution because of holiday.
Figure2-71.QueryResult
SELECT catalog_num, stock_num, manu_code, cat_advert, cat_descr
FROM catalog
WHERE cat_advert[1,4] = ’High’
Thesubscript[1,4] causesFigure2-72 toretrieveallrowsinwhichthefirst fourlettersof thecat_advertcolumnare High,asFigure2-73shows.