YQL

How do I select table rows by a list of keys?

If a selection is made using a composite key, the query parameter must have the type of a list of tuples:

DECLARE $keys AS List<Tuple<UInt64, String>>;
SELECT * FROM some_table
WHERE (Key1, Key2) IN $keys;

To select rows effectively, make sure that the value types in the parameters match the key column types in the table.

Is search by index performed for conditions containing the LIKE operator?

You can only use the LIKE operator to search a table index if it specifies a row prefix:

Why does a query return only 1000 rows?

How do I update only those values whose keys are not in the table?

You can use the LEFT JOIN operator to identify the keys a table is missing and update their values:

DECLARE $values AS List<Struct<Key: UInt64, Value: String>>;
SELECT v.Key AS Key, v.Value AS Value
FROM AS_TABLE($values) AS v
ON v.Key = t.Key
WHERE t.Key IS NULL;

Are there any specific features of Join operations?

A Join in YDB is performed using one of the two methods below:

Common Join.
Index Lookup Join.

Common Join

The contents of both tables (to the left and to the right of Join) are sent to the requesting node which applies the operation to the totality of the data. This is the most generic way of performing a Join that is used whenever other optimizations are unavailable. For large tables, this method is either slow or doesn’t work in general due to exceeding the data transfer limits.

Index Lookup Join

For rows on the left of Join, relevant values are looked up to the right. You use this method whenever the right part is a table and the Join key is its primary or secondary index key prefix. In this method, limited selections are made from the right table instead of full reads. This lets you use it when working with large tables.

Note

How do I Join data from query parameters?

You can use query parameter data as a constant table. To do this, use the modifier with a parameter whose type is a list of structures:

There is no explicit limit on the number of entries in the constant table, but mind the standard limit on the total size of query parameters (50 MB).

What’s the best way to implement a query like (key1, key2) IN ((v1, v2), (v3, v4), …)?

It’s better to write it using a JOIN with a constant table:

$keys = AsList(
    AsStruct(1 AS Key1, "One" AS Key2),
    AsStruct(2 AS Key1, "Three" AS Key2),
    AsStruct(4 AS Key1, "One" AS Key2)
);
SELECT t.* FROM AS_TABLE($keys) AS k
ON t.Key1 = k.Key1 AND t.Key2 = k.Key2;

How efficient is it to run multiple queries in a transaction?

When multiple queries are run sequentially, the total transaction latency may be greater than when the same operations are executed within a single query. This is primarily due to additional network latency for each query. Therefore, if a transaction doesn’t need to be interactive, we recommend formulating all operations in a single YQL query.

YQL

YQL