SQL Language Improvements
The SELECT - SQL Command and other SQL commands have been substantially enhanced in Visual FoxPro 9.0. This topic describes the enhancements made to these commands, and new commands that affect SQL performance.
Expanded Capacities
Several SELECT - SQL command limitations have been removed or increased in Visual FoxPro 9.0. The following table lists the areas where limitations have been removed or increased.
| Capacity | Description |
|---|---|
Number of Joins and Subqueries in a SELECT - SQL command |
Visual FoxPro 9.0 removes the limit on the total number of join clauses and subqueries in a SELECT - SQL command. The previous limit was nine. |
Number of UNION clauses in a SELECT - SQL command |
Visual FoxPro 9.0 removes the limit on number of UNION clauses in a SQL SELECT statement. The previous limit was nine. |
Number of tables referenced a SELECT - SQL command |
Visual FoxPro 9.0 removes the limit on the number of tables and aliases referenced in a SQL SELECT statement. The previous limit was 30. |
Number of arguments in an IN( ) clause |
Visual FoxPro 9.0 removes the limit of 24 values in the IN (Value_Set) clause for the WHERE clause. However, the number of values remains subject to the setting of SYS(3055) - FOR and WHERE Clause Complexity. For functionality changes concerning the IN clause, see Changes in Functionality for the Current Release. |
Subquery Enhancements
Visual FoxPro 9.0 provides more flexibility in subqueries. For example, multiple subqueries are now supported. The following describes the enhancements to subqueries in Visual FoxPro 9.0.
Multiple Subqueries
Visual FoxPro 9.0 supports multiple subquery nesting, with correlation allowed to the immediate parent. There is no limit to the nesting depth. In Visual FoxPro 8.0, error 1842 (SQL: Subquery nesting is too deep) was generated when more than one level of subquery nesting occurred.
The following is the general syntax for multiple subqueries.
SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …
Examples
The following example queries, which will generate an error in Visual FoxPro 8.0, are now supported in Visual FoxPro 9.0.
CREATE CURSOR MyCursor (field1 I)
INSERT INTO MyCursor VALUES (0)
CREATE CURSOR MyCursor1 (field1 I)
INSERT INTO MyCursor1 VALUES (1)
CREATE CURSOR MyCursor2 (field1 I)
INSERT INTO MyCursor2 VALUES (2)
SELECT * FROM MyCursor T1 WHERE EXISTS ;
(SELECT * from MyCursor1 T2 WHERE NOT EXISTS ;
(SELECT * FROM MyCursor2 T3))
*** Another multiple subquery nesting example ***
SELECT * FROM table1 WHERE table1.iid IN ;
(SELECT table2.itable1id FROM table2 WHERE table2.iID IN ;
(SELECT table3.itable2id FROM table3 WHERE table3.cValue = "value"))
GROUP BY in a Correlated Subquery
Many queries can be evaluated by executing a subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
Visual FoxPro 8.0 does not allow using GROUP BY in correlated subquery, and generates error 1828 (SQL: Illegal GROUP BY in subquery). Visual FoxPro 9.0 removes this limitation and supports GROUP BY for correlated subqueries allowed to return more than one record.
The following is the general syntax for the GROUP BY clause in a correlated subquery.
SELECT … WHERE … (SELECT … WHERE … GROUP BY …) …
Examples
The following example, which will generate an error in Visual FoxPro 8.0, is now supported in Visual FoxPro 9.0.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor1 VALUES(1,2,3)
CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor2 VALUES(1,2,3)
SELECT * from MyCursor1 T1 WHERE field1;
IN (SELECT MAX(field1) FROM MyCursor2 T2 ;
WHERE T2.field2=T1.FIELD2 GROUP BY field3)
TOP N in a Non-Correlated Subquery
Visual FoxPro 9.0 supports the TOP N clause in a non-correlated subquery. The ORDER BY clause should be present if the TOP N clause is used, and this is the only case where it is allowed in subquery.
The following is the general syntax for the TOP N clause in a non-correlated subquery.
SELECT … WHERE … (SELECT TOP nExpr [PERCENT] … FROM … ORDER BY …) …
Examples
The following example, which will generate an error in Visual FoxPro 8.0, is now supported in Visual FoxPro 9.0.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor1 VALUES(1,2,3)
CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor2 VALUES(1,2,3)
SELECT * FROM MyCursor1 WHERE field1 ;
IN (SELECT TOP 5 field2 FROM MyCursor2 order by field2)
Subqueries in a SELECT List
Visual FoxPro 9.0 allows a subquery as a column or a part of expression in a projection. A subquery in a projection has exactly the same requirements as a subquery used in a comparison operation. If a subquery does not return any records, NULL value is returned.
In Visual FoxPro 8.0, an attempt to use a subquery as a column or a part of expression in a projection would generate error 1810 (SQL: Invalid use of subquery).
The following is the general syntax for a subquery in a SELECT list.
SELECT … (SELECT …) … FROM …
Example
The following example, which will generate an error in Visual FoxPro 8.0, is now supported in Visual FoxPro 9.0.
SELECT T1.field1, (SELECT field2 FROM MyCursor2 T2;
WHERE T2.field1=T1.field1) FROM MyCursor1 T1
Aggregate functions in a SELECT List of a Subquery
In Visual FoxPro 9.0, aggregate functions are now supported in a SELECT list of a subquery compared using the comparison operators <, <=, >, >= followed by ALL, ANY, or SOME. See Considerations for SQL SELECT Statements for more information about aggregate functions.
Example
The following example demonstrates the use of an aggregate function (the COUNT( ) function) in a SELECT list of a subquery.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (FIELD1 i)
INSERT INTO MyCursor VALUES (6)
INSERT INTO MyCursor VALUES (0)
INSERT INTO MyCursor VALUES (1)
INSERT INTO MyCursor VALUES (2)
INSERT INTO MyCursor VALUES (3)
INSERT INTO MyCursor VALUES (4)
INSERT INTO MyCursor VALUES (5)
INSERT INTO MyCursor VALUES (-1)
CREATE CURSOR MyCursor2 (FIELD2 i)
INSERT INTO MyCursor2 VALUES (1)
INSERT INTO MyCursor2 VALUES (2)
INSERT INTO MyCursor2 VALUES (2)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
SELECT * FROM MYCURSOR WHERE field1;
< ALL (SELECT count(*) FROM MyCursor2 GROUP BY field2) ;
INTO CURSOR MyCursor3
BROWSE
Correlated Subqueries Allow Complex Expressions to be Compared with Correlated Field
In Visual FoxPro 8.0, correlated fields can only be referenced in the following forms:
correlated field <comparison> local field
-or-
local field <comparison> correlated field
In Visual FoxPro 9.0. correlated fields support comparison to local expressions, as shown in the following forms:
correlated field <comparison> local expression
-or-
local expression <comparison> correlated field
A local expression must use at least one local field and cannot reference any outer (correlated) field.
Example
In the following example, a local expression (MyCursor2.field2 / 2) is compared to a correlated field (MyCursor.field1).
SELECT * FROM MyCursor ;
WHERE EXISTS(SELECT * FROM MyCursor2 ;
WHERE MyCursor2.field2 / 2 > MyCursor.field1)
Changes for Expressions Compared with Subqueries.
In Visual FoxPro 8.0, the left part of a comparison using the comparison operators [NOT] IN, <, <=, =, ==, <>, !=, >=, >, ALL, ANY, or SOME with a subquery must reference one and only one table from the FROM clause. In case of a comparison with correlated subquery, the table must also be the correlated table.
In Visual FoxPro 9.0, comparisons work in the following ways:
The expression on the left side of an IN comparison must reference at least one table from the FROM clause.
The left part for the conditions =, ==, <>, != followed by ALL, SOME, or ANY must reference at least one table from the FROM clause.
The left part for the condition >, >=, <, <= followed by ALL, SOME, or ANY (SELECT TOP…) must reference at least one table from the FROM clause.
The left part for the condition >, >=, <, <= followed by ALL, SOME, or ANY (SELECT <aggregate function>…) must reference at least one table from the FROM clause.
The left part for the condition >, >=, <, <= followed by ALL, SOME, or ANY (subquery with GROUP BY and/or HAVING) must reference at least one table from the FROM clause.
In Visual FoxPro 9.0, the left part of a comparison that does not come from the list (for example, ALL, SOME, or ANY are not included) doesn't have to reference any table from the FROM clause.
In all cases, the left part of the comparison is allowed to reference more than one table from the FROM clause. For a correlated subquery, the left part of the comparison does not have to reference the correlated table.
Subquery in an UPDATE - SQL Command SET List
In Visual FoxPro 9.0, the UPDATE - SQL Command now supports a subquery in the SET clause.
A subquery in a SET clause has exactly the same requirements as a subquery used in a comparison operation. If the subquery does not return any records, the NULL value is returned.
Only one subquery is allowed in a SET clause. If there is a subquery in the SET clause, subqueries in the WHERE clause are not allowed.
The following is the general syntax for a subquery in the SET clause.
UPDATE … SET … (SELECT …) …
Example
The following example demonstrates the use of a subquery in the SET clause.
CLOSE DATA
CREATE CURSOR MyCursor1 (field1 I , field2 I NULL)
INSERT INTO MyCursor1 VALUES (1,1)
INSERT INTO MyCursor1 VALUES (2,2)
INSERT INTO MyCursor1 VALUES (5,5)
INSERT INTO MyCursor1 VALUES (6,6)
INSERT INTO MyCursor1 VALUES (7,7)
INSERT INTO MyCursor1 VALUES (8,8)
INSERT INTO MyCursor1 VALUES (9,9)
CREATE CURSOR MyCursor2 (field1 I , field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
UPDATE MyCursor1 SET field2=100+(SELECT field2 FROM MyCursor2 ;
WHERE MyCursor2.field1=MyCursor1.field1) WHERE field1>5
SELECT MyCursor1
LIST
Sub-SELECT in the FROM Clause
A sub-SELECT is often referred to as a derived table. Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause creates a table used by the outer SELECT statement. Visual FoxPro 9.0 permits the use of a subquery in the FROM clause.
A sub-SELECT should be enclosed in parentheses and an alias is required. Correlation is not supported. A sub-SELECT has the same syntax limitations as the SELECT command, but not the subquery syntax limitations. All sub-SELECTs are executed before the top most SELECT is evaluated.
The following is the general syntax for a subquery in the FROM clause.
SELECT … FROM (SELECT …) [AS] Alias…
Example
The following example demonstrates the use of a subquery in the FROM clause.
SELECT * FROM (SELECT * FROM MyCursor T1;
WHERE field1 = (SELECT T2.field2 FROM MyCursor1 T2;
WHERE T2.field1=T1.field2);
UNION SELECT * FROM MyCursor2;
ORDER BY 2 desc) AS subquery
** Note that the following code will generate an error ** SELECT * FROM (SELECT TOP 5 field1 FROM MyCursor) ORDER BY field1
ORDER BY with Field Names in the UNION clause
When using a UNION clause in Visual FoxPro 8.0, you are forced to use numeric references in the ORDER BY clause. In Visual FoxPro 9.0, this restriction has been removed and you can use field names.
The referenced fields must be present in the SELECT list (projection) for the last SELECT in the UNION; that projection is used for ORDER BY operation.
Example
The following example demonstrates the use of a field names in the ORDER BY clause.
CLOSE DATABASES all
CREATE CURSOR MyCursor(field1 I,field2 I)
INSERT INTO MyCursor values(1,6)
INSERT INTO MyCursor values(2,5)
INSERT INTO MyCursor values(3,4)
SELECT field1, field2, .T. AS FLAG,1 FROM MyCursor;
WHERE field1=1;
UNION ;
SELECT field1, field2, .T. AS FLAG,1 FROM MyCursor;
WHERE field1=3;
ORDER BY field2 ;
INTO CURSOR TEMP READWRITE
BROWSE NOWAIT
Optimized TOP N Performance
In Visual FoxPro 8.0 and earlier versions, when using the TOP N [PERCENT] clause all records are sorted and then the TOP N are extracted. In Visual FoxPro 9.0, performance has been improved by eliminating records that do not qualify for the TOP N from the sort process as early as possible.
The TOP N optimization is done only if the SET ENGINEBEHAVIOR Command is set to 90.
Optimization requires that TOP N return no more than N records (this is not the case for Visual FoxPro 8.0 and earlier versions) which is enforced if SET ENGINEBEHAVIOR is set to 90.
TOP N PERCENT cannot be optimized unless the whole result set can be read into memory at once.
Improved Optimization for Multiple Table OR Conditions
Visual FoxPro 9.0 provides for improved Rushmore optimization involving multi-table OR conditions. Visual FoxPro uses multi-table OR conditions to Rushmore optimize filter conditions for a table as long as both sides of the condition can be optimized. The following example shows this:
CLEAR
CREATE CURSOR Test1 (f1 I)
FOR i=1 TO 20
INSERT INTO Test1 VALUES (I)
NEXT
INDEX ON f1 TAG f1
CREATE CURSOR Test2 (f2 I)
FOR i=1 TO 20
INSERT INTO Test2 VALUES (I)
NEXT
INDEX ON f2 TAG f2
SYS(3054,12)
SELECT * from Test1, Test2 WHERE (f1 IN (1,2,3) AND f2 IN (17,18,19)) OR ;
(f2 IN (1,2,3) AND f1 IN (17,18,19)) INTO CURSOR Result
SYS(3054,0)
In this scenario, table Test1 can be Rushmore optimized using the following condition:
(f1 IN (1,2,3) OR f1 IN (17,18,19))and table Test2 with the following:
(f2 IN (17,18,19) OR f2 IN (1,2,3))
Support for Local Buffered Data
At times it can be beneficial to use SELECT - SQL to select records from a local buffered cursor in which the table has not been updated. Many times when creating controls like grids, list boxes, and combo boxes it is necessary to consider newly added records which have not yet been committed to disk. Currently, SQL statements are based on content that is already committed to disk.
Visual FoxPro 9.0 provides language enhancements that allow you to specify if the data returned by a SELECT - SQL command is based on buffered data or data written directly to disk.
The SELECT - SQL command now supports a WITH … BUFFERING clause that lets you specify if retrieved data is based on buffered data or data written directly to disk. For more information, see SELECT - SQL Command - WITH Clause.
If you do not include the BUFFERING clause, the retrieved data is then determined by the setting for SET SQLBUFFERING command. For more information, see the SET SQLBUFFERING Command.
Enhancements to other SQL Commands
The following sections describe enhancements made to the INSERT - SQL Command, UPDATE - SQL Command, and DELETE - SQL Command commands in Visual FoxPro 9.0.
UNION Clause in the INSERT - SQL Command
In Visual FoxPro 9.0, a UNION clause is now supported in the INSERT - SQL Command.
The following is the general syntax for the UNION clause.
INSERT INTO … SELECT … FROM … [UNION SELECT … [UNION …]]
Example
The following example demonstrates the use of a UNION clause in INSERT-SQL.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (field1 I,field2 I)
CREATE CURSOR MyCursor1 (field1 I,field2 I)
CREATE CURSOR MyCursor2 (field1 I,field2 I)
INSERT INTO MyCursor1 VALUES (1,1)
INSERT INTO MyCursor2 VALUES (2,2)
INSERT INTO MyCursor SELECT * FROM MyCursor1 UNION SELECT * FROM MyCursor2
SELECT MyCursor
LIST
Correlated UPDATE - SQL Commands
Visual FoxPro 9.0 now supports correlated updates with the UPDATE - SQL Command.
If a FROM clause is included in the UPDATE -SQL command, then the name after UPDATE keyword defines the target for the update operation. This name can be a table name, an alias, or a file name. The following logic is used to select the target table:
If the name matches an implicit or explicit alias for a table in the FROM clause, then the table is used as a target for the update operation.
If the name matches the alias for the cursor in the current data session, then the cursor is used as a target.
A table or file with the same name is used as a target.
The UPDATE -SQL command FROM clause has the same syntax as the FROM clause in the SELECT - SQL command with the following limitations:
The target table or cursor cannot be involved in an OUTER JOIN as a secondary table.
The target cursor cannot be a subquery result.
All other JOINs can be evaluated before joining the target table.
The following is the general syntax for a correlated UPDATE command.
UPDATE … SET … FROM … WHERE …
Example
The following example demonstrates a correlated update using the UPDATE -SQL command.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)
INSERT INTO MyCursor1 VALUES (1,1,0)
INSERT INTO MyCursor1 VALUES (2,2,0)
INSERT INTO MyCursor1 VALUES (5,5,0)
INSERT INTO MyCursor1 VALUES (6,6,0)
INSERT INTO MyCursor1 VALUES (7,7,0)
INSERT INTO MyCursor1 VALUES (8,8,0)
INSERT INTO MyCursor1 VALUES (9,9,0)
CREATE CURSOR MyCursor2 (field1 I , field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
CREATE CURSOR MyCursor3 (field1 I , field2 I)
INSERT INTO MyCursor3 VALUES (6,600)
INSERT INTO MyCursor3 VALUES (7,700)
UPDATE MyCursor1 SET MyCursor1.field2=MyCursor2.field2, field3=MyCursor2.field2*10 FROM MyCursor2 ;
WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1
SELECT MyCursor1
LIST
UPDATE MyCursor1 SET MyCursor1.field2=MyCursor3.field2 FROM MyCursor2, MyCursor3 ;
WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND MyCursor2.field1=MyCursor3.field1
SELECT MyCursor1
LIST
Correlated DELETE - SQL Commands
Visual FoxPro 9.0 now supports correlated deletions with the DELETE - SQL Command.
If a FROM clause has more than one table, the name after the DELETE keyword is required and it defines the target for the delete operation. This name can be a table name, an alias or a file name. The following logic is used to select the target table:
If the name matches an implicit or explicit alias for a table in the FROM clause, then the table is used as a target for the update operation.
If the name matches the alias for the cursor in the current data session, then the cursor is used as a target.
A table or file with the same name is used as a target.
The DELETE -SQL command FROM clause has the same syntax as the FROM clause in the SELECT - SQL command with the following limitations:
The target table or cursor cannot be involved in an OUTER JOIN as a secondary table.
The target cursor cannot be a subquery result.
It should be possible to evaluate all other JOINs before joining the target table.
The following is the general syntax for a correlated DELETE command.
DELETE [alias] FROM alias1 [, alias2 … ] … WHERE …
Example
The following example demonstrates a correlated deletion using the DELETE -SQL command.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)
INSERT INTO MyCursor1 VALUES (1,1,0)
INSERT INTO MyCursor1 VALUES (2,2,0)
INSERT INTO MyCursor1 VALUES (5,5,0)
INSERT INTO MyCursor1 VALUES (6,6,0)
INSERT INTO MyCursor1 VALUES (7,7,0)
INSERT INTO MyCursor1 VALUES (8,8,0)
INSERT INTO MyCursor1 VALUES (9,9,0)
CREATE CURSOR MyCursor2 (field1 I , field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
CREATE CURSOR MyCursor3 (field1 I , field2 I)
INSERT INTO MyCursor3 VALUES (6,600)
INSERT INTO MyCursor3 VALUES (7,700)
DELETE MyCursor1 FROM MyCursor2 ;
WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1
SELECT MyCursor1
LIST
RECALL ALL
DELETE MyCursor1 FROM MyCursor2, MyCursor3 ;
WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND MyCursor2.field1=MyCursor3.field1
SELECT MyCursor1
LIST
RECALL ALL
DELETE FROM MyCursor1 WHERE MyCursor1.field1>5
SELECT MyCursor1
list
RECALL ALL
DELETE MyCursor1 from MyCursor1 WHERE MyCursor1.field1>5
RECALL ALL IN MyCursor1
DELETE T1 ;
FROM MyCursor1 T1 JOIN MyCursor2 ON T1.field1>5 AND MyCursor2.field1=T1.field1, MyCursor3 ;
WHERE MyCursor2.field1=MyCursor3.field1
RECALL ALL IN MyCursor1
Updatable Fields in UPDATE - SQL Command
The number of fields that can be updated with the UPDATE - SQL Command is no longer limited to 128 as in prior versions of Visual FoxPro. You are now limited to 255, which is the number of fields available in a table.
SET ENGINEBEHAVIOR
The SET ENGINEBEHAVIOR Command has a new Visual FoxPro 9.0 option, 90, that affects SELECT - SQL command behavior for the TOP N clause and aggregate functions. For additional information, see the SET ENGINEBEHAVIOR Command.
Data Type Conversion
Conversion between data types (for example, conversion between memo and character fields) has been improved in Visual FoxPro 9.0. This conversion improvement applies to the ALTER TABLE - SQL Command with the COLUMN option as well as structural changes made with the Table Designer.
See Also
Reference
Guide to Reporting Improvements
Data and XML Feature Enhancements
Class Enhancements
Language Enhancements
Interactive Development Environment (IDE) Enhancements
Enhancements to Visual FoxPro Designers
Miscellaneous Enhancements
Changes in Functionality for the Current Release
ALTER TABLE - SQL Command
SET SQLBUFFERING Command
SET ENGINEBEHAVIOR Command