SQL to ReQL cheat sheet
SQL to ReQL cheat sheet
Terminology
SQL and RethinkDB share very similar terminology. Below is a table of terms and concepts in the two systems.
SQL | RethinkDB |
---|---|
database | database |
table | table |
row | document |
column | field |
table joins | table joins |
primary key | primary key (by default id ) |
index | index |
INSERT
This is a list of queries for inserting data into a database.
SQL | ReQL |
---|---|
INSERT INTO users(user_id, age, name) VALUES ("f62255a8259f", 30, Peter) | r.table("users").insert({ "user_id": "f62255a8259f", "age": 30, "name": "Peter" }) |
SELECT
This is a list of queries for selecting data out of a database.
SQL | ReQL |
---|---|
SELECT * FROM users |
r.table("users") |
SELECT user_id, name FROM users |
r.table("users") .pluck("user_id", "name") |
SELECT * FROM users WHERE name = "Peter" |
r.table("users").filter({ "name": "Peter" }) If you have a secondary index built on the field r.table("users") .get_all("Peter", index="name") |
SELECT * FROM users WHERE name = "Peter" AND age = 30 |
r.table("users").filter({ "name": "Peter", "age": 30 }) |
SELECT * FROM users WHERE name LIKE "P%" |
r.table("users").filter( r.row['name'].match("^P") ) |
SELECT * FROM users ORDER BY name ASC |
r.table("users").order_by("name") |
SELECT * FROM users ORDER BY name DESC |
r.table("users").order_by( r.desc("name") ) |
SELECT user_id FROM users WHERE name = "Peter" ORDER BY name DESC |
r.table("users").filter({ "name": "Peter" }).order_by( r.desc("name") ).pluck("user_id") |
SELECT * FROM users LIMIT 5 SKIP 10 |
r.table("users").skip(10).limit(5) |
SELECT * FROM users WHERE name IN ('Peter', 'John') |
r.table("users").filter(lambda doc: r.expr(["Peter", "John"]) .contains(doc["name"]) ) If you have a secondary index built on the field r.table("users") .get_all("Peter", "John", index="name") |
SELECT * FROM users WHERE name NOT IN ('Peter', 'John') |
r.table("users").filter(lambda doc: r.expr(["Peter", "John"]) .contains(doc["name"]) .not_() ) |
SELECT COUNT(*) FROM users |
r.table("users").count() |
SELECT COUNT(name) FROM users WHERE age > 18 |
r.table("users").filter( (r.row.has_fields("name")) & (r.row["age"] > 18) ).count() |
SELECT AVG("age") FROM users |
r.table("users") .avg("age") |
SELECT MAX("age") FROM users |
r.table("users")["age"] .max() |
SELECT DISTINCT(name) FROM users |
r.table("users").pluck("name").distinct() |
SELECT * FROM users WHERE age BETWEEN 18 AND 65; |
r.table("users").filter( (r.row["age"] >= 18) & (r.row["age"] <= 65))If you have a secondary index built on the field age , you can run a more efficient query: r.table("users") .between(18, 65, index="age") |
SELECT name, 'is_adult' = CASE WHEN age>18 THEN 'yes' ELSE 'no' END FROM users |
r.table("users").map({ "name": r.row["name"], "is_adult": r.branch( r.row["age"] > 18, "yes", "no" ) }) |
SELECT * FROM posts WHERE EXISTS (SELECT * FROM users WHERE posts.author_id = users.id) |
r.table("posts") .filter(lambda post: r.table("users") .filter( lambda user: user.id == post.author_id ).count() > 0 ) |
UPDATE
This is a list of commands for updating data in the database.
SQL | ReQL |
---|---|
UPDATE users SET age = 18 WHERE age < 18 |
r.table("users").filter( r.row["age"] < 18 ).update({ "age": 18 }) |
UPDATE users SET age = age+1 |
r.table("users").update( { "age": r.row["age"]+1 } ) |
DELETE
This is a list of queries for deleting data from the database.
SQL | ReQL |
---|---|
DELETE FROM users |
r.table("users").delete() |
DELETE FROM users WHERE age < 18 |
r.table("users") .filter( r.row["age"] < 18) .delete() |
JOINS
This is a list of queries for performing joins between multiple tables.
SQL | ReQL |
---|---|
SELECT * FROM posts JOIN users ON posts.user_id = users.id |
r.table("posts").inner_join( r.table("users"), lambda post, user: post["user_id"] == user["id"] ).zip() Note: If you have an index (primary key or secondary index) built on the field of the right table, you can perform a more efficient join with eq_join. r.table("posts").eq_join( "id", r.table("users"), index="id" ).zip() |
SELECT posts.id AS post_id, user.name, users.id AS user_id FROM posts JOIN users ON posts.user_id = users.id SELECT posts.id AS post_id, user.name, users.id AS user_id FROM posts INNER JOIN users ON posts.user_id = users.id |
r.table("posts").inner_join( r.table("users"), lambda post, user: post["user_id"] == user["id"] ).map({ "post_id": r.row["left"]["id"], "user_id": r.row["right"]["id"], "name": r.row["right"]["name"] }) |
SELECT * FROM posts RIGHT JOIN users ON posts.user_id = users.id SELECT * FROM posts RIGHT OUTER JOIN users ON posts.user_id = users.id |
r.table("posts").outer_join( r.table("users"), lambda post, user: post["user_id"] == user["id"] ).zip() Note: You can perform more efficient r.table("posts").concat_map(lambda post: r.table("users") .get_all(post["id"],index="id") .do( lambda results: r.branch( results.count() == 0, [{"left": post}], results.map( lambda user: { "left": post "right": user } ) ) ) ).zip() |
SELECT * FROM posts LEFT JOIN users ON posts.user_id = users.id SELECT * FROM posts LEFT OUTER JOIN users ON posts.user_id = users.id |
r.table("users").outer_join( r.table("posts"), lambda user, post: post.user_id == user.id ).zip() r.table("users").concat_map(lambda user: r.table("posts") .get_all(user["id"],index="id") .do( lambda results: r.branch( results.count() == 0, [{"left": user}], results.map( lambda post: { "left": user "right": post } ) ) ) ).zip() |
AGGREGATIONS
This is a list of queries for performing data aggregation.
SQL | ReQL |
---|---|
SELECT category FROM posts GROUP BY category |
r.table("posts").map( r.row["category"] ).distinct() |
SELECT category, SUM('num_comments') FROM posts GROUP BY category |
r.table('posts') .group('category') .sum('num_comments') |
SELECT category, status, SUM('num_comments') FROM posts GROUP BY category, status |
r.table("posts") .group('category', 'status') .sum('num_comments') |
SELECT category, SUM(num_comments) FROM posts WHERE num_comments > 7 GROUP BY category |
r.table("posts") .filter(r.row['num_comments']>7) .group('category') .sum('num_comments') |
SELECT category, SUM(num_comments) FROM posts GROUP BY category HAVING num_comments > 7 |
r.table("posts") .group('category') .sum('num_comments') .ungroup() .filter(r.row["reduction"] > 7) |
SELECT title, COUNT(title) FROM movies GROUP BY title HAVING COUNT(title) > 1 |
r.table("movies") .group("title") .count() .ungroup() .filter(r.row["reduction"] > 1) |
TABLE and DATABASE manipulation
This is a list of queries for creating and dropping tables and databases.
SQL | ReQL |
---|---|
CREATE DATABASE my_database; |
r.db_create('my_database') |
DROP DATABASE my_database; |
r.db_drop('my_database') |
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50), age INT); |
r.table_create('users', primary_key="id") Note: RethinkDB is a NoSQL database and does not enforce schemas. Note: The default primary key is |
TRUNCATE TABLE users; |
r.table("users").delete() |
DROP TABLE users; |
r.table_drop("users") |
Read More
Browse the following resources to learn more about ReQL:
© RethinkDB contributors
Licensed under the Creative Commons Attribution-ShareAlike 3.0 Unported License.
https://rethinkdb.com/docs/sql-to-reql/python/