Select (SQL) - Query Evaluation ANSI

Query Evaluation ANSI

The processing of a SELECT statement according to ANSI SQL would be the following:

1. SELECT g.* FROM users u INNER JOIN groups g ON g.Userid = u.Userid WHERE u.LastName = 'Smith' AND u.FirstName = 'John'
2. the FROM clause is evaluated, a cross join or Cartesian product is produced for the first two tables in the FROM clause resulting in a virtual table as Vtable1
3. the ON clause is evaluated for vtable1; only records which meet the join condition g.Userid = u.Userid are inserted into Vtable2
4. If an outer join is specified, records which were dropped from vTable2 are added into VTable 3, for instance if the above query were: SELECT u.* FROM users u LEFT JOIN groups g ON g.Userid = u.Userid WHERE u.LastName = 'Smith' AND u.FirstName = 'John' all users who did not belong to any groups would be added back into Vtable3
5. the WHERE clause is evaluated, in this case only group information for user John Smith would be added to vTable4
6. the GROUP BY is evaluated; if the above query were: SELECT g.GroupName, COUNT(g.*) AS NumberOfMembers FROM users u INNER JOIN groups g ON g.Userid = u.Userid GROUP BY GroupName vTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName
7. the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example: SELECT g.GroupName, COUNT(g.*) AS NumberOfMembers FROM users u INNER JOIN groups g ON g.Userid = u.Userid GROUP BY GroupName HAVING COUNT(g.*) > 5
8. the SELECT list is evaluated and returned as Vtable 7
9. the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8
10. the ORDER BY clause is evaluated, ordering the rows and returning VCursor9. This is a cursor and not a table because ANSI defines a cursor as an ordered set of rows (not relational).