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...

• Let's say you have a WHERE clause that filters records on the 3rd table of a 5 table select, does the order of the JOIN clauses affect performance? Meaning, should you use the FORCE clause to define the order in which the tables are joined? If so, what would the best approach be to joining those tables.

The VFP engine figures out the order of the joins to be what it considers optimal. It appears to take filters into consideration. Save the FORCE clause for situations where you've already learned the optimal order and you want to avoid the time necessary for VFP to figure it out.-- Tamar Granor



• When using SYS(3054,11) and executing multi-table join select statements, I get alot of "joining intermediate results with intermediate results using temp index" - does this indicate a poorly written select statement? There are many times that I have the ability to add index tags to the underlying tables, but I'm not sure if they'd help.

Any time that two sets of intermediate results are joined, the only possible index is a temp index. So that line doesn't automatically mean that there's something wrong with your query or indexes. However, if speeding such a query up is important to you (because it's too slow for the task at hand or will be executed many times), it's probably worth experimenting with additional tags. -- Tamar Granor


• Does the table you specify in the FROM clause matter? If so, which table should you specify?

As long as the ON clauses match the JOIN clauses properly, the order of tables doesn't matter in an inner join. With an outer join, changing the table order can give you different results. -- Tamar Granor


• When do you want to start splitting up your SELECT because there are too many joins into multiple queries and joining them with a final result query?

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