CockroachDB Join Expressions

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

Introduction

When you need to write a query that combines rows from two or more tables, the JOIN operator offers a simple and powerful way to get the results you want. There are several types of JOIN expressions available to use in CockroachDB, and it’s important to understand the differences between them. In this article, we’ll provide an overview of the different CockroachDB JOIN operations, and we’ll look at examples of each type.

Prerequisites

Before we proceed with this tutorial, let’s review a few prerequisites that are necessary for the task:

  • You’ll need to have CockroachDB installed on your machine.
  • You’ll also need a basic knowledge of CockroachDB in order to follow along with our examples. You can connect to your CockroachDB database using the cockroach sql command-line interface.
  • The \c command used to connect to a Postgres database will not work in the Cockroach SQL interface. You can execute the USE command instead to connect to a CockroachDB database:
1
USE test_db;

CockroachDB JOIN Examples

As we mentioned earlier, the CockroachDB JOIN operator is used to combine query results from multiple tables into one table.

Supported Types of JOIN in CockroachDB

The following table provides a list of supported SQL JOIN expressions in CockroachDB:

TYPEDESCRIPTION
Inner Joinreturns all the matched values from both tables
Left Outer Joinreturns all the values from the left table and the matched values on the right table
Right Outer Joinreturns all the values from the right table and the matched values on the left table
Full Outer Joinreturns all the values from both tables

We’ll need a sample data set that we can use in our examples, so let’s create some tables and add records to them. The statements shown below can be used to create the tables:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR (100)
);
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    book_title VARCHAR (100),
    genre VARCHAR (100),
    author_id INT
);

Now we can use an INSERT INTO SQL statement to insert some records into the table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO authors (author_id, author_name)
VALUES ('1', 'William Shakespeare'),
    ('2', 'George Orwell'),
    ('3', 'J.K. Rowling'),
    ('4', 'Ernest Hemingway'),
    ('5', 'William Faulkner'),
    ('6', 'J.D. Salinger');
INSERT INTO books (book_id, book_title, genre, author_id)
VALUES ('1', 'Romeo and Juliet', 'Tragedy', '1'),
    ('2', 'The Catcher in the Rye', 'Bildungsroman', '6'),
    ('3', 'A Rose for Emily', 'Fiction', '5'),
    ('4', 'To the Lighthouse', 'Novel, Fiction', NULL),
    ('5', 'Harry Potter and the Prisoner of Azkaban', 'Novel, Fantasy Fiction', '3'),
    ('6', 'Hamlet', 'Tragedy, Drama', '1'),
    ('7', 'Slaughterhouse-Five', 'Novel, Satire, Science Fiction', NULL),
    ('8', 'As I Lay Dying', 'Novel, Fiction,', '5'),
    ('9', 'Nine Stories', 'Short story, Fiction', '6'),
    ('10', 'Fantastic Beasts and Where to Find Them', 'Drama, Fantasy Fiction', '3');

CockroachDB INNER JOIN

The following INNER JOIN statement will select all authors and their respective books that have a matching author_id from the authors and books tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.author_name, b.book_title, b.genre FROM authors AS a
INNER JOIN books AS b ON a.author_id = b.author_id;
      author_name     |                book_title                |         genre
----------------------+------------------------------------------+-------------------------
  William Shakespeare | Romeo AND Juliet                         | Tragedy
  J.D. Salinger       | The Catcher IN the Rye                   | Bildungsroman
  William Faulkner    | A Rose FOR Emily                         | Fiction
  J.K. Rowling        | Harry Potter AND the Prisoner OF Azkaban | Novel, Fantasy Fiction
  William Shakespeare | Hamlet                                   | Tragedy, Drama
  William Faulkner    | AS I Lay Dying                           | Novel, Fiction,
  J.D. Salinger       | Nine Stories                             | Short story, Fiction
  J.K. Rowling        | Fantastic Beasts AND WHERE TO Find Them  | Drama, Fantasy Fiction
(8 ROWS)

CockroachDB LEFT OUTER JOIN

