SQL to ReQL cheat sheet
SQL to ReQL cheat sheet
This document has not been updated for Java. The API documentation for Java is complete, but many ReQL articles still have examples in other languages. We’ll be updating each article after the Java driver is officially released.
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({ userId: "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("userId", "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") .getAll("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").orderBy("name") |
SELECT * FROM users ORDER BY name DESC |
r.table("users").orderBy( r.desc("name") ) |
SELECT user_id FROM users WHERE name = "Peter" ORDER BY name DESC |
r.table("users").filter({ name: "Peter" }).orderBy( r.desc("name") ).pluck("userId") |
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( function (doc) { return r.expr(["Peter","John"]) .contains(doc("name")); } ) If you have a secondary index built on the field r.table("users") .getAll("Peter", "John", {index: "name"}) |
SELECT * FROM users WHERE name NOT IN ('Peter', 'John') |
r.table("users").filter( function (doc) { return 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.hasFields("name") .and(r.row("age").gt(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").ge(18) .and(r.row("age").le(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").gt(18), "yes", "no" ) }) |
SELECT * FROM posts WHERE EXISTS (SELECT * FROM users WHERE posts.author_id = users.id) |
r.table("posts") .filter(function (post) { return r.table("users") .filter(function (user) { return user("id").eq(post("authorId")) }).count().gt(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").lt(18) ).update({age: 18}) |
UPDATE users SET age = age+1 |
r.table("users").update( {age: r.row("age").add(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").lt(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").innerJoin( r.table("users"), function (post, user) { return post("userId").eq(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 eqJoin. r.table("posts").eqJoin( "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").innerJoin( r.table("users"), function (post, user) { return post("userId").eq(user("id")); }).map({ postId: r.row("left")("id"), userId: 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").outerJoin( r.table("users"), function (post, user) { return post("userId").eq(user("id")); }).zip() Note: You can perform more efficient r.table("posts").concatMap( function (post) { return r.table("users") .getAll(post("id"), {index: id}) .do( function (result) { return r.branch( result.count().eq(0), [{left: post}], result.map(function (user) { return { 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").outerJoin( r.table("posts"), function (user, post) { return post("userId").eq(user("id")); } ).zip() r.table("users").concatMap( function (user) { return r.table("posts").getAll(user("id"), {index: "id"}).do( function (results) { return r.branch( results.count().eq(0), [{left: user}], results.map(function (post) { return {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').gt(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").gt(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").gt(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.dbCreate('my_database') |
DROP DATABASE my_database; |
r.dbDrop('my_database') |
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50), age INT); |
r.tableCreate('users', {primaryKey: "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.tableDrop("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/java/