Fetch Rows Including Unmatched From Right Table

Return all rows from the right table and matching rows from the left, filling NULL where no match exists.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Show all job categories even if no employee holds that role

Manager and Designer have no employees, so e.name is NULL for those rows. This query is equivalent to SELECT e.name, jc.title FROM job_categories jc LEFT JOIN employees e ON jc.id = e.category_id ORDER BY jc.id, e.name — the same result written as a LEFT JOIN by swapping the table order.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE job_categories (id INT, title VARCHAR(50));

CREATE TABLE employees (id INT, name VARCHAR(50), category_id INT);

INSERT INTO
  job_categories (id, title)
VALUES
  (1, 'Engineer'),
  (2, 'Manager'),
  (3, 'Designer');

INSERT INTO
  employees (id, name, category_id)
VALUES
  (1, 'Ada', 1),
  (2, 'Grace', 1);

Validated query Shared across supported engines.

SQL

SELECT
  e.name,
  jc.title
FROM
  employees e
  RIGHT JOIN job_categories jc ON e.category_id = jc.id
ORDER BY
  jc.id,
  e.name;

Expected result Returned rows for the shared example.

name	title
Ada	Engineer
Grace	Engineer
NULL	Manager
NULL	Designer

Output is identical across all engines.

Useful when

Where this command helps.

keeping every row from the right-side dataset
working with inherited SQL that already uses right-oriented joins

Explanation

What the command is doing.

RIGHT JOIN is the mirror of LEFT JOIN. It keeps every row from the right table regardless of whether a match exists on the left side. In practice, most SQL authors rewrite RIGHT JOIN as a LEFT JOIN by swapping the table order, since the logic is equivalent and left-to-right reading is more natural.

Fetch Rows Including Unmatched From Right Table

Show all job categories even if no employee holds that role

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.