To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_REL.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL Select - Equality and Relational Operators

Objectives

The objective of the lesson is as follows:

  • Describe a SQL SELECT INTO command
  • Describe SQL data types
  • Describe SQL relational operators
  • Describe the following SQL operators
    • AND
    • OR
    • NOT
    • IS NULL
    • BETWEEN
    • IN
    • LIKE

Introduction

Everyday we have situations that arise that cause us to compare two objects. We use words to compare these objects like:

  • faster
  • slower
  • bigger
  • taller
  • shorter
  • same
  • greater
SQL allows the user to compare two objects. In this lesson, we will look at how we compare objects to get the desired result-set. The objects that we will encounter in these comparisons come in the following list of general SQL data types that may not be supported by all relational databases.



Data Type Values Description
BOOLEAN true or false A boolean value is either true, false or null.
CHAR(size) or CHARACTER(size) A string of fixed length. The maximum size of a CHAR string is 1 billion characters.

VARCHAR(size) or LONGVARCHAR(size) CHARACTER VARYING(size), LONG CHARACTER VARYING(size), TEXT(size) or STRING(size) - A string of variable length. The size constraint of these string types do not have to be given and defaults to the maximum possible size of strings that the database is able to store. The maximum size of these string types is 1 billion characters.

CLOB A string of variable length without a size constraint. The maximum size of a CLOB object is 1 billion characters.
TINYINT -128 to 127 An 8-bit signed integer value.
SMALLINT -32768 to 32767 A 16-bit signed integer value.
INTEGER or INT -2147483648 to 2147483647 A 32-bit signed integer value.
BIGINT or LONG -9223372036854775808 to 9223372036854775807 A 64-bit signed integer value.
FLOAT -3.40292347E+38 to 3.40292347E+38 A 32-bit precision floating point value. These types are analogous to the Java float type.

DOUBLE -1.79769313486231570E+308 to +1.79769313486231570E+308 A 64-bit precision floating point value. These types are analogous to the Java double
REAL, NUMERIC or DECIMAL A higher precision numeric value. These numeric types are represented by java.math.BigDecimal and therefore can represent numeric values of any precision and scale.
DATE A day/month/year value that represents a specific instant in time, with millisecond precision. The DATE type does not have any near time bounding issues and is able to represent future and past dates. For a representation of the TIMESTAMP refer to the java.util.Date description.
TIME A time of day value. time - milliseconds since January 1, 1970, 00:00:00 GMT; a negative number is milliseconds before January 1, 1970, 00:00:00 GMT
TIMESTAMP A day/month/year and time of day value. The TIMESTAMP type does not have any near time bounding issues and is able to represent dates many millennia in the future and the past. The TIMESTAMP type is internally represented by java.util.Date.
BINARY(size), VARBINARY(size) or LONGVARBINARY(size) A variable sized binary object. The size constraint is optional and defaults to the maximum size. The maximum size of a binary object is 2 billion bytes.

BLOB 2 billion bytes A variable sized binary object with no size constraint.
CURRENCY


Below is the list of data types supported by MS Access.
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • Yes/No
  • OLE Object
  • Hyperlink


Below is the list of data types supported by Oracle.
  • VARCHAR2(size) - Variable length character string having max. length of size
  • CHAR(size) - Fixed length character string with number of bytes equal to size
  • NUMBER(p,s) - Number having a precision p and s digits to the right of the decimal. If you leave off p and s (e.g., NUMBER), then it is a floating point number.
  • LONG - Character data of variable length up to 2 gigabytes (cannot be a key)
  • DATE - A date field
  • RAW(size) - Raw binary data of length size. Max. size is 255 bytes
  • LONG RAW - Raw binary data up to 2 gigabytes (cannot be a key)
Below is the list of data types supported by MySQL.

Data Type Description
CHAR( ) A fixed length string from 0 to 255 characters long that does not vary in length.
VARCHAR( n ) A variable section from 0 to 255 characters long. The n parameter allows you to enter the number of characters to be used in the column and it is used when the data may vary in length.
TINYTEXTA string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters. BLOB stands for Binary Large OBject. Both TEXT and BLOB are variable length data types that can store large amounts of data and are similar to VARCHAR, but in the form of a larger version.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.
TINYINT( ) The values from -128 to 127 are normal
0 to 255 if the number is UNSIGNED.
SMALLINT( ) The values from -32768 to 32767 normal
0 to 65535 if the number is UNSIGNED.
MEDIUMINT( ) The values from -8388608 to 8388607 normal
0 to 16777215 if the number is UNSIGNED.
INT( ) The values from -2147483648 to 2147483647 normal
0 to 4294967295 if the number is UNSIGNED.
BIGINT( ) The values from -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 if the number is UNSIGNED.
FLOAT A small number with a floating decimal point with values from -3.40292347E+38 to 3.40292347E+38
DOUBLE( , ) A large number with a floating decimal point with values from -1.79769313486231570E+308 to +1.79769313486231570E+308
DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
DATE Format - YYYY-MM-DD.
DATETIME Format - YYYY-MM-DD HH:MM:SS.
TIMESTAMP Format - YYYYMMDDHHMMSS.
TIME Format - HH:MM:SS.
ENUM ( ) Short for ENUMERATION which means that each column may have one of a specified possible values.
  • ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.
  • You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET Similar to ENUM except each column may have more than one of the specified possible values.
  • SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.


