highlight: Logical operators in Joins, Where, Having
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.
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..
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
- 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
- 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"
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
[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.