Structured query language, or SQL as it has come to be called today, is the lingua franca of relational database management systems. Visual Basic 4.0 and Microsoft Access both use SQL exclusively to process queries against desktop, client/server, and
mainframe databases. Access includes a graphical query by example (QBE) tool—the query design mode window—to write Jet (Access) SQL statements for you. You can develop quite sophisticated applications with Access without even looking at an SQL
statement in Access's SQL window. Visual Basic 4.0 does not include a graphical QBE tool, so until some enterprising third-party developer creates a Query OLE control, you'll need to learn Jet SQL to create Visual Basic 4.0 applications that interact in
any substantial way with databases.
Microsoft Access, without a version number, refers to Access 2.0 and Access for Windows 95 version 7.0. There are no significant differences between Jet SQL in these two versions of Access. Access 2.0 added a substantial number of reserved words to the SQL vocabulary of Access 1.x. The new TOP n and TOP n PERCENT Jet SQL reserved words are discussed in the "Comparing the Jet SQL Dialect with ANSI SQL-92" section, and subqueries are covered by the "Using Nested Queries and Subqueries" section of this chapter. SQL statements for adding tables to Access databases, plus adding fields and indexes to Access tables are two of the subjects of Chapter 7, "Running Crosstab and Action Queries." Microsoft calls the Visual Basic 4.0 dialect of SQL Microsoft Jet Database Engine SQL. This book uses the term Jet SQL; although the dialect originated in Microsoft Access 1.0, the Jet database engine now can be used by any application that supports VBA.
The first part of this chapter introduces you to the standardized version of SQL specified by the American National Standards Institute (ANSI), a standard known as X.3.135-1992 and called SQL-92 in this book. (When you see SQL-89 or SQL-92, the
reference is to ANSI SQL, not the Access variety.) ANSI SQL-92 has been accepted by the International Standards Organization (ISO), a branch of the United Nations headquartered in Geneva, and the International Electrotechnical Commission (IEC) as ISO/IEC
9075:1992, or Database Language SQL. A separate ANSI standard, X.3.168-1989, defines Database Language Embedded SQL. Thus, SQL-92 is a thoroughly standardized language—much more so than xBase, for which no independent standards
yet exist. Today's client/server RDBMSs support SQL-89 and many of SQL-92's new SQL reserved words; many RDBMSs also add their own reserved words to create proprietary SQL dialects. A knowledge of ANSI SQL is required to use SQL passthrough techniques with
the Jet 3.0 database engine and to employ the Remote Data Object (RDO) and Remote Data Control (RDC) of the Enterprise Edition of Visual Basic 4.0. SQL passthrough is described in Chapter 20, "Creating Front-Ends for Client/Server Databases," and
the RDO/RDC combination is covered by Chapter 22, "Using the Remote Data Object."
The second part of the chapter, beginning with the section, "Comparing the Jet SQL Dialect with ANSI SQL-92" discusses the differences between SQL-92 and Jet SQL. If you're fluent in the ANSI versions of SQL, either SQL-89 or SQL-92, you'll
probably want to skip to the latter part of this chapter, which deals with the flavor of SQL used by the Jet 3.0 database engine. Chapter 19, "Understanding the Open Database Connectivity API," describes how the Jet 3.0 database engine translates
Jet SQL into the format used by ODBC drivers. Although this chapter describes the general SQL syntax for queries that modify data (called action queries by Access and in this book) and the crosstab queries of Jet SQL, examples of the use of these
types of queries are described in Chapter 7.
Dr. E. F. Codd's relational database model of 1970, discussed in the preceding chapter, was a theoretical description of how relational databases are designed, not how they are used. You need a database application language to create tables and specify
the fields the tables contain, to establish relations between tables, and to manipulate the data in the database. The first language that Dr. Codd and his associates at the IBM San Jose laboratory defined was Structured English Query Language (SEQUEL),
which was designed for use with a prototype relational database that IBM called System R. The second version of SEQUEL was called SEQUEL/2, which was later renamed SQL. Technically, SQL is the name of an IBM data manipulation language, not an abbreviation
for structured query language. As you'll see later in this chapter, there are significant differences between IBM's SQL used for their DB2 mainframe and DB2/2 OS/2 databases, and ANSI SQL-92.
The sections that follow describe the differences between SQL and the procedural languages commonly used for computer programming, and how applications use SQL with desktop, client/server, and mainframe databases.
This book has made extensive use of the term query without defining what the term means. Because Visual Basic 4.0 uses SQL to process all queries, this book defines query as an expression in any dialect of SQL that defines an operation to
be performed by a database management system. A query usually contains at least the following three elements:
The simplest SQL query that you can construct is SELECT * FROM TableName, which returns the entire content of TableName as the query result set. Queries are classified in this book as select queries, which return data (query result
sets), or action queries, which modify the data contained in a database without returning any data.
IBM's original version of SQL, implemented as SEQUEL, had relatively few reserved words and simple syntax. Over the years, new reserved words have been added to the language by publishers of database management software. Many of the reserved words in
proprietary versions of SQL have found their way into the ANSI SQL standards. Vendors of SQL RDBMSs that claim adherence to the ANSI standards have the option of adding their own reserved words to the language, as long as the added reserved words don't
conflict with the usage of the ANSI-specified reserved words. Transact-SQL, the language used by the Microsoft and Sybase versions of SQL Server, has many more reserved words than conventional ANSI SQL. Transact-SQL even includes reserved words that enable
conditional execution and loops within SQL statements. (The CASE, NULLIF, and COALESCE reserved words of SQL-92 are rather primitive for conditional execution purposes.) Jet SQL includes the TRANSFORM and PIVOT statements needed to create crosstab queries
that, while missing from ANSI SQL, are a very useful construct. You can write ANSI SQL statements that create crosstab queries, but such statements have a very complex syntax.
A further discussion of the details of the syntax of SQL statements appears after the following sections, which describe the basic characteristics of the SQL language and how you combine SQL and conventional 3GL source code statements.
All the dialects of SQL are fourth-generation languages (4GLs). The term fourth-generation derives from the following descriptions of the generations in the development of languages to control the operation of computers:
Whether SQL is a true 4GL is subject to controversy, because the SQL statements you write are actually executed by a 3GL or, in some cases, a 2GL language that deals directly with the data stored in the database file(s) and is responsible for sending your application the data in a format the application can understand.
Regardless of the controversy over whether or not generic SQL is a 4GL, you need to be aware of some other differences between SQL and conventional 3GLs. The most important of these differences are as follows:
Consider yourself fortunate that you are using Visual Basic 4.0 to process SQL statements. You don't need to worry about how many rows a query will return or what data types occur in the query result set's columns. The Recordset object receiving the
data handles all of these details for you. You don't need to recompile and link your Visual Basic application each time you change a query statement; just change the statement and run your application again. Visual Basic "compiles" the changes
for you automatically.
Four different methods of executing SQL statements are recognized by the current ANSI SQL standards. The methods you use depend on your application programming environment, as described in the following list:
Technically, static SQL and dynamic SQL are methods of binding SQL statements to database application programs. Binding refers to how you combine or attach SQL statements to your source or object code, how you pass values to SQL statements, and
how you process query result sets. A third method of binding SQL statements is the call-level interface (CLI). The Microsoft Open Database Connectivity (ODBC) API uses the CLI developed by the SQL Access Group (SAG), a consortium of RDBMS publishers and
users. A CLI accepts SQL statements from your application in the form of strings and then passes the statements directly to the server for execution. The server notifies the CLI when the data is available and then returns the data to your application.
Details of the ODBC CLI are given in Chapter 20.
If you are a COBOL coder or a C/C++ programmer who is accustomed to writing embedded SQL statements, you'll need to adjust to Visual Basic's automatic creation of virtual tables when you execute a SELECT query, rather than executing CURSOR-related
FETCH statements to obtain the query result rows one-by-one.
ANSI SQL statements have a very flexible format. Unlike all dialects of BASIC, which separate statements with newline pairs (carriage return and line feed), and C, C++, and Pascal, which use semicolons as statement terminators, SQL does not require
that you separate the elements that constitute a complete SQL statement with newline pairs, semicolons, or even a space in most cases. (SQL ignores most white space, which comprises newline pairs, tabs, and extra spaces.) Therefore, you can use white space
to format your SQL statements to make them more readable. The examples of SQL statements in this book place groups of related identifiers and SQL reserved words on separate lines and use indentation to identify continued lines. Here is an example of an Jet
SQL crosstab query statement that uses this formatting convention:
TRANSFORM Sum(CLng([Order Details].UnitPrice*Quantity*
(1 - Discount)*100)/100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID
FROM Orders, Products, [Order Details],
Orders INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID,
Products INNER JOIN [Order Details] ON Products.ProductID =
[Order Details].ProductID
WHERE Year(OrderDate)=1994
GROUP BY Products.ProductName, Orders.CustomerID
ORDER BY Products.ProductName
PIVOT "Qtr " & DatePart("q",OrderDate) In("Qtr 1",
"Qtr 2","Qtr 3","Qtr 4")
The square brackets surrounding the [Order Details] table name are specific to Jet SQL and are used to group table or field names that contain spaces or other punctuation that is illegal in the naming rules for tables and fields of SQL RDBMSs. Jet SQL also uses the double quotation mark (") to replace the single quote mark or apostrophe ('), which acts as the string identifier character in most implementations of SQL. The preceding example of the SQL statement for a crosstab query is based on the tables in the Northwind.mdb sample database of Access 95. Many field names in Access 2.0's NWIND.MDB contain spaces; spaces are removed from field names in Northwind.mdb.
The sections that follow describe how you categorize SQL statements and how the formal grammar of SQL is represented. Also, they provide examples of writing a variety of select queries in ANSI SQL.
ANSI SQL is divided into the following six basic categories of statements, presented here in the order of most frequent use:
It is not obligatory that a publisher of a DBM who claims to conform to ANSI SQL support all of the reserved words in the SQL-92 standard. In fact, it is probably safe to state that there is no commercial RDBMS (at this book's writing) that implements
all the SQL-92 keywords for interactive SQL. The Jet 3.0 database engine, for example, does not support any DCL reserved words. You use the Data Access Object's programmatic security objects with Visual Basic reserved words and keywords instead. The Jet
3.0 engine does not need to support CCL statements, because neither Visual Basic 4.0 nor Access manipulate cursors, per se. The Data control of Visual Basic 4.0, described in Chapter 3, "Using Visual Basic's Data Control," creates the equivalent
of a scrollable, bidirectional cursor. The Remote Data Object supports Microsoft SQL Server 6.0's scrollable cursors.
This book uses the terminology defined by Appendix C of the Programmer's Reference for the Microsoft ODBC Software Development Kit (SDK) to define the following levels of SQL grammatical compliance:
The formal grammar of SQL is represented in the Backus Naur form (BNF), which is used to specify the formal grammar of many computer programming languages. Here is the full BNF of the verb that specifies the operation a query is to perform on a
database:
<action> ::=
SELECT
|DELETE
|INSERT [ <left paren> <privilege column list> <right paren>]
|UPDATE [ <left paren> <privilege column list> <right paren>]
|REFERENCES [ <left paren> <privilege column list> <right paren>]
|USAGE
...
<privilege column list> ::= <column name list>
...
<column name list> ::= <column name> [{<comma>, <column name>} ...]
To use BNF representation, you locate the class (<action> in the preceding example) where the reserved word is included. Members of the class are separated with the vertical bar (|) character. Optional parameters of reserved words and elements
are enclosed with square brackets ([]). Literal values, such as <privilege column list>, are enclosed within angle braces (<>), and elements that must be grouped, such as a comma preceding a second <column name>, are enclosed within curly
braces ({}). You then search the list of elements to find the allowable composition of an element. In the previous example, the <privilege column list> is composed of the <column name list>. Then, check to see if <column name list> has a
composition (in this case, one or more <column name> elements). This process is tedious, especially when the elements are not arranged in alphabetic order.
Microsoft uses a simplified form of BNF to describe the grammar supported by the present version of the ODBC API. The Jet SQL syntax rules eliminate the use of the ::= characters to indicate the allowable substitution of values for an element and
instead substitute a tabular format, as illustrated in Table 5.1. Ellipses (. . .) in the table indicate that you have to search for the element; the element is not contiguous with the preceding element of the table.
| Element | Syntax |
| select-statement | SELECT[ALL|DISTINCT|DISTINCTROW] select-list table-expression |
| . . . | |
| select-list | *|select-sublist[{, select-sublist}. . .] |
| select-sublist | table-name.*|expression [AS column-alias]|column-name |
| . . . | |
| table-expression | from-clause|[where-clause]|[group-by-clause]|[having-clause]|[order-by-clause] |
| . . . | |
| from-clause | FROM table-reference-list |
| table-reference-list | table-reference [{, table-reference}. . .] |
| table-reference | table-name [AS correlation-name|joined-table] |
| . . . | |
| table-name | base-table-name|querydef-name|attached-table-name|correlation-name |
The DISTINCTROW qualifier and the querydef-name element are specific to Jet SQL. DISTINCTROW is discussed in the section, "Theta Joins and the DISTINCTROW Keyword," of this chapter, and Chapter 2 describes the Access QueryDef object.
After you've looked up all the allowable forms of the elements in the table, you may have forgotten the key word whose syntax you set out to determine. The modified Backus Naur form used by Microsoft is unquestionably easier to use than full BNF.
Here is A more practical representation of the syntax of a typical ANSI SQL statement, which substitutes underscores for hyphens:
SELECT [ALL|DISTINCT] select_list
FROM table_names
[WHERE {search_criteria|join_criteria}
[{AND|OR search_criteria}]
[ORDER BY {field_list} [ASC|DESC]]
The following list explains the use of each SQL reserved word in the preceding statement:
If you use more than one table in your query, make sure that you create a join between the tables with a WHERE Table1.field_name = Table2.field_name clause. If you omit the statement that creates the join, your query will return the Cartesian product of the two tables. A Cartesian product is all the combinations of fields and rows in the two tables. A Cartesian product results in extremely large query-return set and, if the tables have a large number of records, can cause your computer to run out of memory due to the size of the query result set. (The term Cartesian is derived from the name of a famous French mathematician, René Déscartes.)
Depending on the dialect of SQL your database uses and the method of transmitting the SQL statement to the DBM, you may need to terminate the SQL statement with a semicolon. (Jet SQL no longer requires the semicolon; statements you send directly to the
server through the ODBC driver do not use terminating semicolons.)
The Visual Data sample application that accompanies Visual Basic 4.0 (\VB4\Samples\Visdata\Visdata.vbp) is a new and improved version of the Visual Data application that originated with Visual Basic 2.0. VisData falls into the category of ad
hoc query generators. Although you can use Visual Basic 4.0's Data Manager add-in to execute ad hoc SQL queries, VisData has several useful query execution and viewing options not offered by Data Manager. You can use VisData to test some simple SQL
statements by following these steps:
Figure 5.2. The query result window of the VisData application using the DBGrid control option.
The VisData application included with Visual Basic 3.0 loaded a conventional Grid control by means of a Visual Basic procedure. Using code to load a Grid control lets you separately determine the time to create the Recordset object and to load the Recordset object's data into a grid. Conventional Grid controls and unbound third-party spreadsheet OLE controls often are used in client/server front-end applications that return Recordset objects of the Snapshot type. The ability to determine the time to acquire the data and to load the grid or spreadsheet is useful for troubleshooting performance problems.
The square brackets ([]) surrounding Company Name are necessary when a field name or a table name contains a space. Only Jet databases permit spaces and punctuation other than the underscore (_) in field names. Using spaces in field and table names, or to name any other database object, is not considered a good database-programming practice. Spaces in database field and table names appear in this book only when such names are included in sample databases created by others.
Figure 5.5. The query return set displaying only three fields of the Publishers table.
The DBGrid window of VisData provides a number of buttons that enable you to filter the records so that only selected records appear and to sort the records on selected fields. A filter is the equivalent of adding a WHERE field_name where_expression clause to your SQL statement. The sort button adds an ORDER BY field_names clause.
Microsoft designed the VisData sample application to demonstrate the features of Visual Basic 4.0 that pertain to manipulating and displaying data contained in the tables of databases. VisData is a rich source of Visual Basic code examples. VisData
includes a class module, VisDataClass, that lets you use VisData as a Visual Basic 4.0 add-in or run VisData as an OLE Automation server. VisData also contains useful examples of code to create and control the appearance of MDI child forms.
As mentioned previously in this chapter, SQL provides the basic arithmetic operators: <, <=, =, =>, > and <>. SQL adds a set of operators that are used in conjunction with values of fields of the text data type (LIKE and IN) and to
deal with NULL values in fields (IS NULL and IS NOT NULL). The Jet 3.0 database engine also supports the use of many of the native string and numeric functions of VBA in SQL statements to calculate column values of query return sets. (Few of these VBA
functions are included in ANSI SQL.)
Access supports the use of user-defined functions (UDFs) in SQL statements to calculate column values in queries. Visual Basic only supports native functions that are reserved words, such as Val(). Functions other than SQL aggregate functions are called implementation-specific in ANSI SQL. Implementation-specific means that the supplier of the RDBMS is free to add functions to the supplier's implementation of ANSI SQL, unless the names of such functions conflict with SQL-92 reserved words.
The majority of the operators you use in SQL statements are dyadic. Dyadic functions require two operands. (All arithmetic functions and BETWEEN are dyadic.) Operators such as LIKE, IN, IS NULL, and IS NOT NULL are monadic. Monadic operators require
only one operand. All expressions that you create with comparison operators return True or False, not a value. The sections that follow describe the use of the common dyadic and monadic operators of ANSI SQL.
The use of arithmetic operators with SQL does not differ greatly from their use in Visual Basic or other computer languages. The following is a list of the points you need to remember about arithmetic operators and functions used in SQL statements
(especially in WHERE clauses):
If you attempt to execute the preceding SQL statement against Biblio.mdb in VisData, you receive the following error message: Error 3071. Can't evaluate expression. The error is caused by a Null value in the Zip data cell of one publisher (PubID 81, S. Pitman) in the table. Most expressions do not accept Null argument values. (The VBA IsNull() function is an obvious exception.) Therefore, you would assume that you need to add an IS NOT NULL criterion to your WHERE clause. Using WHERE Zip IS NOT NULL AND Val(Zip) > 12000 solved the problem in Visual Basic 3.0 with the Jet 1.1 database engine, because the Zip IS NOT NULL expression was evaluated before the Val(Zip) > 12000 expression. This WHERE clause no longer works in Jet 2.0+, because both expressions are evaluated for each record. A workaround for this problem is included in the section "Self-Joins and Composite Columns," later in this chapter. The syntax of the NULL predicates is explained in the section "Monadic Text and NULL Value Operators," which follows. Note that Val(Zip) is not set in bold type. This is because the function is a part of an SQL statement. When you enclose a VBA keyword within a String variable, such as an SQL statement, the VBA parser does not recognize Val() as a native VBA function.
Where Jet SQL uses syntax that is not specified by ANSI SQL, such as the use of number signs (#) to indicate date-time field data types, or where examples of complete statements are given in Jet SQL, the SQL reserved words that are also VBA keywords or reserved words appear in the uppercase and lowercase conventions of VBA.
You can create calculated columns in query return sets by defining fields that use SQL arithmetic operators. You also can use those functions that are supported by the Jet 3.0 database engine or your client/server RDBMS. Ordinarily, calculated columns
are derived from fields of numeric field data types. Biblio.mdb uses a numeric data type (the auto-incrementing long integer Counter field) for ID fields; therefore, you can use the PubID field or Val(Zip) expression as the basis for the calculated field.
Type SELECT PubID, Name, Zip, (3 * Val(Zip)) AS Zip_Times_3 FROM Publishers in the SQL Statement window of VisData. The query result set appears in Figure 5.6.
Figure 5.6. A calculated column added to the query against the publisher's table.
You do not receive an error message when you execute the SELECT PubID, Name, Zip, (3 * Val(Zip)) AS Zip_Times_3 FROM Publishers query. The row that causes the error because of a NULL Zip entry, PubID 81, does not appear in the query result set.
The AS qualifier designates an alias for the column name, column_alias. With Jet 3.0, the AS qualifier is optional; you can substitute a space for the AS qualifier, as in SELECT PubID, Name, Zip, 3*Val(Zip) Zip_Times_3 FROM Publishers. If you
don't supply the [AS] column_alias qualifier, the column name is Expr1003 when you use the Access database engine. Access 1.x provides a default AS Expr1 column alias for calculated columns; the column_alias that appears when you use ODBC to
connect to databases is implementation specific. IBM's DB2 and DB2/2, for example, do not support aliasing of column names with the AS qualifier. ODBC drivers for DB2 and DB2/2 may assign the field name from which the calculated column value is derived or
apply an arbitrary name, such as Col_1.
If you must include spaces in the column_alias, make sure that you enclose the column_alias within square brackets for the Jet database engine and in single quote marks for RDBMSs that support spaces in column_alias fields. Although you may see column names such as Col 1 when you execute queries against DB2 or other mainframe databases in an emulated 3270 terminal session, these column_alias values are generated by the local query tool running on your PC, not by DB2.
One of the most useful operators for the WHERE criterion of fields of the text field data type is ANSI SQL's LIKE predicate, called the Like operator in Jet SQL. (The terms predicate and operator are used interchangeably in this context.)
The LIKE predicate enables you to search for one or more characters you specify at any location in the text. Table 5.2 shows the syntax of the ANSI SQL LIKE predicate and the Jet SQL Like operator used in the WHERE clause of an SQL statement.
| Purpose | ANSI SQL | Jet SQL | Returns |
| Match any text that contains the characters | LIKE '%am%' | Like "*am*" | ram, rams, damsel, amnesty |
| Match any text beginning with the characters | LIKE 'John%' | Like "John*" | Johnson, Johnsson |
| Match any text ending with the characters | LIKE '%son' | Like "*son" | Johnson, Anderson |
| Match the text and any single trailing character | LIKE 'Glen_' | Like "Glen?" | Glenn, Glens |
| Match the text and any single preceding character | LIKE '_am' | Like "?am" | dam, Pam, ram |
| Match the text with one preceding character and any trailing characters | LIKE '_am%' | Like "_am*" | dams, Pam, Ramses |
The IS NULL and IS NOT NULL predicates test whether a value has been entered in a field. IS NULL returns False and IS NOT NULL returns True if a value, including an empty string "" or 0, is present in the field. The VBA
IsNull(field_name) function and the SQL field_name IS NULL expressions are equivalent.
You determine the case-sensitivity (whether uppercase characters match lowercase characters) of Visual Basic database applications that use the Jet 3.0 database engine with the Option Compare Binary (case-sensitive) or Option Compare Text
(case-insensitive) keywords. The case sensitivity of the LIKE predicate for client/server RDBMSs to which you send queries with the SQL passthrough option is installation specific. The DBA sets the case-sensitivity of searches upon installing the RDBMS or
creating a new database. You can use the SQL-92 UPPER() and LOWER() functions with SQL passthrough queries or the equivalent VBA functions, UCase() and LCase(),to remove case-sensitivity from matches. (Jet 3.0 does not support ANSI SQL
UPPER() and LOWER() functions.)
The SQL-92 POSITION() function returns the position of characters in a test field using the syntax POSITION(characters IN field_name). The equivalent Jet SQL function is InStr(field_name, characters), which is also an VBA function.
If characters are not found in field_name, both functions return 0.
The SQL-92 SUBSTRING() function returns a set of characters with SUBSTRING(field_name FROM start_postion FOR number_of_characters). The Jet SQL equivalent is VBA's Mid(field_name, start_position[,
number_of_characters]). Both of these functions are quite useful for selecting and parsing text fields.
As mentioned earlier in this chapter, you can join two tables by using table_name.field_name operands with a comparison operator in the WHERE clause of an SQL statement. Additional tables can be joined by combining two sets of join statements
with the AND operator. SQL-86 and SQL-89 supported only WHERE joins. You can create equi-joins, natural equi-joins, left and right equi-joins, not-equal joins, and self-joins with the WHERE clause. Joins that are created with the equal (=) operator use
"equi-" as the prefix.
SQL-92 added the JOIN reserved words plus the CROSS, NATURAL, INNER, OUTER, FULL, LEFT, and RIGHT qualifiers to describe a variety of JOINs. At the time this book was written, few RDBMSs supported the JOIN statement. (Microsoft SQL Server, for example,
does not include the JOIN statement in Transact-SQL.) Jet SQL supports INNER, LEFT, and RIGHT JOINs with SQL-92 syntax using the ON predicate. Jet SQL does not support the USING clause, or the CROSS, NATURAL, or FULL qualifiers for JOINs.
A CROSS JOIN returns the Cartesian product of two tables. The term CROSS is derived from cross-product, a synonym for Cartesian product. You can emulate a CROSS JOIN by leaving out the join components of the WHERE clause of a SELECT
statement that includes a table name from more than one table. Figure 5.7 shows VisData's DBGrid window displaying the first 12 rows of the 11,658-row Cartesian product created by entering SELECT Publishers.Name, Authors.Author FROM Publishers, Authors in
the SQL Statement window. There are 67 Publishers records and 174 Authors records, thus the query returns 11,658 rows (67 * 174 = 11,658). It is highly unlikely that you would want to create a CROSS JOIN in a commercial database application.
Figure 5.7. Twelve rows of the 11,658-row Cartesian product from the Publishers and Authors table.
The common types of joins that you can create with SQL-89 and Jet SQL are described in the sections that follow.
All joins, except the CROSS JOIN or Cartesian product, require that the field data types of the two fields be identical or that you use a function, where supported by the RDBMS, to convert dissimilar field data types to a common type.
The most common type of join is the equi-join or INNER JOIN. You create an equi-join with a WHERE clause using the following generalized statement:
SELECT Table1.field_name, ... Table2.field_name ... FROM Table1, Table2 WHERE Table1.field_name = Table2.field_name
The SQL-92 JOIN syntax to achieve the same result is as follows:
SELECT Table1.field_name, ... Table2.field_name ...
FROM Table1 INNER JOIN Table2
ON Table1.field_name = Table2.field_name
A single-column equi-join between the PubID field of the Publishers table and the PubID field of the Titles table of the Biblio.mdb database appears as follows:
SELECT Publishers.Name, Titles.ISBN, Titles.Title
FROM Publishers INNER JOIN Titles
ON Publishers.PubID = Titles.PubID
When you execute this query with the VisData application, the Publishers and Titles tables are joined by the PubID columns of both fields. The result of the join appears in Figure 5.8.
The INNER qualifier is optional in SQL-92, but is required in Jet SQL. If you omit the INNER qualifier when you use the Jet database engine, you receive the message Syntax error in FROM clause when you attempt to execute the query.
Figure 5.8. VisData displaying an equi-join on the PubID fields of the Publishers and Titles tables.
Natural equi-joins create joins automatically between identically named fields of two tables. Natural equi-joins eliminate the necessity of including the ON predicate in the JOIN statement. Jet SQL does not support the NATURAL JOIN statement, and no commercial client/server RDBMS had implemented NATURAL JOIN at the time this book was written.
The Jet SQL statements that you create in the graphical QBE design mode of Access generate an expanded JOIN syntax. Access separates the JOIN statement from a complete FROM clause with a comma and then repeats the table names in a separate, fully
defined JOIN statement. Using the Jet SQL syntax shown in the following example gives the same result as the preceding ANSI SQL-92 example.
SELECT DISTINCTROW Publishers.Name, Titles.ISBN, Titles.Title
FROM Publishers, Titles,
Publishers INNER JOIN Titles
ON Publishers.PubID = Titles.PubID
The purpose of the DISTINCTROW statement in Jet SQL is discussed in the section "Comparing the Jet SQL Dialect with ANSI SQL-92," which follows later in the chapter.
The equivalent of the two preceding syntax examples, using the WHERE clause to create the join, is as follows:
SELECT Publishers.Name, Titles.ISBN, Titles.Title FROM Publishers, Titles WHERE Publishers.PubID = Titles.PubID
There is no difference between the result of using INNER JOIN and the WHERE clause to create an equi-join. The SQL WHERE clause is simpler, so few developers use the JOIN syntax.
Equi-joins return only rows in which the values of the joined fields match. Field values of records of either table that do not have matching values in the other table do not appear in the query result set returned by an equi-join. If there is no match between any of the records, no rows are returned. A query result set without rows is called a null set.
You can create multiple equi-joins to link several tables by pairs of fields with common data values. For example, you can link the Publishers, Titles, Title Author, and Authors tables of Biblio.mdb with the following Jet SQL statement:
SELECT DISTINCTROW Titles.Title, Publishers.Name,
Titles.ISBN, Authors.Author
FROM Publishers INNER JOIN
(Authors INNER JOIN
(Titles INNER JOIN [Title Author]
ON Titles.ISBN = [Title Author].ISBN)
ON Authors.Au_ID = [Title Author].Au_ID)
ON Publishers.PubID = Titles.PubID
The preceding SQL statement uses nested joins to create the required relationships to display title, publisher, and author data for each book. Using JOIN syntax becomes quite complex when multiple joins are involved. Access's graphical QBE
window lets you create such SQL statements much faster than you can write and test them yourself. The query result set from the preceding Jet SQL query appears in Figure 5.9.
Figure 5.9. The query result set with four tables joined.
The equivalent of the preceding example using the ANSI SQL WHERE clause is as follows:
SELECT Titles.Title, Publishers.Name,
Titles.ISBN, Authors.Author
FROM Publishers, Titles, Authors, [Title Author]
WHERE Titles.ISBN = [Title Author].ISBN AND
Authors.Au_ID = [Title Author].Au_ID AND
Publishers.PubID = Titles.PubID
As a rule, using the WHERE clause to specify equi-joins results in simpler query statements than specifying INNER JOINs. When you need to create OUTER JOINs, the subject of the next section, you may want to use INNER JOIN statements to maintain consistency in Jet SQL statements.
INNER JOINs (equi-joins) return only rows with matching field values. OUTER JOINs return all the rows of one table and only those rows in the other table that have matching values. There are two types of OUTER JOIN:
It is a convention that joins are created in one-to-many form; that is, the primary table that represents the "one" side of the relation appears to the left of the JOIN expression, or the operator of the WHERE clause, and the related table of
the "many" side appears to the right of the expression or operator. You use LEFT OUTER JOINs to display all of the records of the primary table, regardless of matching records in the related table. RIGHT OUTER JOINs are useful for finding orphan
records. Orphan records are records in related tables that have no related records in the primary tables; orphan records result when you violate referential integrity rules.
The SQL-92 syntax for a statement that returns all Titles records, regardless of matching values in the Authors table is as follows:
SELECT Titles.Title, [Title Author].ISBN, [Title Author].Au_ID
FROM Titles LEFT OUTER JOIN [Title Author]
ON Titles.ISBN = [Title Author].ISBN
The equivalent join using the WHERE clause is created by the following query:
SELECT Titles.Title, [Title Author].ISBN, [Title Author].Au_ID
FROM Titles
WHERE Titles.ISBN *= [Title Author].ISBN
Jet SQL requires that you use the special syntax described in the preceding section, and it does not permit you to add the OUTER reserved word in the JOIN statement. The Jet SQL equivalent of the previous query example is as follows:
SELECT DISTINCTROW Titles.Title, [Title Author].ISBN,
[Title Author].Au_ID
FROM Titles LEFT JOIN [Title Author]
ON Titles.ISBN = [Title Author].ISBN
The result of running the preceding query against the Biblio.mdb database appears in Figure 5.10. The first record without a corresponding Title Author record is highlighted in the figure. You get 279 rows, instead of 241 rows, when you execute the
LEFT JOIN, because there are 38 Title records with no corresponding records in the Title Author table.
Jet SQL does not support the *= and =* operators in WHERE clauses. You need to use the LEFT JOIN or RIGHT JOIN reserved words to create outer joins when you use the Jet database engine. This restriction does not apply to SQL passthrough queries that you execute on servers that support *= and =* operators, such as Microsoft and Sybase SQL Server.
If you attempt to add the Authors table to the preceding SQL statement, you receive the message Error 3296: Join expression not supported. The Jet 3.0 database engine cannot process with a single query the following SQL statement:
SELECT DISTINCTROW Titles.Title, Titles.ISBN, [Title Author].Au_ID FROM Authors INNER JOIN (Titles LEFT JOIN [Title Author] ON Titles.ISBN = [Title Author].ISBN) ON Authors.Au_ID = [Title Author].Au_ID
You must use two nested queries: the first query creates the LEFT JOIN query result set, and the second query INNER JOINs the Authors table to the Title Author table. Nested queries are one of the subjects of the "Using Nested Queries and Subqueries" section, later in this chapter.
You can create joins using comparison operators other than =, *=, and =*. Joins that are not equi-joins are called theta joins. The most common form of theta joins is the not-equal (theta) join that uses the WHERE table_name.field_name
<> table_name.field_name syntax. The Biblio.mdb database does not contain tables with fields that lend themselves to demonstrating not-equal joins. However, if you have a copy of the NWIND.MDB sample database of Access 2.0 or the Northwind.mdb
database of Access 95, you can execute an Jet SQL query to find records in the Orders table that have a ShipAddress value that differs from the Address value in the Customers field by employing the following query:
SELECT DISTINCTROW Customers.CompanyName, Customers.Address,
Orders.ShipAddress
FROM Customers, Orders,
Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipAddress<>Customers.Address
The preceding query, which uses the field names from Northwind.mdb, results in the query return set that is shown in Figure 5.11.
If you execute the same query without Jet SQL's DISTINCTROW qualifier, you get the same result. However, if you substitute the ANSI SQL DISTINCT qualifier for Jet SQL's DISTINCTROW, the result is distinctly different, as illustrated by Figure 5.12.
The four-row query result set that is shown in Figure 5.12 is created by the following statement, which is the same in Jet SQL and ANSI SQL:
SELECT DISTINCT Customers.CompanyName, Customers.Address,
Orders.ShipAddress
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.ShipAddress <> Customers.Address
Figure 5.12. The effect of applying the DISTINCT qualifier to the query in Figure 5.11.
The DISTINCT qualifier specifies that only rows that have differing values in the fields specified in the SELECT statement be returned by the query. Jet SQL's DISTINCTROW qualifier causes the return set to include each row in which any of the values of
all of the fields in the two tables (not just the fields specified to be displayed by the SELECT statement) differ.
A self-join is a join created between two fields of the same table having similar field data types. The first field is usually the primary key field and the second field of the join is ordinarily a foreign key field that relates to the primary
key field, although this is not a requirement for a self-join. (The preceding may be a requirement to make the result of the self-join meaningful, however.)
When you create a self-join, the RDBMS creates a copy of the original table, then joins the copy to the original table. No tables in Biblioi.mdb offer fields on which you can create a meaningful self-join. The Employees table of Northwind.mdb, however,
includes the ReportsTo field that specifies the EmployeeID of an employee's supervisor. (Many of the field names of the Employees table of Access 2.0's NWIND.MDB sample databases contain spaces, requiring that the field names be enclosed in square
brackets.) The Jet SQL statement to create a self-join on the Employees table to display the name of an employee's supervisor is this:
SELECT Employees.EmployeeID] AS EmpID,
Employees.LastName] & ", " & Employees.FirstName
AS Employee,
Employees.ReportsTo
AS SupID,
EmpCopy.LastName & ", " & EmpCopy.First Name
AS Supervisor
FROM Employees, Employees
AS EmpCopy,
Employees INNER JOIN EmpCopy
ON Employees.ReportsTo = EmpCopy.Employee ID
You create a temporary copy of the table, named EmpCopy, with the FROM. . . Employees AS EmpCopy clause. Each of the field names of the query is aliased with an AS qualifier. The Employee and Supervisor columns are composite columns whose values are
created by combining last names and comma-spaces with the first names. The query result set from the preceding SQL statement appears in Figure 5.13.
Figure 5.13. The query result set of a self-join on the Employees table of Northwind.mdb.
ANSI SQL does not provide a SELF INNER JOIN, but you can create the equivalent by using the ANSI version of the preceding statement. You can substitute a WHERE Employees.[Reports To] = EmpCopy.[Employee ID] clause for the INNER JOIN. . .ON statement.
Self-joins are relatively uncommon, because a table that is normalized to fourth normal form would not include an equivalent of the ReportsTo field. A separate table would relate the EmployeeID values of employees and supervisors. However, creating a separate table to contain information that can be held in single table without ambiguity is generally considered over-normalization. This is the primary reason that most developers stop normalizing tables at the third normal form.
Another application for self-joins is to overcome the problem with creating queries with WHERE criteria that involve data type conversion on fields containing NULL values, as described in the preceding section "Dyadic Arithmetic Operators and
Functions." You can eliminate the error that occurs with the WHERE Zip IS NOT NULL AND Val(Zip) > 12000 criteria using the following statement:
SELECT Publishers.*
FROM Publishers INNER JOIN Publishers As Pub2
ON (Publishers.PubID = Pub2.PubID)
WHERE Val(Publishers.Zip) > 12000
In this statement, you don't need to add the Zip IS NOT NULL statement because the self-join creates a result set that doesn't include the record with the error. Figure 5.14 shows the result of executing the preceding SQL statement. The behavior of the
self-join with NULL value fields is similar to that of the DBGrid when displaying a calculated field, as discussed in the preceding section, "Calculated Query Columns."
Figure 5.14. The query result set of a self-join designed to allow use of the Val(field_name) function with records containing NULL values.
The Jet 3.0 database lets you create persistent QueryDef objects that you can substitute for tables in SQL statements. A QueryDef is said to be persistent, because the QueryDef is stored as a named Document object in an Access database. A QueryDef is
similar to an SQL VIEW created from an SQL statement. Basing a query on a QueryDef object instead of a table is called nesting queries. The Jet database engine treats tables and QueryDef objects as one object class for naming purposes; thus, you
can't have a QueryDef and a table with the same name in a single database. When you execute a query based on a QueryDef, Jet first executes the query defined by the QueryDef object (sometimes called an inner query) to return its result set as
a Recordset object, then Jet executes the primary query (also called an outer query) against the Recordset.
A nested query is the only convenient method of eliminating the Error 3296: Join expression not supported message when combining INNER and OUTER JOINs, as described in the preceding section, "Outer Joins." To use VisData to create a
persistent QueryDef and join the QueryDef's Recordset with the Authors table, follow these steps:
SELECT DISTINCTROW Titles.Title, [Title Author].ISBN,
[Title Author].Au_ID
FROM Titles LEFT JOIN [Title Author]
ON Titles.ISBN = [Title Author].ISBN
SELECT DISTINCTROW qdfAllTitles.Title, qdfAllTitles.ISBN,
Authors.Author
FROM qdfAllTitles LEFT JOIN Authors
ON qdfAllTitles.Au_ID = Authors.Au_ID
Figure 5.16. Executing the outer query runs the inner query first.
Nested queries and subqueries are closely related. Access 2.0 added subquery capability to Jet 2.0. In the preceding example, the QueryDef takes the place of an SQL subquery. You can use subqueries in the field_list of a SELECT statement or as criteria
in WHERE and HAVING clauses of queries. Subqueries are limited to returning a single column and come in the following two flavors:
SELECT field_list
FROM table_list
WHERE field_name [NOT] IN
(SELECT field_name
FROM table_name
WHERE search_criteria)
SELECT field_list
FROM table_list
WHERE field_name {<|<=|=|=>|>} {ANY|SOME|ALL}
(SELECT field_name
FROM table_name
WHERE search_criteria)
The following SQL statement returns records for any product in the Products table of Northwind.mdb that has a unit price equal to or greater than any item in the Order Details table carrying a discount of 25 percent or more:
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM [Order Details]
WHERE Discount >= .25)
The preceding query returns 76 of the 77 records in the Products table (only ProductID 33, Geitost, does not meet the search criterion). If you substitute ALL for ANY, the query returns only one record—Product ID 38, Côte de
Blaye—undoubtedly a French wine of exceptional vintage. The price of Côte de Blaye is equal to or greater than all of the products for which a discount of 25 percent or greater was applicable.
Subqueries can be used in conjunction with the INSERT, UPDATE, and DELETE action queries described in Chapter 7. For additional examples of the use of subqueries, search Visual Basic 4.0's online help for subqueries. Subqueries are useful, but
they are not easy to write correctly. In most cases, a nested query can be used in place of a subquery. The advantage of a nested query is that the inner QueryDef is precompiled by the Jet engine, so you're likely to obtain a performance benefit.
UNION queries, which were introduced with Jet 2.0, let you combine the query result set of two independent queries. The general syntax of the UNION query is this:
{TABLE table_name1|SELECT field_list1 FROM table_list1}
UNION [ALL]
{TABLE table_name2|SELECT field_list2 FROM table_list2}
The number of fields of table_name1 must equal the number of fields of table_name2 or field_list2, but the fields need not be of the same data type. Duplicate rows are not returned unless you add the ALL predicate. Following is an
example of an SQL statement, derived from an example included in the "UNION Operation" help topic, that returns records for Brazilian customers and suppliers:
SELECT CompanyName, City, SupplierID AS ID
FROM Suppliers
WHERE Country = "Brazil"
UNION SELECT CompanyName, City, CustomerID
FROM Customers
WHERE Country = "Brazil"
ORDER BY City
SupplierID is of the Long data type and CustomerID is of the Text data type, as shown in Figure 5.17. The field names of the query result set, including aliases, are based on the field names of the first query. You can add an ORDER BY clause at the end
of the UNION query to specify the sort order of the entire query result set.
The example for the preceding query in the "UNION Operation" help topic contains an extra semicolon at the end of the next-to-last line. If you attempt to execute the sample query as written, you receive an Error 3142: Characters found after SQL statement message.
ANSI SQL includes set functions (called SQL aggregate functions in this book) that act on sets of records. The standard SQL-92 aggregate functions are described in the following list. The field_name argument of the functions can be the
name of a field (with a table_name. specifier, if required) or the all-fields specifier, an asterisk (*). Here's the list:
The SQL aggregate functions can act on persistent tables or virtual tables, such as query result sets. The basic syntax of queries that use the SQL aggregate functions is this:
SELECT FUNCTION(field_name|*) [AS column_alias]
The preceding example returns a single record with the value of the SQL aggregate function you choose. You can test the SQL aggregate functions with Biblio.mdb using the following query:
SELECT COUNT(*) AS Count,
SUM(PubID) AS Total,
AVG(PubID) AS Average,
MIN(PubID) AS Minimum,
MAX(PubID) AS Maximum
FROM Publishers
The result of the preceding aggregate query appears in Figure 5.18.
Databases with significant content usually have tables that contain fields representing classification of objects. The Biblio.mdb database does not have such a classification, but the Products tables of Northwind.mdb classifies into eight different
categories an eclectic assortment of exotic foodstuffs. You use the GROUP BY clause when you want to obtain values of the SQL aggregate functions for each class of an object. The GROUP BY clause creates a virtual table called, not surprisingly, a
grouped table.
The following Jet SQL query counts the number of items in each of the eight food categories included in the CategoryID field of the Products table, and then calculates three total and average values for each of the categories:
SELECT CategoryID AS Category,
COUNT(ProductID) AS Items,
Format(AVG(UnitPrice), "$#,##0.00") AS Avg_UP,
SUM(UnitsInStock) AS Sum_Stock,
SUM(UnitsOnOrder) AS Sum_Ordered
FROM Products
GROUP BY CategoryID
The preceding query uses the VBA Format() function to format the values returned for average unit price (Avg_UP) in conventional monetary format. This feature is not found in ANSI SQL.
The result of the preceding query appears in Figure 5.19.
Figure 5.19. Using GROUP BY with the SQL aggregate functions.
You may want to restrict group (category) membership by a particular criteria. You might think that you could use a WHERE clause to establish the criteria, but WHERE clauses apply to the entire table. The HAVING clause acts like the WHERE clause for
groups. Therefore, if you want to limit the applicability of the SQL aggregate functions to a particular set of a group, you would add the HAVING clause with the IN() operator, as in the following Jet SQL example, which returns only rows for the 1
(Beverages) and 2 (Condiments) categories:
SELECT CategoryID AS Category,
COUNT(ProductID) AS Items,
Format(AVG(UnitPrice), "$#,##0.00") AS Avg_UP,
SUM(UnitsInStock) AS Sum_Stock,
SUM(UnitsOnOrder) AS Sum_Ordered
FROM Products
GROUP BY CategoryID
HAVING CategoryID IN(1, 2)
The preceding sections of this chapter have outlined many of the syntactical differences between Jet SQL and ANSI SQL-92 (plus earlier versions of ANSI SQL such as SQL-86 and SQL-89). Jet SQL does not support ANSI SQL Data Control Language(DCL) for
granting and revoking user permissions for database objects. Both Access 95 and Visual Basic 4.0 let you modify permissions for users and groups by altering the value of the Permission property of a Container or Document object with VBA code. Access 95
establishes permissions through the user interface and includes a Security Wizard; Visual Basic 4.0 requires that you create your own user interface for security administration. Jet SQL does not support Cursor Control Language (CCL). The Jet 3.0 database
engine handles all cursor-related activities.
The sections that follow summarize the differences between the keywords of Jet SQL and the reserved words of ANSI SQL, as well as how Jet SQL deals with the data types defined by ANSI SQL.
ANSI SQL reserved words, by tradition, are set in uppercase type. Reserved words in ANSI SQL may not be used as names of objects, such as tables or fields, or as names of parameters or variables used in SQL statements. This book refers to elements of
Jet SQL syntax as keywords because, with the exception of some Jet SQL functions, Jet SQL keywords are not reserved words in VBA. Although you could use Jet SQL keywords as names of objects, such use is not considered a generally accepted
programming practice (GAPP).
The commonly used ANSI SQL reserved words (including functions) and symbols that do not have a direct equivalent Jet SQL reserved word or symbol are listed in Table 5.3. This table does not include many of the new reserved words added to SQL-89 by
SQL-92, because these reserved words have not yet been implemented in the versions of client/server RDBMS that had been released as commercial products at the time this book was written.
| Reserved Word | Category | Substitute |
| AUTHORIZATION | DCL | Jet SQL does not support DCL |
| BEGIN | TPL | Visual Basic BeginTrans method |
| CHECK | DDL | Not supported in Jet DDL |
| CLOSE | DCL | Jet SQL does not support DCL |
| COMMIT | TPL | Visual Basic CommitTrans method |
| CREATE VIEW | DDL | Not supported in Access DDL |
| CURRENT | CCL | Jet 3.0 handles cursor control |
| CURSOR | CCL | (See preceding) |
| DECLARE | CCL | (See preceding) |
| DROP VIEW | DDL | Not supported in Access DDL |
| FETCH | CCL | Field name of a Recordset object |
| GRANT | DCL | Jet SQL does not support DCL |
| IN subquery | DQL | Use a query against a query Dynaset instead of a subquery |
| POSITION() | DQL | Use InStr() |
| PRIVILEGES | DCL | Jet SQL does not support DCL |
| REVOKE | DCL | (See preceding) |
| ROLLBACK | TPL | Use Visual Basic Rollback method |
| SUBSTRING() | DQL | Use Mid() functions |
| WORK | TPL | Not required by the Visual Basic BeginTrans method |
| *= | DQL | Use LEFT JOIN |
| =* | DQL | Use RIGHT JOIN |
| != (not equal) | DQL | Use the <> for not equal |
| : (variable prefix) | DQL | Use PARAMETERS statement (if needed) |
Table 5.4 lists Jet SQL keywords that are not reserved words in ANSI SQL. Many of the Jet SQL keywords describe data types that you specify by using the DB_ constants described in Chapter 2. Data type conversion to and from ANSI SQL is
discussed shortly.
| Jet SQL | ANSI SQL | Category | Purpose |
| BINARY | No equivalent | DDL | Presently, not an official Access data type (used for SID field in SYSTEM.MDA) |
| BOOLEAN | No equivalent | DDL | Logical field data type (0 or -1 values only) |
| BYTE | No equivalent | DDL | Asc()/Chr() data type, 1-byte integer (tinyint of SQL Server) |
| CURRENCY | No equivalent | DDL | Currency data type |
| DATETIME | No equivalent | DDL | Date/Time field data type (Variant sub-type 7) |
| DISTINCTROW | No equivalent | DQL | Creates an updatable Recordset object |
| DOUBLE | REAL | DDL | Double-precision floating point number |
| IN predicate with crosstab queries | No equivalent | DQL | Defines fixed column headers for crosstab queries |
| LONG | INT[EGER] | DDL | Long integer data type |
| LONGBINARY | No equivalent | DDL | OLE Object field data type |
| LONGTEXT | No equivalent | DDL | Memo field data type |
| (WITH) OWNERACCESS | No equivalent | DQL | Runs queries with |
| (OPTION) | object owner's permissions | ||
| PARAMETERS | No equivalent | DQL | User- or program- entered query parameters (should be avoided in Visual Basic code) |
| PERCENT | No equivalent | DQL | Used with TOP |
| PIVOT | No equivalent | DQL | Used in crosstab queries |
| SHORT | SMALLINT | DDL | Integer data type, 2 bytes |
| SINGLE | FLOAT | DDL | Single-precision real number |
| TEXT | VARCHAR[ACTER] | DDL | Text data type |
| TOP | No equivalent | DQL | TOP n or TOP n PERCENT |
| TRANSFORM | No equivalent | DQL | Specifies a crosstab query |
| ? (LIKE wild card) | _ (wild card) | DQL | Single character with Like |
| * (LIKE wild card) | % (wild card) | DQL | Zero or more characters |
| # (LIKE wild card) | No equivalent | DQL | Single digit, 0 through 9 |
| # (date specifier) | No equivalent | DQL | Enclose date/time values |
| <> (not equal) | != | DQL | Access uses ! as a separator |
Jet SQL provides the four SQL statistical aggregate functions listed in Table 5.5 that are not included in ANSI SQL. These Jet SQL statistical aggregate functions are set in uppercase and lowercase type in the Microsoft Visual Basic
documentation, but are set in uppercase type in this book.
| Access Function | Purpose |
| STDDEV() | Standard deviation of a population sample |
| STDDEVP() | Standard deviation of a population |
| VAR() | Statistical variation of a population sample |
| VARP() | Statistical variation of a population |
Table 5.6 lists the Jet SQL keywords that often appear in uppercase and lowercase, rather than the all uppercase SQL format in the Microsoft documentation and Visual Basic code examples supplied with Visual Basic 4.0. Jet SQL keywords
that also are Visual Basic reserved words appear in bold type.
| Jet SQL and Visual Basic | ANSI SQL and This Book | Jet SQL and Visual Basic | ANSI SQL and This Book |
| And | AND | Max() | MAX() |
| Avg() | AVG() | Min() | MIN() |
| Between | BETWEEN | Not | NOT |
| Count() | COUNT() | Null | NULL |
| Is | IS | Or | OR |
| Like | LIKE | Sum() | SUM() |
Table 5.7 lists the data types specified by ANSI SQL-92 and the equivalent data types of Jet SQL, when equivalent data types exist. Categories of ANSI SQL data types precede the SQL-92 data type identifier.
| ANSI SQL-92 | Jet SQL | Variant Subtype | Comments |
| Exact Numeric | Number | ||
| INTEGER | Long (Integer) | 3 | 2 bytes |
| SMALLINT | Integer | 2 | 4 bytes |
| NUMERIC[(p[, s])] | Not supported | p = precision, | |
| s = scale | |||
| DECIMAL[(p[, s])] | Not supported | p = precision, | |
| s = scale | |||
| Approximate Numeric | Number | ||
| REAL | Double (Precision) | 5 | 8 bytes |
| DOUBLE PRECISION | Not supported | 16 bytes | |
| FLOAT | Single (Precision) | 4 | 4 bytes |
| Character (Text) | Text | ||
| CHARACTER[(n)] | String | 8 | (Text fields |
| are variable | |||
| length) | |||
| CHARACTER VARYING | String8 | ||
| Bit Strings | None supported | ||
| BIT[(n)] | Not supported | (Binary fields | |
| are variable | |||
| length) | |||
| BIT VARYING | Not supported | (Used by | |
| Microsoft) | |||
| Datetimes | |||
| DATE | Not supported | 10 bytes | |
| TIME | Not supported | 8 bytes (plus | |
| fraction) | |||
| TIMESTAMP | Date/Time | 7 | 19 bytes |
| TIME WITH TIME ZONE | Not supported | 14 bytes | |
| TIMESTAMP WITH TIME ZONE | Not supported | 25 bytes | |
| Intervals (Datetimes) | None supported |
Many of the data types listed in the Jet SQL column of Table 5.7 as "not supported" are converted by ODBC drivers to standard ODBC data types that are compatible Jet SQL data types. When you use attached database files, data
types are converted by the Jet database engine's ISAM driver for dBASE, FoxPro, Paradox, and Btrieve files. Data type conversion by ODBC and ISAM drivers is one of the subjects of the next chapter.
It is impossible to fully describe all the reserved words and syntax of Structured Query Language in a single chapter, especially when the chapter also must compare a particular dialect of SQL, such as Jet SQL, with a "standard" version of
the language. This is particularly true when the standard language is relatively new, as is the case for SQL-92, and there remain few RDBMSs that support more than a fraction of the reserved words added to SQL-89 by the new standard. For a full exposition
of SQL-92, you need a reference guide, such as Jim Melton's and Alan R. Simon's Understanding the New SQL: A Complete Guide, which is listed in the section "A Visual Basic and Database Bibliography," in this book's introduction.
This chapter introduced newcomers to SQL, first to the ANSI variety, then to the Access dialect. ANSI SQL (as implemented by the Microsoft ODBC API) is required to use the SQL passthrough technique, which enables you to process queries on the back-end
server of a client/server database in the RDBMS's native dialect of SQL. To use the Jet 3.0 database engine to process queries, you also need to know the Access dialect of SQL. There are many examples of both ANSI SQL and Access queries presented in this
book, so you've just started on the path to fluency in using SQL with Visual Basic database applications. The next chapter delves into the innards of the Jet 3.0 database engine and its relationship with other desktop database file formats. The last
chapter of this section, "Running Crosstab and Action Queries," expands your SQL vocabulary to Jet SQL's crosstab query syntax and shows you how to write SQL statements that modify the data in database tables.
