SQL Queries¶

Various custom functions allow SQL queries to be executed while importing data:

Knowing which table name to use¶

When performing SQL queries against a SQL database (as in sqlToTable), you would use the names of the tables as they appear in that database.

When using SQL queries with a single table (as in csvToTable, excelTable, etc.), the table name will always be tbl, regardless of where the data comes from. For example:

select a, b from tbl
select * from tbl where c = 5

This makes it easy to write queries that work regardless of the source file name and source table name.

Shorthand queries¶

When the query starts with WHERE or ORDER BY, the correct table name will automatically be prepended.

For example:

where c = 5 will automatically be converted to select * from tbl where c = 5
order by c will automatically be converted to select * from tbl order by c
where c = 5 order by c will automatically be converted to select * from tbl where c = 5 order by c

When using sqlToTable, you have to specify the full query.

Duplicate column names¶

Some database engines allow queries which result in duplicate column names, like SELECT a AS z, b AS z FROM table. The rules described on the page about column names apply to these cases.

Query syntax¶

There are many different SQL dialects in use today, each with its own features, misfeatures, and quirks. For most simple SELECT statements, there is little difference between them.

When querying an external database using sqlToTable, use the dialect of the database engine you are querying.

For all other SQL queries, use the DuckDB dialect, which is based on the PostgreSQL dialect.

SQLite query syntax¶

The SQLite dialect has a few quirks, some of which are even admitted to be misfeatures in its own documentation.

Note

Autory 2 primarily used the SQLite dialect for its internal queries. Relying on quirks of the SQLite dialect will result in warnings or errors in Autory 3.

The most notable issue with the SQLite dialect to be aware of is its acceptance of double-quoted string literals.

❌ Accepted in SQLite, but wrong in Autory and in most other SQL dialects:
```
SELECT * FROM "tbl" WHERE "name" = "John Doe"
```

✅ Correct in Autory and in most other SQL dialects:

SELECT * FROM "tbl" WHERE "name" = 'John Doe'

Note that double-quotes around identifiers (i.e., table names and column names) are correct, but usually optional.

The BcSqliteDoubleQuotedStringLiterals run control option determines whether double-quoted string literals are accepted (with a warning) or rejected.