If you find yourself running a lot of ad-hoc queries that start with SELECT * FROM, you might be interested in this interesting feature of DuckDB.

DuckDB allows us to start queries with the FROM clause. When we do this, DuckDB will return all columns without us needing to specify SELECT *.

Example

Suppose we have an employees table, and we want to select all rows and all columns.

The following query will suffice:

FROM employees;

Result:

+----+---------+-------------+
| id |  name   | department  |
+----+---------+-------------+
| 1  | Alvin   | HR          |
| 2  | Barbara | HR          |
| 3  | Hesham  | Engineering |
| 4  | Cuong   | Engineering |
| 5  | Axel    | Marketing   |
| 6  | Frady   | Marketing   |
| 7  | Blanche | Marketing   |
| 8  | null    | HR          |
+----+---------+-------------+

That did the trick. It returned all rows and all columns.

If you’re familiar with SQL, the above query probably looks weird. In fact, it probably looks plain wrong. In most other RDBMSs, a query like that will result in an error. But not in DuckDB.

The above query is the equivalent of the following:

SELECT * FROM employees;

Result:

+----+---------+-------------+
| id |  name   | department  |
+----+---------+-------------+
| 1  | Alvin   | HR          |
| 2  | Barbara | HR          |
| 3  | Hesham  | Engineering |
| 4  | Cuong   | Engineering |
| 5  | Axel    | Marketing   |
| 6  | Frady   | Marketing   |
| 7  | Blanche | Marketing   |
| 8  | null    | HR          |
+----+---------+-------------+

So, as long as we want all columns returned, we can start any query with FROM. We can omit the SELECT * part. It’s basically a shorthand method for SELECT * FROM.

Adding Clauses, etc

We can use this method even if we’re filtering the query’s rows, such as using a WHERE clause.

For example, we can do this:

FROM employees WHERE department = 'HR';

Result:

+----+---------+------------+
| id |  name   | department |
+----+---------+------------+
| 1  | Alvin   | HR         |
| 2  | Barbara | HR         |
| 8  | null    | HR         |
+----+---------+------------+

We can also use it when joining multiple tables, etc. Basically, starting with FROM simply allows us to omit the SELECT * part from the query.

Important Consideration

The above method can be great for ad-hoc queries, but we should think carefully before implementing them into our code. For example, if the code ever needs to be ported to another DBMS, it could break. Also, queries starting with FROM could reduce query readability and cause confusion among other developers (especially if they’re not aware that DuckDB allows us to omit the SELECT * part from a query).

That said, if you need to write a lot of ad-hoc queries, omitting SELECT * can definitely speed things up.