CockroachDB Join Expressions
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 theUSE
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 | 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) |
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