Understanding the CockroachDB Subquery

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

SQL subqueries allow for using the results of a selection query within, or inside, a larger query. In addition to scalar subqueries, found in a scalar expression, CockroachDB supports relational subqueries that appear as operand in a selection query, or a table expression. This tutorial will explain how to use the CockroachDB subquery by placing the subquery inside of a main query to accomplish various tasks for adding or changing data in a table.

Prerequisites

  • CockroachDB must be properly installed and configure on the local machine in order to use the CockroachDB subquery.

  • A good working knowledge in CockroachDB is needed.

CockroachDB Subquery

The CockroachDB subquery, also referred to as a nested query, is a query placed inside of a main query. Here the main query uses the result values from the subquery query. The subquery can be placed inside of the two notations (...) or [...].

The following is an example of a subquery statement:

1
2
3
4
SELECT * FROM TABLE_NAME
WHERE column_name = (
    SELECT column_name FROM TABLE_NAME
);

CockroachDB Subquery Examples

Before beginning to explain the subquery examples, the following tables must first be created:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR (50) NOT NULL,
price DECIMAL NOT NULL,
quantity INT NOT NULL
);
CREATE TABLE order_products (
op_id SERIAL PRIMARY KEY,
product_id INT NOT NULL REFERENCES products (product_id),
customer_name VARCHAR (50) NOT NULL,
quantity INT NOT NULL
);

Now insert the following records into the tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO products (product_id, product_name, price, quantity)
VALUES ('123', 'Mattress', '79.99', '423'),
('124', 'Folding Table', '54.04', '310'),
('125', 'Coat Rack', '30.53', '368'),
('126', 'Storage Cabinet', '77.61', '465'),
('127', 'Corner Desk', '88.87', '355');
INSERT INTO order_products (product_id, customer_name, quantity)
VALUES ('124', 'Adrianna Dixon', '23'),
('127', 'Cerys Odonnell', '12'),
('123', 'Lesley Mcclure', '1'),
('127', 'Vincent Byrd', '5'),
('123', 'Cruz Hood', '50'),
('125', 'Sherri Morton', '3'),
('126', 'Maureen Farrell', '15'),
('126', 'Ayaz Myers', '1');

The results should resemble the following table named products:

1
2
3
4
5
6
7
8
  product_id |  product_name   | price | quantity
-------------+-----------------+-------+-----------
         123 | Mattress        | 79.99 |      423
         124 | Folding TABLE   | 54.04 |      310
         125 | Coat Rack       | 30.53 |      368
         126 | Storage Cabinet | 77.61 |      465
         127 | Corner Desk     | 88.87 |      355
(5 ROWS)

Here is the second table named order_products:

1
2
3
4
5
6
7
8
9
10
11
        op_id        | product_id |  customer_name  | quantity
---------------------+------------+-----------------+-----------
  537612237604290561 |        124 | Adrianna Dixon  |       23
  537612237604356097 |        127 | Cerys Odonnell  |       12
  537612237604388865 |        123 | Lesley Mcclure  |        1
  537612237604421633 |        127 | Vincent Byrd    |        5
  537612237604454401 |        123 | Cruz Hood       |       50
  537612237604487169 |        125 | Sherri Morton   |        3
  537612237604519937 |        126 | Maureen Farrell |       15
  537612237604552705 |        126 | Ayaz Myers      |        1
(8 ROWS)

The following example demonstrates how to obtain the list of products containing more than 20 orders:

1
2
3
4
5
6
7
8
9
SELECT * FROM products
WHERE product_id IN (
    SELECT product_id FROM order_products WHERE quantity > 20
);
  product_id | product_name  | price | quantity
-------------+---------------+-------+-----------
         124 | Folding TABLE | 54.04 |      310
         123 | Mattress      | 79.99 |      423
(2 ROWS)

Here is a second example that obtains the list of customer names that have placed the highest priced orders:

1
2
3
4
5
6
7
8
9
SELECT customer_name FROM order_products
WHERE product_id = (
    SELECT product_id FROM products ORDER BY price DESC LIMIT 1
);
  customer_name
------------------
  Cerys Odonnell
  Vincent Byrd
(2 ROWS)

Subquery With Select and Insert

A subquery can also aid when inserting a new row into a table. The following example shows how to display the newly inserted records:

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM [
    INSERT INTO products (product_id, product_name, price, quantity)
    VALUES ('128', 'Inflatable chair', '31.79', '416'),
    ('129', 'Nightstands', '67.49', '324')
    RETURNING *
];
  product_id |   product_name   | price | quantity
-------------+------------------+-------+-----------
         128 | Inflatable chair | 31.79 |      416
         129 | Nightstands      | 67.49 |      324
(2 ROWS)

As shown here, the subquery selects the table and displays the inserted values:

1
2
3
4
5
6
7
8
9
10
  product_id |   product_name   | price | quantity
-------------+------------------+-------+-----------
         123 | Mattress         | 79.99 |      423
         124 | Folding TABLE    | 54.04 |      310
         125 | Coat Rack        | 30.53 |      368
         126 | Storage Cabinet  | 77.61 |      465
         127 | Corner Desk      | 88.87 |      355
         128 | Inflatable chair | 31.79 |      416
         129 | Nightstands      | 67.49 |      324
(7 ROWS)

Subquery With Select and Update

The following example shows how a subquery can also be used to update a table and then display the updated records:

1
2
3
4
5
6
7
8
SELECT * FROM [
    UPDATE products SET price = 50 WHERE product_id = 123
    RETURNING *
];
  product_id | product_name | price | quantity
-------------+--------------+-------+-----------
         123 | Mattress     |    50 |      423
(1 ROW)

Conclusion

This tutorial explained how to use the CockroachDB subquery, a query placed inside of a main query. The tutorial first covered how create two example tables and then insert data into the tables. The article then provided working examples that demonstrated how to use a nested query, one to obtain the list of products containing more than 20 orders and a second example that obtained the list of customer names who had placed the highest priced orders. The tutorial then explained how use the CockroachDB subquery to display the inserted records. Finally, the tutorial covered how to execute a Select and Update query to update a table and then display the updated records. Remember that the main query uses the result values from the subquery and the subquery can be placed inside two different notations, either parentheses (…)`or brackets […].

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.