Understanding the CockroachDB Subquery
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