In this tutorial we will be working with MS Access data types. The following section outlines the relational operators used by SQL.

Relational Operators

There are six Relational Operators in SQL. They are described in the table below.

SQL Operator Example of SQL condition Meaning of SQL condition
= Equal: col1 = col2 Is col1 is equal to col2
<> or != Not Equal: col1 <> col2
col1 != col2
col1 is not equal to col2
> Greater Than: col1 > col2 Is col1 is greater than col2
< Less Than: col1 < col2 Is col1 is less than col2
>= Greater Than or Equal To: col1 <= col2 Is col1 is greater than or equal to col2
<= Less Than or Equal To: col1 =< col2 Is col1 is less than or equal to col2


In the following sections, we will explore examples that illustrate how to use these operators.

Looking at the Greater Than Relational Operator

The following table shows the contents of the table named Balance.

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1444567890 95.67 04/15/2005 95.23
1333567890 395.21 04/15/2005 95.09
1255567890 406.77 04/15/2005 95.83
1236667890 765.33 04/15/2005 95.75
1234777890 5995.97 04/15/2005 95.51
1234567900 6005.91 04/15/2005 95.77
1234000890 9996.53 04/15/2005 95.17
1222567890 105.55 04/15/2005 95.63
1238888890 6056.77 04/15/2005 95.39
1234599999 1995.45 04/15/2005 95.53
1233267890 2996.41 04/15/2005 95.47
1989867890 3995.23 04/15/2005 95.54
1212127890 4996.88 04/15/2005 95.31


For example, if you want to see the account members with a balance greater than a certain dollar amount, you issue the following SQL command.

 
SELECT * FROM Balance where Balance > '4000'
 


The WHERE description, SALARY > 4000, is known as a condition (an operation which evaluates to True or False). The result is shown in the following table.

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1444567890 95.78 04/15/2005 95.23
1255567890 406.31 04/15/2005 95.83
1236667890 765.43 04/15/2005 95.75
1234777890 5995.97 04/15/2005 95.51
1234567900 6005.91 04/15/2005 95.77
1234000890 9996.53 04/15/2005 95.17
1238888890 6056.77 04/15/2005 95.39
1212127890 4996.88 04/15/2005 95.31


The WHERE description or clause, BALANCE > 4000, is known as a condition (an operation which evaluates to True or False). When you look at the results shown in the above table, you see the following values:
  • 95.78
  • 406.31
  • 765.43
How did this happen? These values are not greater than 4000. The data type for the column Balance is declared as text. This means that the data in this field are character strings. When SQL compares two character strings, it starts with the left most character in each string and compares the first character in each string. If they are equal, the second character in each string is compared. This is repeated until a character is found that is not the same or a string runs out of characters to compare. This means that comparing two strings using a > operator may not be the way to proceed. When we describe SQL functions, we will return to comparing strings.

What happens if we declare the Balance column as a number? What is a number? A number has a data type of int in the Java programming language, where it is defined as a whole number (not a fraction or a decimal) between -2,147,483,648 to +2,147,483,647. Examples of whole numbers are:

  • -1
  • 0
  • +1
  • 50
  • 333
As in the above case where we issued the SELECT command where we issued the SELECT command, we issue the following command.

 
SELECT * FROM Balance where Balance > 4000
 

Instead of '4000' in the command, you will note that we have 4000, which is a whole number. In the first case we have a character string and in the second case we have a number. The resulting table looks like:

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1234777890 5996 04/15/2005 95.51
1234567900 6006 04/15/2005 95.77
1234000890 9997 04/15/2005 95.17
1238888890 6057 04/15/2005 95.39
1212127890 4997 04/15/2005 95.31


The problem with this result is that we lose the decimal point and the cents. So what can we do to solve this problem? Let's declare the Balance column/field as a data type of currency and issue the following command. Currency is of the form of a country's money. In the U.S.A., it is of the form $498.77.

 
SELECT * FROM Balance where Balance > 4000
 

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1234777890 5995.97 04/15/2005 95.51
1234567900 6005.91 04/15/2005 95.77
1234000890 9996.53 04/15/2005 95.17
1238888890 6056.77 04/15/2005 95.39
1212127890 4996.88 04/15/2005 95.31

