Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 36 ยท Joins and Optimization

Join Types in HiveQL

Learn every HiveQL join: inner, left, right and full outer, LEFT SEMI, cross and multi-table joins, plus the rules that make Hive joins correct and fast.

A join combines rows from two tables that share a key. In a classic database a join is a local operation on one machine. In Hive the tables are files spread over a cluster, so a join also means moving data: rows with the same key must meet on the same worker before they can be matched. That is why joins are the most expensive operation in HiveQL and why this chapter starts with them.

This lesson covers the join types HiveQL supports and the syntax rules that trip people up. The next lessons explain how Hive executes joins and how to make them faster.

Hive itself cannot run in your browser, so the Hive statements below are read-only. The <TryIt> editors run the same join logic in SQLite (the sql.js engine). For plain joins SQLite and HiveQL behave the same; where they differ, the lesson says so.

Inner join

An inner join keeps only the rows that have a match on both sides. JOIN and INNER JOIN mean the same thing.

SQL
SELECT o.id, c.first_name, o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id;

HiveQL has always required that the join condition lives in ON, and for years only equality conditions were allowed there. Modern Hive (2.2 and later) also accepts inequality conditions such as ON a.ts BETWEEN b.start AND b.end, but these cannot be hash-matched, so they run as a much slower comparison of many row pairs. Prefer an equality key plus a filter whenever you can.

SQL Inner join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Outer joins

Outer joins keep unmatched rows and fill the missing side with NULL.

  • LEFT OUTER JOIN keeps every row of the left table.
  • RIGHT OUTER JOIN keeps every row of the right table.
  • FULL OUTER JOIN keeps every row of both tables.
SQL
-- Every customer, with their orders if they have any
SELECT c.id, c.first_name, o.id AS order_id
FROM customers c
LEFT OUTER JOIN orders o ON o.customer_id = c.id;

The word OUTER is optional. A classic use of a left join is the anti-join: keep only the left rows with no partner, by filtering on NULL afterwards.

SQL Customers who never ordered ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ON versus WHERE in outer joins

A condition in ON decides which rows match. A condition in WHERE filters the finished result. For an inner join the two are equivalent, but for an outer join they are not. Putting a filter on the right table in WHERE throws away the NULL rows and quietly turns your left join into an inner join.

SQL
-- Keeps all customers, orders only when shipped
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped';

-- Looks the same, but drops every customer without a shipped order
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';
SQL ON versus WHERE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

LEFT SEMI JOIN

Hive has a join type that most databases lack. LEFT SEMI JOIN returns rows of the left table that have at least one match on the right, but never returns right-side columns and never duplicates a left row, however many matches exist. It is Hive's way of writing IN or EXISTS.

SQL
SELECT c.id, c.first_name
FROM customers c
LEFT SEMI JOIN orders o ON o.customer_id = c.id;

Two rules: the right table may only appear in the ON clause (not in SELECT or WHERE), and the result has the columns of the left table only. Modern Hive also accepts WHERE id IN (SELECT ...) and WHERE EXISTS (...) and rewrites them into semi joins, so both styles work. SQLite has no LEFT SEMI JOIN, so the editor below uses EXISTS, the portable equivalent.

SQL Semi join with EXISTS (portable stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The counterpart is NOT EXISTS or NOT IN, which keep rows with no match (an anti join, as above).

Cross join and multi-table joins

CROSS JOIN pairs every left row with every right row. For tables of 1,000 and 5,000 rows that is 5 million rows, so use it only deliberately, for example to attach a one-row table of parameters or to build a calendar grid.

SQL
SELECT p.name, w.label
FROM products p
CROSS JOIN (SELECT 'Spring' AS label) w;

You can chain as many joins as you need. Hive plans them left to right, and when consecutive joins share the same key it can run them in a single MapReduce or Tez stage. That is a good reason to put the biggest table last in a chain of joins on the same column, a point the next lessons make precise.

SQL
SELECT o.id, c.first_name, p.name, oi.quantity
FROM orders o
JOIN customers c   ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p    ON oi.product_id = p.id;
SQL Four-table join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Rules and pitfalls

  • NULL never equals NULL. Rows whose key is NULL match nothing in an inner or outer join. Clean or default the key first, or use the null-safe operator <=> in HiveQL (a.k <=> b.k).
  • Aliases are your friend. Qualify every column (o.id) so a renamed column never makes a query ambiguous.
  • Filter early. Write WHERE conditions or subqueries that shrink a table before joining it. Hive pushes many predicates down automatically, but not through every outer join.
  • Duplicate keys multiply rows. Joining on a non-unique key gives one output row per matching pair. If a count looks too big, check key uniqueness.
  • Avoid implicit joins. FROM a, b WHERE a.k = b.k is accepted by recent Hive but older versions produced a cross join. Use explicit JOIN ... ON.
flowchart LR
    A["Left table"] --> J{"Join type"}
    B["Right table"] --> J
    J --> I["INNER: matches only"]
    J --> L["LEFT: all left rows"]
    J --> F["FULL: all rows of both"]
    J --> S["LEFT SEMI: left rows that match"]

The join types differ only in what happens to rows without a partner.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does LEFT SEMI JOIN return?

    A semi join is a filter on the left table: it keeps a left row if a match exists, never repeats it and never returns right-side columns.
  2. 2. A query uses LEFT JOIN, then WHERE o.status = 'shipped' on the right table. What happens?

    Unmatched rows have NULL in o.status, so the WHERE condition removes them. Put the condition in ON to keep the outer-join behavior.
  3. 3. How do you find customers with no orders using a join?

    The left join keeps every customer, and the rows without an order have NULL in the order columns, which the filter selects.
  4. 4. Why should you avoid a CROSS JOIN between two large tables?

    Every left row is paired with every right row, so the output grows multiplicatively.

Summary

  • Inner joins keep matches; left, right and full outer joins keep unmatched rows and fill gaps with NULL.
  • LEFT SEMI JOIN is Hive's EXISTS: left columns only, no duplicates.
  • In outer joins, conditions in ON and WHERE mean different things.
  • Keys equal to NULL never match; duplicate keys multiply rows.
  • Equality conditions are the fast path; inequality joins are supported but slow.

Next: Map Joins and Shuffle Joins