Cloud

RIGHT JOIN

The RIGHT JOIN returns all matching records from the right table combined with the left table. Even if there are no matching records in the left table, the RIGHT JOIN still returns a row in the result, with NULL in each column from the left table.

Syntax

SELECT column_1, column_2...
FROM table_1
RIGHT JOIN table_2
ON table_1.matching_field = table_2.matching_field;

In this syntax:

SELECT column_1, column_2… defines the columns from both tables to display.
FROM table_1 defines table_1 as the left table in the FROM clause.
RIGHT JOIN table_2 defines table_2 as the right table in the RIGHT JOIN condition.
ON table_1.matching_field = table_2.matching_field sets the join condition after the ON keyword with the matching field between the two tables.

Table alias

A table alias is a temporary name given to a table in a query. Aliases don’t change the result; they make queries easier to read.

SELECT A.column_1, B.column_2...
FROM table_1 A
RIGHT JOIN table_2 B
ON A.matching_field = B.matching_field;

Examples

Create the customer table:

CREATE TABLE customer (
  id int NOT NULL,
  customer_name text
);

INSERT INTO customer
    (id, customer_name)
VALUES
    (201011,'James'),
    (200914,'Harry'),
    (201029,'Ellie'),
    (201925,'Mary');

SELECT * FROM customer;

This returns:

+-----------+----------------+
| id        | customer_name  |
+-----------+----------------+
| 201011    | James          |
| 200914    | Harry          |
| 201029    | Ellie          |
| 201925    | Mary           |
+-----------+----------------+

Create the orders table:

CREATE TABLE orders (
  order_id int NOT NULL,
  order_date date,
  order_amount int,
  customer_id int
);

INSERT INTO orders
    (order_id, order_date, order_amount, customer_id)
VALUES
    (181893,'2021-10-08',3000,201029),
    (181894,'2021-11-18',2000,201029),
    (181891,'2021-09-10',9000,201011),
    (181892,'2021-10-10',7000,201925),
    (181897,'2022-05-27',6700,null),
    (181899,'2021-07-22',4500,201011);

SELECT * FROM orders;

This returns:

+------------+------------------+---------------+-------------+
| order_id   | order_date       | order_amount  | customer_id |
+------------+------------------+---------------+-------------+
| 181893     | 2021-10-08       | 3000          | 201029      |
| 181894     | 2021-11-18       | 2000          | 201029      |
| 181891     | 2021-09-10       | 9000          | 201011      |
| 181892     | 2021-10-10       | 7000          | 201925      |
| 181897     | 2022-05-27       | 6700          | null        |
| 181899     | 2021-07-22       | 4500          | 201011      |
+------------+------------------+---------------+-------------+

A RIGHT JOIN query against these tables:

SELECT customer_name, order_date, order_amount
FROM customer
RIGHT JOIN orders
ON customer.id = orders.customer_id;

The customer table is the left table, and the orders table is the right table.
The query combines values from the orders table using customer_id and matches records using the id column from the customer table.
When records match, Redpanda SQL creates a new row with customer_name and order_amount columns as defined in the SELECT clause.
Otherwise, a new row is created with NULL values from the left table (customer).

The query returns:

+------------------+----------------+-----------------+
| customer_name    | order_date     | order_amount    |
+------------------+----------------+-----------------+
| James            | 2021-09-10     | 9000            |
| James            | 2021-07-22     | 4500            |
| Ellie            | 2021-10-08     | 3000            |
| Ellie            | 2021-11-18     | 2000            |
| Mary             | 2021-10-10     | 7000            |
| null             | 2022-05-27     | 6700            |
+------------------+----------------+-----------------+

Based on the data from the customer and orders tables:

Order id 181893 matches the customer Ellie.
Order id 181894 matches the customer Ellie.
Order id 181891 matches the customer James.
Order id 181899 matches the customer James.
Order id 181892 matches the customer Mary.
Order id 181897 does not match any customer, so the customer_name column is NULL.