Everything is a Table
This is the most trivial of tricks, and not even really a trick, but it is fundamental to a thorough understanding of SQL: Everything is a table! When you see a SQL statement like this:
SELECT * FROM person
… you will quickly spot the table person
sitting right there in the FROM
clause. That’s cool, that is a table. But did you realise that the whole statement is also a table? For instance, you can write:
SELECT * FROM ( SELECT * FROM person ) t
And now, you have created what is called a “derived table” – i.e. a nested SELECT
statement in a FROM
clause.
That’s trivial, but if you think of it, quite elegant. You can also create ad-hoc, in-memory tables with the VALUES()
constructor as such, in some databases (e.g. PostgreSQL, SQL Server):
SELECT * FROM ( VALUES(1),(2),(3) ) t(a)
Which simply yields:
a --- 1 2 3
If that clause is not supported, you can revert to derived tables, e.g. in Oracle:
SELECT * FROM ( SELECT 1 AS a FROM DUAL UNION ALL SELECT 2 AS a FROM DUAL UNION ALL SELECT 3 AS a FROM DUAL ) t
Now that you’re seeing that VALUES()
and derived tables are really the same thing, conceptually, let’s review the INSERT
statement, which comes in two flavours:
-- SQL Server, PostgreSQL, some others: INSERT INTO my_table(a) VALUES(1),(2),(3); -- Oracle, many others: INSERT INTO my_table(a) SELECT 1 AS a FROM DUAL UNION ALL SELECT 2 AS a FROM DUAL UNION ALL SELECT 3 AS a FROM DUAL
In SQL everything is a table. When you’re inserting rows into a
table, you’re not really inserting individual rows. You’re really
inserting entire tables. Most people just happen to insert a
single-row-table most of the time, and thus don’t realise what INSERT
really does.
Everything is a table. In PostgreSQL, even functions are tables:
SELECT * FROM substring('abcde', 2, 3)
The above yields:
substring --------- bcd
If you’re programming in Java, you can use the analogy of the Java 8 Stream
API to take this one step further. Consider the following equivalent concepts:
TABLE : Stream<Tuple<..>> SELECT : map() DISTINCT : distinct() JOIN : flatMap() WHERE / HAVING : filter() GROUP BY : collect() ORDER BY : sorted() UNION ALL : concat()
With Java 8, “everything is a Stream” (as soon as you start working
with Streams, at least). No matter how you transform a stream, e.g. with
map()
or filter()
, the resulting type is always a Stream again.
We’ve written an entire article to explain this more deeply, and to compare the Stream API with SQL:
Common SQL Clauses and Their Equivalents in Java 8 Streams
And if you’re looking for “better streams” (i.e. streams with even more SQL semantics), do check out jOOλ, an open source library that brings SQL window functions to Java.
- ۹۵/۰۳/۰۲