Sat Mar 3 09:39:33 EST 2012

Understanding SQL

I still don't quite get it..  Especially not the single value vs table
stuff.  Representing tables as lists, in Haskell syntax:

        f :  (a,b,c)   -> (d,e)
        g :  [(a,b,c)] -> [(e,e)]
        h :  [(a,b,c)] -> (d,e)

Here g = map f, where this mapping seems to happen automatically in
SQL depending on context?  And h is a projection from a table to a
single record, something like COUNT or EXISTS.  COUNT is called an
aggregate function.

First find of the day: SELECT without FROM returns a 1 element table:

  mysql> SELECT 123;
  | 123 |
  | 123 |
  1 row in set (0.00 sec)

which is enough to pass a value to a function that expects a table.
This is useful for testing out predicates, i.e.:

  SELECT ("lalala" IN (select (val) from foo));
  SELECT (EXISTS (select (val) from foo where val = "lalala"));

"SELECT ... FROM <ref>" requires a table reference.  It's possible to
abstract queries as tables (views).  How to add a query directly?  In
SqlServer this requires an alias[1].  Also works in Mysql:

  SELECT * from (SELECT * from foo) alias1;

How to save the result of a query as a table?  This is SELECT
... INTO, but that's not standard.  Mysql supports INSERT INTO[2]
which requires an existing table.


[1] http://bytes.com/topic/sql-server/answers/142990-select-subquery
[2] http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html