Kbase P183823: How many rows are in the result set returned by a SELECT * statement on two tables without a WHERE o
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/9/2011 |
|
Status: Unverified
GOAL:
How many rows are in the result set returned by a SELECT * statement on two tables without a WHERE or JOIN clause?
GOAL:
How many rows will be returned by a SELECT statement on multiple tables without a WHERE or JOIN clause?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
A SELECT on two or more tables without a WHERE or JOIN clause to qualify the relationship between the tables will return the Cartesian product of the tables involved.
This is a result set where each column from each source is combined in every possible combination of rows between each other source.
For example, Given table A with the two one field records:
Jack
Jill
And table B with three one field records:
1
2
3
Then select * from A, B will return 6 = 2 * 3 rows of two field each as follows:
Jack 1
Jack 2
Jack 3
Jill 1
Jill 2
Jill 3
Let us say we have a third table C with one 5 one field columns:
First
Second
Third
Fourth
Fifth
Then the statement: SELECT * FROM A, B, C will return 2 * 3 * 5 = 30 rows of three fields each as follows:
Jack 1 First
Jack 1 Second
Jack 1 Third
Jack 1 Fourth
Jack 1 Fifth
Jack 2 First
Jack 2 Second
Jack 2 Third
Jack 2 Fourth
Jack 2 Fifth
Jack 3 First
Jack 3 Second
Jack 3 Third
Jack 3 Fourth
Jack 3 Fifth
And ditto ( 15 more rows) for Jill.
In general if tables T1, T2, T3?Tn have r1, r2, r3?rn records then the stamen SELECT * FROM T1, T2, T3?,Tn will return r1 * r2 * r3 *? * rn rows.