When you issue the following SQL command, you get a result similar to the above results.

 
SELECT * FROM Balance where Balance >= 4000
 

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1234777890 5996.0 04/15/2005 95.51
1234567900 6006.0 04/15/2005 95.77
1234000890 9997.0 04/15/2005 95.17
1238888890 6057.0 04/15/2005 95.39
1212127890 4997.0 04/15/2005 95.31


If you issue the following command, you will get:
 
SELECT * FROM Balance where Balance >= 6000
 

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1234000890 9996.53 04/15/2005 95.17
1238888890 6057.77 04/15/2005 95.39


Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who had a balance greater than or equal to $6,000.

More Complex Conditions: Compound Conditions / Logical Operators

There are operators that allow you to have multiple conditions in the WHERE clause. These operators are called logical operators and are listed below.

Operator Description
AND The AND operator is used to combine two logical operands. The operands are comparisons or logical expressions. It has the following general format:
condition1 AND condition2
both conditions must evaluate to true for the AND to be true, otherwise it evaluates to false.
This operator is supported by MS Access
OR The OR operator is used to combine two logical operands. The operands are comparisons or logical expressions. It has the following general format:
condition1 OR condition2
The OR operator returns:
  • True - if either condition evaluates to true
  • False - if both conditions evaluate to false

This operator is supported by MS Access
NOT The NOT operator is used to negate the result of a comparison expression or a logical expression. It has the following general format:
NOT condition1
  • If condition1 evaluates to true, the NOT operator negates the result to false
  • If condition1 evaluates to false, the NOT operator negates the result to true

This operator is supported by MS Access
IS NULL The null operator is used to compare a value with the NULL value. It has the following general format:
WHERE condition1 IS NULL
The equivalent operation in MS Access is:
field1 = ''
or
condition1 = ''
BETWEEN The BETWEEN operator is used to search for values between two values It has the following general format:
WHERE BALANCE BETWEEN 2000 AND 6000
IN The IN operator is used to compare values in a list to those fields/columns encountered in the specified table(s).
LIKE The LIKE operator is used to compare values in the specified table(s) to similar values specified using wildcards.


AND operator

The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions hold true).

 
SELECT * FROM AccountName, Balance WHERE
AccountName.AccountNo = Balance.AccountNo  AND Balance.Balance > 4000
  

The result of the above command is shown in the following table.

 AccountNo  
 FirstName  
 LastName  
 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
1234777890 Carole Fusemi 1234777890 5995.97 04/15/2005 95.51
1234567900 Peter Wie 1234567900 6005.91 04/15/2005 95.77
1234000890 Donald Hill 1234000890 9996.53 04/15/2005 95.17
1238888890 Althea Keyes 1238888890 6056.77 04/15/2005 95.39
1212127890 Keith Makey 1212127890 4996.88 04/15/2005 95.31

OR operator

The OR operator joins multiple conditions in the WHERE clause, and it returns a row if ANY of the conditions listed evaluate to true. For example, to show those accounts in the Balance table for persons from Apex or Cary, we issue the following command:


SELECT * FROM  AccountAddr  where AccountAddr.City =  'Apex' OR AccountAddr.City = 'Cary'
 

The result of the above command is the following:

 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1236667890 123 Saunders St. Apex NC 27500 USA
1234000890 123 Oak Blvd. Cary NC 27500 USA
1222567890 123 Walnut Lane Apex NC 27500 USA
1989867890 123 Main St. Apex NC 27709 USA


If we want to see names associated with these accounts, we issue the SQL SELECT command against the AccountName and AccountAddr tables. The command would look like:

SELECT * FROM AccountName, AccountAddr  where (AccountName.AccountNo = 
AccountAddr.AccountNo) AND (AccountAddr.City =  'Apex' OR AccountAddr.City = 'Cary')
 

The result of the above command is the following tables:

 AccountNo  
 FirstName  
 LastName  
 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1236667890 Mike Jordan 1236667890 123 Saunders St. Apex NC 27500 USA
1234000890 Donald Hill 1234000890 123 Oak Blvd. Cary NC 27500 USA
1222567890 Regina Bell 1222567890 123 Walnut Lane Apex NC 27500 USA
1989867890 John Wieland 1989867890 123 Main St. Apex NC 27709 USA

NOT operator

The NOT operator negates an evaluation of a condition. For example, if a condition evaluates to true, then the NOT changes that evaluation to false. If we negate part of the above example, we get the following SQL


SELECT * FROM AccountName, AccountAddr  where (AccountName.AccountNo = 
AccountAddr.AccountNo) AND NOT (AccountAddr.City =  'Apex' OR AccountAddr.City = 'Cary')
 

The result of issuing the above SQL command results in the following.

 AccountNo  
 FirstName  
 LastName  
 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1444567890 Julie Smith 1444567890 123 Oak St. Raleigh NC 27500 USA
