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.
SQL | Relational | Octave table |
---|---|---|
SELECT | PROJECT | subsetvars , t(:,ix) |
WHERE | RESTRICT | subsetrows , t(ix,:) |
INNER JOIN | JOIN | innerjoin |
OUTER JOIN | OUTER JOIN | outerjoin |
FROM table1, table2, … | Cartesian product | cartesian |
GROUP BY | SUMMARIZE | groupby |
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