Wiki Home

Select SQL


Namespace: WIN_COM_API
Retrieves data from one or more tables. Visual FoxPro Syntax:
SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]]
  [Alias.] Select_Item [AS Column_Name]
  [, [Alias.] Select_Item [AS Column_Name] ...]
FROM [FORCE]
[DatabaseName!]Table [[AS] Local_Alias]
  [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN
     DatabaseName!]Table [[AS] Local_Alias]
     [ON JoinCondition ]
[[INTO Destination]
  | [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT]
  | TO SCREEN]]
[PREFERENCE PreferenceName]
[NOCONSOLE]
[PLAIN]
[NOWAIT]
[WHERE JoinCondition [AND JoinCondition ...]
  [AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

We've found good performance gains, especially over a network, if you place INNER JOIN clauses before the LEFT, RIGHT, and FULL JOIN clauses.-- Steven Black

I'd like more information on joins. For instance...

I'm sure that there are as many different answers to the questions above as there are senarios we could come up with, but I'm looking for good rules-of-thumb to go by while optimizing my SELECT statements. Thanks.


Does anyone know if a subquery that is used as part of a FilterCondition is re-executed for each record that the where clause is evaluating? Or is the subquery executed once then used for each record being evaluated in the where clause?
This depends on the subquery. If it's self-contained, that is, if it can be executed on its own without the outer query, then it's run only once. If the subquery references one or more fields from the outer query (the term for this is a "correlated subquery," then it's executed for each record in the outer query. You can generally rewrite correlated subqueries to be uncorrelated.-- Tamar Granor

Note, however, that VFP (as of version 9, SP1) does *not* Rushmore-optimize the matching of at least types of subquery filters, like "IN". In other words, on a large database, this will be VERY slow, no matter what indexes you have, because it will have to load the entire items table (note that it is not the subquery which is not optimized, but the *match* to the subquery -- invoice_cursor can be EMPTY in the following example, and it will still be just as slow):

SELECT items.* ;
  FROM items ;
  WHERE items.invoice_fk IN (SELECT invoice_pk FROM invoice_cursor)


In my humble opinion, this makes this "feature" worse than useless within the VFP data engine for anything that's not a small temporary cursor, because you might be tempted to use it if you didn't know how terrible a hit to your performance you were about to get. -- Peter Crabtree

Note: SELECT SQL works even if the file is locked.-- Steven Black
VFP has a limit of 9 JOINs. To reduce the number of joins, consider using UserDefinedFunctions for some lookups instead of using JOINs.-- Steven Black VFP 9 removed this limit, although there may be other limits on compexity. Craig Roberts
User Defined Functions in SQL Selects: a UDF in a WHERE clause will be executed for every line item. In a HAVING clause it will be executed for every group. In either case it may cause VFP to follow an inefficient procedure because the value is not known when VFP decides how it's going to execute the query. An easy mistake to make is to use a function that returns a constant in a where or having clause. For example "WHERE MyTable.MyDate= DateToMyDate(Date())" James Beerbower

What about a UDF in the Select Alias/Select.Item
eg
Field Field_Len
Customer.cName 15
Customer.cSurname 30
SQL starts with
SELECT concatenate(Customer.cName,Customer.cSurname) AS Customer_FullName etc where the UDF 'concatenate' is
Function Concatenate(cStr1,cStr2,cStr3)
Do Case
	Case pcount() = 2
		RETURN alltrim(cStr1) + " " + alltrim(cStr2)
	Case pcount() = 3
		RETURN alltrim(cStr1) + " " + alltrim(cStr2) + " " + alltrim(cStr3)
	Otherwise
		RETURN ""
EndCase		
ENDFUNC

In my case, the SQL results truncate the Customer_FullName field to a length of 12, however if you change the SQL start to
SELECT alltrim(Customer.cName) + " " + alltrim(Customer.cSurname) AS Customer_FullName etc, it works as expected.... Why? -- Peter Easson
Because SQL SELECT determines column widths based on the first record returned. In this case, the concatenate function returns variable width results depending on the length of the field contents-- Steven Black.

Thanks Steven,
but don't both ways evaluate to the same length for the first record? -- Peter Easson

Apparently not. One cannot determine which record is returned first. It has to do with which indexes (permanent or temporary) the VFP engine uses in its selection plan.-- Steven Black

- Try to add a padr() to the return of your function, or add it in the select...

SELECT padr(alltrim(Customer.cName) + " " + alltrim(Customer.cSurname),80) AS Customer_FullName

or

SELECT padr(concatenate(Customer.cName,Customer.cSurname),80) AS Customer_FullName

Sometimes when selecting many records, the user reconsiders and wants to stop the SELECT in its tracks. We have been including a variable at the beginning of the WHERE clause that is set .T. until the user cancels, then it is set .F. and all subsequent records relatively quickly fail the WHERE. This still produces a result cursor, and the SELECT still processes all remaining records. Does anyone know of a way to immediately stop processing the SELECT? It doesn't matter if it returns a result or not if the user cancels. Thanks. Christopher Olson

Does Esc not do this for you? -- Cindy Winegarden

It does.. provided that you issue SET ESCAPE ON before the SELECT and disable it afterwards (you should generally have it OFF during app execution). While it is ON, it's also convenient to set an ON ESCAPE clause to avoid the awkward messagebox (Cancel, Retry..) -- Jose Marcenaro

- So, if you have a class or function that runs your selects, it can set escape on, you also might want to set talk on to get a progress bar. Afterward it will set talk and escape back off. This way you don't have to write that code again. I think this is called, code reuse.

Is there a difference between those two SELECT commands?

SELECT 1:
SELECT h.cHeader, d.cDetail ;
FROM Headers h, Details d ;
WHERE h.nPrimaryKey = d.nPrimaryKey


SELECT 2:
SELECT h.cHeader, d.cDetail ;
FROM Headers h INNER JOIN Details d ;
ON h.nPrimaryKey = d.nPrimaryKey


No, they produce the same results. The second is better form, IMO, though. It also makes it easier to change to an outer join if that should be needed.--TamarGranor
Sql Parser

Category VFP Commands
( Topic last updated: 2007.10.15 05:41:43 PM )