Joins
KSQL supports INNER JOIN and LEFT JOIN. RIGHT JOIN and CROSS JOIN are intentionally not supported — see Limitations.
INNER JOIN
Returns only rows that match in both tables.
SELECT Name, Amount
FROM Orders
INNER JOIN Customers ON Orders.Customer = Customers.Name
JOIN (bare) is the same as INNER JOIN:
SELECT Name, Amount
FROM Orders
JOIN Customers ON Orders.Customer = Customers.Name
LEFT JOIN
Returns all rows from the left table, matched rows from the right (NULLs where there's no match).
SELECT Name, Amount, Status
FROM Orders
LEFT JOIN Customers ON Orders.Customer = Customers.Name
Table aliases
Use aliases to shorten references and avoid ambiguity:
SELECT c.Name, o.Amount, o.Status
FROM Orders o
LEFT JOIN Customers c ON o.Customer = c.Name
WHERE c.Revenue > 10000
-- Explicit AS keyword
SELECT c.Name, o.Amount
FROM Orders AS o
LEFT JOIN Customers AS c ON o.Customer = c.Name
When two joined tables share a column name (e.g. both have a Name column), you must prefix references with the table name or alias to disambiguate.
Multi-table joins
Chain multiple JOINs:
SELECT c.Name, o.Amount, p.ProductName
FROM Orders o
LEFT JOIN Customers c ON o.Customer = c.Name
LEFT JOIN Products p ON o.Product = p.SKU
RIGHT JOIN workaround
Swap the table order and use LEFT JOIN:
-- Instead of: FROM A RIGHT JOIN B ON ...
-- Write: FROM B LEFT JOIN A ON ...
SELECT b.Name, a.Value
FROM TableB b
LEFT JOIN TableA a ON b.Key = a.Key
Joining on relation columns
Relation columns link to rows in other tables. They expose the linked row's display value, which you can join on directly:
-- Orders table has a Customer column that links to Customers
SELECT o.OrderNumber, c.Name, c.Revenue
FROM Orders o
LEFT JOIN Customers c ON o.Customer = c.Name