1255567890 Cato Willingham 1255567890 123 Jones St. Durham NC 27500 USA
1234777890 Carole Fusemi 1234777890 123 Oak Lane Raleigh NC 27500 USA
1234567900 Peter Wie 1234567900 123 Oak Way GarnerNC27500USA
1238888890AltheaKeyes1238888890123 Walnut WayRaleighNC27500USA
1234599999CharlesLawrence1234599999123 Whatis St.GarnerNC27500USA
1233267890RobertUrengo1233267890123 Academy St.DurhamNC27500USA
1212127890KeithMakey1212127890404 Walnut Ave.RaleighNC27511USA

You will note that there is no data from Apex or Cary.

IS NULL operator

The IS NULL operator checks a field or condition for being equivalent to the NULL value. When we issue the following command

 
SELECT * FROM AccountAddr WHERE City  IS  NULL
 

There are no records in the record set. We can verify the above assertion by using the NOT operator in the following SQL command.
 
SELECT * FROM AccountName, AccountAddr  where (AccountName.AccountNo = 
AccountAddr.AccountNo)  AND  AccountAddr.City  IS NOT NULL
 

This results in the following record set.

 AccountNo  
 FirstName  
 LastName  
 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1444567890JulieSmith1444567890123 Oak St.RaleighNC27500USA
1333567890MaryEasley1333567890123 Walnut St.ApexNC27500USA
1255567890CatoWillingham1255567890123 Jones St.DurhamNC27500USA
1236667890MikeJordan1236667890123 Saunders St.ApexNC27500USA
1234777890CaroleFusemi1234777890123 Oak LaneRaleighNC27500USA
1234567900PeterWie1234567900123 Oak WayGarnerNC27500USA
1234000890DonaldHill1234000890123 Oak Blvd.CaryNC27500USA
1222567890ReginaBell1222567890123 Walnut LaneApexNC27500USA
1238888890AltheaKeyes1238888890123 Walnut WayRaleighNC27500USA
1234599999CharlesLawrence1234599999123 Whatis St.GarnerNC27500USA
1233267890RobertUrengo1233267890123 Academy St.DurhamNC27500USA
1989867890JohnWieland1989867890123 MaIn St.ApexNC27709USA
1212127890KeithMakey1212127890404 Walnut Ave.RaleighNC27511USA

BETWEEN operator

The BETWEEN operator when used in MS Access is of the following SQL command format.

 
SELECT * FROM Balance WHERE BALANCE.BALANCE Between 4000 AND 8000
 

This command results in the following record set.

 AccountNo  
 Balance  
 LastPay  
 AmountPayed  
12347778905995.9704/15/200595.51
12345679006005.9104/15/200595.77
12388888906056.7704/15/200595.39
12121278904996.8804/15/200595.31

IN operator

The IN operator evaluates a condition to the values in a list. In general, the SQL command would look like:

 
SELECT City FROM AccountAddr WHERE City IN ('Apex', 'Raleigh') 
 

SELECT * FROM AccountAddr WHERE AccountAddr.City In('Apex', 'Raleigh')
The result of the above command is the following table.

 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1333567890123 Walnut St.ApexNC27500USA
1236667890123 Saunders St.ApexNC27500USA
1234777890123 Oak LaneRaleighNC27500USA
1222567890123 Walnut LaneApexNC27500USA
1238888890123 Walnut WayRaleighNC27500USA
1989867890123 Main St.ApexNC27709USA
1212127890404 Walnut Ave.RaleighNC27511USA

We can order the results by modifying the above SQL command to look like:

 
SELECT * FROM AccountAddr WHERE AccountAddr.City = 'Apex' 
OR AccountAddr.City = 'Raleigh'  order by AccountAddr.City
 

The resulting record set produces the following table.

 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1222567890123 Walnut LaneApexNC27500USA
1236667890123 Saunders St.ApexNC27500USA
1333567890123 Walnut St.ApexNC27500USA
1212127890404 Walnut Ave.RaleighNC27511USA
1238888890123 Walnut WayRaleighNC27500USA
1234777890123 Oak LaneRaleighNC27500USA
1444567890123 Oak St.RaleighNC27500USA

LIKE operator

The LIKE operator looks for a specified pattern, using the following wildcards.

  • % - means any series of characters.
  • _ - means any single character.

For example
 
SELECT * FROM AccountAddr WHERE AccountAddr.City  LIKE 'A%' 
 

The above SQL command results in the following result set.

 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1236667890123 Saunders St.ApexNC27500USA
1222567890123 Walnut LaneApexNC27500USA
1989867890123 Main St.ApexNC27709USA


The percent sign (%), which is also known as a wildcard, is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "A".












[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#
© 2002 - 2015 All Rights Reserved Total Application Works