Can a table have no columns?

re: Can a table have no columns?

Robert L Davis — Mon, 04 Feb 2013 08:32:46 GMT

What about manually editing the system tables in SQL 2000 or earlier. Could you make that work? I've seen some pretty impossible things happen because people were messing around with updating the system tables manually.

re: Can a table have no columns?

Martijn Evers — Mon, 04 Feb 2013 08:50:29 GMT

C. J. Date calls these relations TABLE_DUM and TABLE_DEE. TABLE_DUM has zero tuples of degree zero, TABLE_DEE has one tuple of degree zero.

The reason these are important is to provide some identity relations with respect to join. Joining any table to TABLE_DEE returns the original table. Joining any table to TABLE_DUM returns TABLE_DUM. You can think of this as analogous to multiplying by 1 or 0.

Unfortunately, standard SQL doesn't account for tuples of degree zero. So while it makes sense that relational theory includes these relations for completeness, SQL as written doesn't.

For a good read on the fundamental nature of tables with no columns look at C.J. Dates work talking about Table Dee and Table Dum.

See http://flylib.com/books/en/2.214.1.38/1/ for a description on those tables.

re: Can a table have no columns?

Greg Low — Mon, 04 Feb 2013 09:27:23 GMT

Hi Martijn, I have that book and just now have re-read the sections on it.

I get his justification for them (mathematically) in relational theory. JOINs to them are like multiplying by 0 or 1.

I don't, however, get the point of doing it with tables. Do you have a concrete example of how they could possibly be useful if, in fact, SQL Server did support them?

Regards,

Greg

re: Can a table have no columns?

Martijn Evers — Mon, 04 Feb 2013 10:58:51 GMT

Part of the issue here is SQL itself. TABLE_DEE and TABLE_DUM are quite useful in tutorial D.(They either get used to returned by operations) for example as switch

There are several situations where I would prefer them even in plain SQL.

1. Instead of writing SELECT * FROM MY_TABLE WHERE 1=0

we write SELECT * FROM MY_TABLE,TABLE_DUM. The query engine should understand and optimize this better from a relational standpoint (no optimizer tricks needed).

2. testing for existence rows: If exist(select * FROM TABLE_1) then... can be replaced by

IF TABLE_1 UNION TABLE_DEE(=TRUE) THEN (UNION= relational AND and TABLE_DEE = relational TRUE.

(syntax is not helping here so TABLE_TRUE and TABLE_FALSE would be good synonyms).

Note, this is the RELATIONAL if then else, not the programmable one from T-SQL. It is very powerful since we can start doing conditional joining and reusing tables/views based on conditions.

Another way of looking at it is: don't use bits or Boolean scalar data types at all, but start using relations instead

Use them as database wide flags encoded with tables/relations:

CREATE TABLE DATABASE_DEPLOYED{}

this is now equivalent to table_dum so FALSE, meaning database is not deployed

INSERT VALUES() INTO DATABASE_DEPLOYED

This is an empty tuple so DATABASE_DEPLOYED is now equivalent to TABLE_DEE

Note, a flag just denotes true or false, but since a record/tuple also denotes a true proposition the table CREATE DATABASE_DEPLOYED(Boolean deployed PK) would denote DATABASE_DEPLOYED "is true is true" which is superfluous and actually erroneous, because what would the statement "is true is false" mean?

For SQL these tables would allow for cleaner SQL with less "if then else", allow for case statements to collapse result-sets etc. But to be really useful I think some extension on T-SQL would also be in order, else it will be window dressing. (Some will argue not even to bother with SQL at all since it is not relational to begin with).

A good candidate that we can implement with dee and dum would be a relational IF statement.

re: Can a table have no columns?

Martijn Evers — Mon, 04 Feb 2013 20:55:34 GMT

@Greg, Did my follow up post disappear?

re: Can a table have no columns?

Greg Low — Mon, 04 Feb 2013 23:35:54 GMT

Sorry Martijn, which post? (There is one where you give examples)

re: Can a table have no columns?

Martijn Evers — Tue, 05 Feb 2013 10:08:17 GMT

@Greg,

Ah, Now I see my first follow up post. I was afraid it got dropped.

To Reiterate,

TABLE_DEE AND TABLE_DUM are tables encdoding TRUE and FALSE. Relational operators can use this (like EXCEPT,INTERSECTION,UNION, Relational IF, Relational EQUAL). With SELECT, MERGE and other typical operational SQL statements we sometimes can use them as well, but there is no consistent usage pattern AFAIK, just a list of handy tricks and query engine optimizations.

For creating base tables it does make sense, but we certainly can design around the limitation of not having table_dee and table_dum.

If we ever get some additional serious relational operators (EQUAL, IF) I would definitely want to see table_dee and table_dum.