Nested Select in CockroachDB

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

Introduction

In this document, we will explore how to use nested select in CockroachDB SQL. During this lesson, we will use the following structure:

  • What? What do Nested Selects do in Cockroach and what is the syntax?
  • How? How do we best use this clause in our CockroachDB SQL commands?
  • Why? When would we make use of a Nested Select? We’ll learn via using a realistic use case. Includes use of the Cockroach “WHERE” clause. We’ll also include exploration of “INSERT INTO” and “NOT IN”.

Prerequisites

  • A novice-level of knowledge of how to write SQL for Cockroach (or similar relational databases like MS SQL Server, Postgres, and Postgres) using one of the many available tools out there, such as DBeaver, or with server-side script or programming languages like Python, Java, PHP, C#, ASP.Net, VB.Net, Ruby, Node.js, B4X, Classic ASP, etc.) that provide a connection to CockroachDB, as well as a method for sending SQL queries to our database tables, to get data or make changes to your data.
  • Comprehension of the use of commonly used Cockroach SQL statements, including SELECT, FROM, and WHERE statements.

What is Nested Select for Cockroach?

Simply put: Nested Selects can also be called “subqueries”.

Sometimes in our Cockroach-based application we may need to pull data from a subset of data we want to create on the fly, update a table based on a subset of data, or insert to a table based on the same. Later in this document, we will look at some ways and whys of doing this.

First, let us study the syntax of this powerful Cockroach tool, from a few perspectives:

Syntax of Nested select in Where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    colData_01
    , colData_02
    , colData_03
FROM
    tblData
WHERE
    colData_01 IN -- In addition, this can be "NOT IN", "EXISTS, an operator like "=", "<", or others.
    (
    SELECT
        colData_01
    FROM
        tblData
    WHERE
        [SOME condition clause]
    )
ORDER BY colData_01

Analysis:

In the syntax script above, we are examining two data sets that are both pulled from the same Cockroach table, “tblData”. The high level view is that the “inner” nested data set, the one in parentheses, runs first and the “outer” query is filtered based on the results of that inner, nested one returns. The IN you see here is important. It’s telling CockroachDB to return only records from tblData where the value in colData_01 exists in the recordset returned by the nested select query. We’ll later look at a few ways this can be useful.

Syntax of Nested select in From

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    colData_01
    , colData_02
    , colData_03
    , subqueryNested.colData_04
FROM
    (
    SELECT
        colData_04
    FROM
        tblData
    WHERE
        [condition]
    ) AS subqueryNested

Analysis:

In this syntax example, we are giving a name to our “inner” nested select query, “subqueryNested”, so that we can refer to it in our outer select at the top of the overall CockroachDB SQL. Note that again, we are enclosing the nested select query in parenthesis. This is a requirement.

Before we do a realistic business example, we’ll look at the syntax for using nested select statements with INSERT INTO.

Syntax of Nested select in Insert Into

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO
    tblData
    (
    colData_01
    , colData_02
    )
    VALUES
    (
    SELECT
        colData_03
        , colData_04
    FROM
        tblSecondary
    WHERE
        [condition]
    )

Analysis:

In the INSERT INTO above, we start by telling Cockroach to add rows to “tblData”. Next, we are determining which columns (colData_01 and colData_02) we want to fill with the two respective VALUES returned by the nested SELECT statement that follows and is encapsulated in parentheses. Finally, note that the nested select pulls its data from “tblSecondary”.

Why we use nested selects

Here’s a realistic business situation for use of Cockroach nested select statements. If a developer didn’t know about nested selects, in some situations they might believe they need to pull data from a table into an array, manipulate that array in some way (maybe sorting), and then use that array to add rows into their first table. Fortunately, nested selects (otherwise known as “subqueries”) provide a far more efficient way of accomplishing the task. We’ll now start writing the SQL, which will be structurally similar to “Syntax of Nested select in Insert Into” above.

First, we set up two tables with test data:

tblTechnologiesUsed

txtTechnologytxtCatTechintRating
JavascriptLanguage75
CockroachDBSQL database80
PythonLanguage95
MS SQL ServerSQL database94
C#Language94

The above Cockroach table is the table used by our fictional firm to track which technologies are being used by the company. The CockroachDB table below is filled with potential technologies. It’s important to notice the bottom table includes technology that is already in the top table. So when we are building our INSERT INTO query using a NESTED SELECT, we want to make sure to remove duplicates. This is a use case where nested selects work really well.

tblTechnologiesProposed

txtTechnologytxtCatTechintRating
JavascriptLanguage76
MongoNoSQL database75
PostgresSQL database95
CockroachDBSQL database80
PythonLanguage90
PHPLanguage70
JavaLanguage75
MS SQL ServerSQL database90
C#Language94
C++Language81
dBaseFlat database25

Writing the CockroachDB query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
INSERT INTO
    tblTechnologiesUsed
    (
    txtTechnology
    , txtCatTech
    , intRating
    )
    VALUES
    (
    SELECT
        txtTechnology
        , txtCatTech
        , intRating
    FROM
        tblTechnologiesProposed
    WHERE
        intRating > 75
        AND
        txtTechnology NOT IN
        (
            SELECT
                txtTechnology
            FROM
                tblTechnologiesUsed
        )
    )

Analysis

The first thing you may notice with our Cockroach subquery example above is that we have a nested SELECT inside another nested SELECT! Why? Because we want to be sure we do not add a row to tblTechnologiesUsed that is already in that table. We’ll look at the situation line by line:

  • INSERT INTO: This is where we set up which columns in “tblTechnologiesUsed” get populated with data from the first (outer) of our SELECT statements below.
  • VALUES: This is part of the INSERT functionality telling Cockroach what values to insert.
  • SELECT (OUTER): Determines which columns are pulled from “tblTechnologiesProposed”.
  • WHERE: This line filters results of our subquery by two requirements. (a) “intRating” must be larger than 75. (b) The value in “txtTechnology” must not already exist in “tblTechnologiesUsed”.
  • NOT IN … SELECT (INNER): This is the part of our Cockroach SQL that makes sure we don’t copy duplicate records from “tblTechnologiesProposed” into “tblTechnologiesUsed”.

After we run the Cockroach SQL above, we get the following resulting dataset:

tblTechnologiesUsed

txtTechnologytxtCatTechintRating
JavascriptLanguage76
PostgresSQL database95
CockroachDBSQL database80
PythonLanguage90
MS SQL ServerSQL database90
C#Language94
C++Language81

Conclusion

We just learned how to use NESTED SELECT in order to query Cockroach with SQL that will accomplish various tasks that require subsorting of data. This is also known as subqueries in Cockroach. We also created a realistic business example to help round out your knowledge of how nested selects in your use of SQL in CockroachDB databases can be created and used to create greater efficiency and easier maintenance.

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.