Next, let’s look at a LEFT OUTER JOIN. The following statement will select all authors, whether or not they have records from the books table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.author_name, b.book_title, b.genre FROM authors AS a
LEFT OUTER JOIN books AS b ON a.author_id = b.author_id;
      author_name     |                book_title                |         genre
----------------------+------------------------------------------+-------------------------
  William Shakespeare | Romeo AND Juliet                         | Tragedy
  J.D. Salinger       | The Catcher IN the Rye                   | Bildungsroman
  William Faulkner    | A Rose FOR Emily                         | Fiction
  J.K. Rowling        | Harry Potter AND the Prisoner OF Azkaban | Novel, Fantasy Fiction
  William Shakespeare | Hamlet                                   | Tragedy, Drama
  William Faulkner    | AS I Lay Dying                           | Novel, Fiction,
  J.D. Salinger       | Nine Stories                             | Short story, Fiction
  J.K. Rowling        | Fantastic Beasts AND WHERE TO Find Them  | Drama, Fantasy Fiction
  George Orwell       | NULL                                     | NULL
  Ernest Hemingway    | NULL                                     | NULL
(10 ROWS)

CockroachDB RIGHT OUTER JOIN

The following RIGHT OUTER JOIN statement will select all books, whether or not they have an associated record from the authors table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.author_name, b.book_title, b.genre FROM authors AS a
RIGHT OUTER JOIN books AS b ON a.author_id = b.author_id;
      author_name     |                book_title                |             genre
----------------------+------------------------------------------+---------------------------------
  William Shakespeare | Romeo AND Juliet                         | Tragedy
  J.D. Salinger       | The Catcher IN the Rye                   | Bildungsroman
  William Faulkner    | A Rose FOR Emily                         | Fiction
  NULL                | TO the Lighthouse                        | Novel, Fiction
  J.K. Rowling        | Harry Potter AND the Prisoner OF Azkaban | Novel, Fantasy Fiction
  William Shakespeare | Hamlet                                   | Tragedy, Drama
  NULL                | Slaughterhouse-Five                      | Novel, Satire, Science Fiction
  William Faulkner    | AS I Lay Dying                           | Novel, Fiction,
  J.D. Salinger       | Nine Stories                             | Short story, Fiction
  J.K. Rowling        | Fantastic Beasts AND WHERE TO Find Them  | Drama, Fantasy Fiction
(10 ROWS)

CockroachDB FULL OUTER JOIN

Last but not least, we’ll look at a FULL OUTER JOIN example. The following query will select all records from both tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
      author_name     |                book_title                |             genre
----------------------+------------------------------------------+---------------------------------
  William Shakespeare | Romeo AND Juliet                         | Tragedy
  J.D. Salinger       | The Catcher IN the Rye                   | Bildungsroman
  William Faulkner    | A Rose FOR Emily                         | Fiction
  NULL                | TO the Lighthouse                        | Novel, Fiction
  J.K. Rowling        | Harry Potter AND the Prisoner OF Azkaban | Novel, Fantasy Fiction
  William Shakespeare | Hamlet                                   | Tragedy, Drama
  NULL                | Slaughterhouse-Five                      | Novel, Satire, Science Fiction
  William Faulkner    | AS I Lay Dying                           | Novel, Fiction,
  J.D. Salinger       | Nine Stories                             | Short story, Fiction
  J.K. Rowling        | Fantastic Beasts AND WHERE TO Find Them  | Drama, Fantasy Fiction
  George Orwell       | NULL                                     | NULL
  Ernest Hemingway    | NULL                                     | NULL
(12 ROWS)

Screenshot of the Cockroachdb JOIN expressions in the CockroachDB SQL CLI

Conclusion

When you query a database, the information you need may be stored in more than one table. Using a JOIN expression allows you to combine records from multiple tables into a single set of query results. In this article, we reviewed the different types of CockroachDB JOIN expressions, and we looked at examples of their use. With this tutorial as a reference, you’ll be able to use the JOIN operator in your own CockroachDB queries.

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.