CockroachDB Join Expressions

Written by Data Pilot

May 08, 2020

CockroachDB

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:

TYPE	DESCRIPTION
Inner Join	returns all the matched values from both tables
Left Outer Join	returns all the values from the left table and the matched values on the right table
Right Outer Join	returns all the values from the right table and the matched values on the left table
Full Outer Join	returns 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

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

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

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

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

CockroachDB Join Expressions

Introduction

Prerequisites

CockroachDB JOIN Examples

Supported Types of JOIN in CockroachDB

CockroachDB INNER JOIN

CockroachDB LEFT OUTER JOIN

CockroachDB RIGHT OUTER JOIN

CockroachDB FULL OUTER JOIN

Conclusion

Pilot the ObjectRocket Platform Free!

Keep in the know!

Services

Platform

Company

Resources

Support