Result of expression
Although it is possible to write any SQL expression with extended subexpressions, the final result returned by the expression must be a value. Or in other words, a single record with single column. It is obvious when you remember, that the result must be presented as a single value in a record of a table.
A common mistake is an expression which usually returns a single value, but under condition can return more than one record. It can be easily solved by adding "LIMIT 1" to the end of the expression.
Syntax, keywords, built-in functions
You may use in the almost every function or a keyword known from MySQL. Some functions are not fully compatible, but we still work on it to minimize the problem. But there are also some differences we want to keep:
JOIN keyword is not supported, but join operations are allowed. For example construction
SELECT COUNT(1) FROM `A`, `B`is allowed. Moreover InstaDB provides arrow (
->) syntax, which can replace and simplify many join constructions. Arrow syntax is described below.
*) is banned, as there is no need for using it, when the final result of expression must be a single value.
It is required to type fully qualified column names
in the expressions. It means, that instead of
SELECT column FROM table you
have to write
SELECT `table`.`column` FROM
As you probably noticed, column and table names in
the above example are surrounded by grave accent
`). Unless you use only ASCII
alphanumerical characters, these marks are not
required, but InstaDB will surround the names by
grave accents anyway when the expression is
saved. It is necessary, because column and table
names may contain any unicode characters, including
Note that you don't need to type
characters by hand. Editor has a feature which helps
to enter correct table and column names. It is
Selects and non-select expressions
Except standard "select" expression other form of
expressions are allowed in InstaDB. Particularly you
may write mathematical expressions without
SELECT syntax. For example you
2 + 2.
It is also possible to use values from columns of the same table:
`table`.`a` + `table`.`b`
or from other tables:
`table_a`.`reference_x` -> `table_x`.`value_n` +
The above expression uses arrow syntax described in details further.
The expression editor makes the correct typing of the tables and columns names a lot easier thanks to the panel at the right side of the expression text area.
This panel represents the current structure of the database tables. Except the PARAM section described further, every position on the list represents a single table. All of them are folded (except the one you are currently edit), but you can click the mark to unfold them and see the list of columns. Note that the expression columns are also listed, as you can use them the same way as value columns.
When you click a any table name or any column, the fully qualified and grave accents surrounded name will appear in the expression text area. Using this feature make assure that no typo is be introduced.
At the top of the table structure panel a PARAM section is located. It contains session specific variables. Currently there are two of them:
PARAM.user_id is a user ID unique for the InstaDB server; i.e. it will be the same for a user in all of his/her databases;
PARAM.user_login is a user login, usually his/her address e-mail.
These two parameters can be used in expressions which depends on individual permissions (we suggest to use PARAM.user_login). The following idiom is frequently used in many expressions in different circumstances:
SELECT `Users`.internal_id FROM `Users` WHERE
`Users`.`E-mail` = PARAM.user_login
The arrow syntax (
Let's say you have a database with two tables: Users and Offices. Of course every user is assigned (by a reference column) to one of the offices. Every department has an address.
Your goal is to add an expression column showing the address (let's call it Office address) to a user. You can do it using standard SQL select:
SELECT `Offices`.`Address` FROM `Offices` WHERE
`Offices`.internal_id = `Users`.`Office`
InstaDB provides the arrow syntax (
which can simplify the above expression:
The arrow syntax let you to follow a reference. You can put the arrow expressions in a row to go along the references as deep as you need. It makes very large expression much easier to write and read. Just try to guess what is the result of the following expression:
And now imagine that you have to write it using standard SELECT syntax.
Split the complex expressions
As it was already mentioned above, an expression column may use other expression columns to calculate the result. It is really worth to remember. This feature lets you (in many cases) to split the complex expressions into smaller pieces and store them in the separate expression columns.
It is useful for two main reasons. First of all it increase readability of the code and make it easier to understand and modify. The second reason is a code reuse. Sometimes it is possible to write an expression which can be used independently by two or more different columns.
So our tip is: when editing really big expression, consider splitting it into smaller chunks. It is not always possible, but it can really be helpful.
More expression examples
Check the following page form more examples of InstaDB expression column examples.