Previous: , Up: Table Representation   [Contents]


3.2 Tables vs SQL

Here’s a table (ha!) of what SQL and relational algebar operations correspond to what Octave table operations.

In this table, t is a variable holding a table array, and ix is some indexing expression.

SQLRelationalOctave table
SELECTPROJECTsubsetvars, t(:,ix)
WHERERESTRICTsubsetrows, t(ix,:)
INNER JOINJOINinnerjoin
OUTER JOINOUTER JOINouterjoin
FROM table1, table2, …Cartesian productcartesian
GROUP BYSUMMARIZEgroupby
DISTINCT(automatic)unique(t)

Note that there is one big difference between relational algebra and SQL & Octave table: Relations in relational algebra are sets, not lists. There are no duplicate rows in relational algebra, and there is no ordering. So every operation there does an implicit DISTINCT/unique() on its results, and there‘s no ORDER BY/sort(). This is not the case in SQL or Octave table.

Note for users coming from Matlab: Matlab does not provide a general groupby function. Instead, you have to variously use rowfun, grpstats, groupsummary, and manual code to accomplish “group by” operations.

Note: I wrote this based on my understanding of relational algebra from reading C. J. Date books. Other people’s understanding and terminology may differ. - apjanke