This blog post is development in process. Will fill in the details missing details (especially pandas) later. Some of the MATLAB syntax are inaccurate in the sense that it’s just a description that is context dependent (such as column names can be cellstr, char string or linear/logical indices).

From data relationship point of view, relation database (RDMBS), heterogenous data tables (MATLAB’s dataset/table or Python Panda’s Dataframe) are the same thing. But a proper database have to worry about concurrency issues and provide more consistency tools (ACID model).

Heterogenous data tables are almost always column-oriented database (mainly for analyzing data) where MySQL and Postgres are row-store database. You can think of column-store database as Struct of Arrays (SoA) and row-store database as Array of Struct (AoS). Remember locality = performance: in general, you want to put the stuff you frequently want to access together as close to each other as possible.

Mechanics:

Concepts	SQL	MATLAB table	Pandas Dataframe
tables	FROM	(work with T)	(work with df)
columns variables fields	SELECT	T.(field) T(:, cols/varnames)
rows records	WHERE HAVING	T( cond(T), : ) T_grp( cond(T_grp), : )
conditions	NOT IS IN BETWEEN	~ ==, isequal*() ismember() a<=b & b<=c
Inject table to another table	INSERT INTO t2 SELECT vars FROM t1 WHERE rows	T2(end+(1:#rows), vars) = T1(rows, vars) (Doable, throws warning)
Insert record/row	INSERT INTO t (c1, c2, ..) VALUES (v1, v2, ..)	T=[T; {v1, v2, ...}] (Cannot default for unspecified column*)
update records/elements	UPDATE table SET column = content WHERE row_cond	T.(col)(row_cond) = content
New table from selection	SELECT vars INTO t2 FROM t1 WHERE rows	T2 = T1(rows, vars)
clear table	TRUNCATE TABLE t	T( :, : )=[]
delete rows	DELETE FROM t WHERE cond (if WHERE is not specified, it kills all rows one by one with consistency checks. Avoid it and use TRUNCATE TABLE instead)	T( cond, : ) = []

Core database concepts:

Concepts	SQL	MATLAB (table/dataset)	Pandas (Dataframe)
linear index	CREATE INDEX idx ON T (col)	T.idx = (1:size(T,1))'
group index	CREATE UNIQUE INDEX idx ON T (cols)	[~, T.idx] = sortrows(T, cols) (old implementation is grp2idx())
set operations	UNION INTERSET	union() intersect() setdiff(), setxor()
sort	ORDER BY	sortrows()
unique	SELECT DISTINCT	unique()
reduction aggregration	F()	@reductionFunctions
grouping	GROUP BY	Specifying ‘GroupingVariables’ in varfun(), rowfun(), etc.
partitioning	(set partition option in Table Definition)	T1=T(:, {'key', varnames_1}) T2=T(:, {'key', varnames_2})
joins	[type] JOIN	*join(T1, T2, ...)	df.join(df2, …)
cartesian product	CROSS JOIN (misnomer, no keys)	T_cross = [repelem(T1, size(T2,1), 1), repmat(T2, [size(T1,1), 1])]

Formal databases has a Table Definition (Column Properties) that must be specified ahead of time and can be updated in-place later on (think of it as static typing). Heterogenous Data Tables can figure most of that out on the fly depending on context (think of it as dynamic typing). This impacts:

• data type (creation and conversion)
• unspecified entries (NULL).
  Often NaN in MATLAB native types but I extended it by overloading relevant data types with a isnull() function and consistently use the same interface
• default values
• keys (Indices)

SQL features not offered by heterogenous data tables yet:

• column name aliases (AS)
• wildcard over names (*)
• pattern matching (LIKE)

SQL features that are unnatural with heterogeneous data tables’ syntax:

• implicitly filter a table with conditions in another table sharing the same key.
  It’s an implied join(T, T_cond)+filter operation in MATLAB. Often used with ANY, ALL, EXISTS

Fundamentally heterogenous data types expects working with snapshots that doesn’t update often. Therefore they do not offer active checking (callbacks) as in SQL:

• Invariant constraints (CHECK, UNIQUE, NOT NULL, Foreign key).
• Auto Increment
• Virtual (dependent) tables (CREATE VIEW)

Know these database/spreadsheet concepts:

• Tall vs wide tables

Language logistics (not related to database)

Concepts	SQL	MATLAB (table/dataset)	Pandas (Dataframe)
Partial display	MySQL: LIMIT Oracle: FETCH FIRST	T( 1:10, : )	df.head()
Comments	-- or /* … */	% or %{ … %}	# or """ … """
function	CREATE PROCEDURE fcn	function [varargout{:}]=fcn(varargin{:})	def fcn:
case	CASE WHEN THEN ELSE END	switch case end	(no case structure, use dictionary)
Null if no results	IFNULL ( statement )	function X=null_if_empty(T, cond) X=T( cond, : ); if( isempty(X) ) X=NaN;
Replace nulls	ISNULL(col, target_val)	T.col(isnan(T.col)) = target_val T = standardizeMissing( T, ... )