JOIN types (INNER/LEFT/RIGHT/FULL/CROSS)

Oracle

-- INNER: matching rows
SELECT m.name, s.amount
FROM customer m JOIN orders s ON m.id = s.customer_id;
-- LEFT: customers without orders are included too
SELECT m.name, s.amount
FROM customer m LEFT JOIN orders s ON m.id = s.customer_id;
-- CROSS: Cartesian product
SELECT r.name, u.name FROM color r CROSS JOIN product u;

A JOIN combines two tables according to the given condition. INNER returns only matches; LEFT/RIGHT return all rows from one side (NULL where there is no match); FULL returns rows from both sides; CROSS returns all combinations. Besides standard ANSI JOINs, Oracle also supports the old (+) outer join syntax.

PostgreSQL

SELECT m.name, s.amount
FROM customer m INNER JOIN orders s ON m.id = s.customer_id;

SELECT m.name, s.amount
FROM customer m LEFT JOIN orders s ON m.id = s.customer_id;

SELECT m.name, s.amount
FROM customer m FULL OUTER JOIN orders s ON m.id = s.customer_id;

PostgreSQL fully supports all standard JOIN types (INNER, LEFT/RIGHT/FULL OUTER, CROSS). USING(my_column) is a shorthand for commonly named columns, while NATURAL JOIN joins automatically on all same-named columns (use with care).

MySQL

SELECT m.name, s.amount
FROM customer m INNER JOIN orders s ON m.id = s.customer_id;

SELECT m.name, s.amount
FROM customer m LEFT JOIN orders s ON m.id = s.customer_id;

-- MySQL has no FULL OUTER JOIN:
SELECT m.name, s.amount FROM customer m LEFT JOIN orders s ON m.id = s.customer_id
UNION
SELECT m.name, s.amount FROM customer m RIGHT JOIN orders s ON m.id = s.customer_id;

MySQL/MariaDB support INNER, LEFT, RIGHT and CROSS JOIN but do not offer FULL OUTER JOIN directly; it is emulated by combining LEFT and RIGHT results with UNION. The old comma-style join produces a Cartesian product and must be restricted with ON/WHERE.

SQL Server

SELECT m.name, s.amount
FROM customer m INNER JOIN orders s ON m.id = s.customer_id;

SELECT m.name, s.amount
FROM customer m LEFT JOIN orders s ON m.id = s.customer_id;

SELECT m.name, s.amount
FROM customer m FULL OUTER JOIN orders s ON m.id = s.customer_id;

SQL Server supports all standard JOIN types. The old *= / =* outer join operators have been removed; ANSI JOIN syntax must be used. APPLY (CROSS/OUTER APPLY) is an additional join form for row-by-row correlated subqueries.

MongoDB

// Closest equivalent: $lookup (left outer join)
db.customer.aggregate([
  { $lookup: {
      from: "orders",
      localField: "id",
      foreignField: "customer_id",
      as: "orders"
  } }
])

MongoDB has no classic JOIN; the $lookup aggregation stage fetches matching documents from another collection as an array (similar to a left outer join). For an INNER-like result, filter out empty arrays afterwards with $match and expand the array into rows with $unwind.

Redis

# Not applicable: Redis has no tables or JOINs.

Redis offers no relational joins; data is usually denormalized, or relationships are built on the client side by reading multiple keys. RediSearch (FT.AGGREGATE) can be used for limited join-like queries; support for the RedisGraph module ended in 2023.

Elasticsearch

# Not applicable: there is no general JOIN in a distributed design.
# Limited options: nested, parent/child (join field),
# terms lookup. Data is usually denormalized at indexing time.

Because of its distributed architecture, Elasticsearch does not offer arbitrary cross-table JOINs. For limited relationships there are the 'nested' field type within a single index and the 'join' (parent/child) field type on the same shard. The most common solution is to denormalize data at indexing time.