Feature highlight: Logical operators in Joins, Where, Having
Credit: Imagenation,Borland Interbase 6.5
 
Description:
SQL and relational databases are derived from Set Theory in mathematics; therefore SQL has a rich set of logical operators for filtering and joining together queries. We look here at the logical operators found in most versions of SQL.

1)And, Or, Not - these are the meat of most query conditions and work just as in classic Logic:
- itemWidth > 11 and itemHeight > 14 and itemThickness > 3 - selects items exceeding a small package size
- rating <= 2 or rating >= 8 - selects records having outlying or extremely high/low raings
- not (region = "E" or sales < quota) - selects all records where sales are greater than quota outside of the East region..

2)Is Null, Is Not Null - Nulls occur when no data value has been entered into a field, that is why every field created must answer the question "Nulls allowed ?". In SQL you cannot check for a Null field with the equals operator - that is userID = Null  is not allowed in SQL. To test for nulls use the proper syntax illustrated below:
userID is Null or customerID is Not Null

3)Between - provides a little shorthand for range tests as seen below:
totalSales between 1500 and 1800 - is equivalent to - totalSales >= 1500 and totalSales <=1800

4)In, Not In - we have seen in the section on subqueries but they also work as simple tests against an enumerated list of values as in the following:
vehicleType in ("sedan", "roadster", "2-door") - selects smaller vehicles rather than luxury or off road car
sportPlayed not in ("tennis","squash", "badminton", "raquetball") - selects non-racket sports played

5)Like - the Like operator is one of the most powerful for searches within a text or varchar field. Like supports text search patterns which work like but not identically the same in syntax as regular expressions from Unix and XML. The illustrated example below searches for customers who might be in the IT business by any occurence of the substrings "Tech", "Consult" and "System".

Here are the four major wildcard characters for Like:

  % - matches any string of zero or more characters; so Consult% - will match Consultant, Consulting, Consults but not consultant or The Consultancy. In our example we place a % before and after so we catch strings with Consult embedded within them. % is used most often in Like.

 _ - matches any single character; so Star_ will match Star1, Start, Star:, Star , StarG etc. Any five character field starting with the Star. Of course the _ wildcard can be used in combination with any of the other wildcards including itself any number of times. Thus, _Star_ will match AStar1, " Star ", *Star*, and any other 6 character field with Star in the middle of it.

[] - matches any single character in the enumerated or specified range. This is best demonstrated by example:
 [bvx]%[r] - matches the strings br, bar, b/r, vr, var, vbr, xr, xar, xbr, x1r, and so on. bvar would not match as brackets allow only one character to be chosen from the list.
 Item[0-9][0-9][0-9] - matches any value in the range Item000 to Item999 - Item070 would match, Item76 would not
.
 [^] - matches any single character not in the enumerated list or specified range. Again an example best illustrates:
 [^0-9]%name - allows for hname, *name, w111name but not 1name or 11name or "9$is that a name".
 
.See the section on Any/Some and ALL for those special subquery qualifiers.