JOINs in TiDB

JOIN is used to combine data from two or more tables based on related columns.

Types of JOIN

Setup Data

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2),
    order_date DATE
);

INSERT INTO customers VALUES
(1, 'John', '[email protected]'),
(2, 'Mike', '[email protected]'),
(3, 'Anna', '[email protected]');

INSERT INTO orders VALUES
(1, 1, 250000, '2024-01-15'),
(2, 1, 180000, '2024-01-20'),
(3, 2, 350000, '2024-02-01'),
(4, 4, 120000, '2024-02-05');  -- customer_id 4 does not exist in customers

INNER JOIN

Returns only the rows that match in both tables:

SELECT c.name, o.amount, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

LEFT JOIN

Returns all rows from the left table, including those without a match:

SELECT c.name, o.amount, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN

Returns all rows from the right table, including those without a match:

SELECT c.name, o.amount, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

FULL OUTER JOIN

Returns all rows from both tables. TiDB does not support the FULL OUTER JOIN syntax directly; use UNION instead:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

Produces the Cartesian product of both tables:

SELECT c.name, p.product_name
FROM customers c
CROSS JOIN products p;

Multiple JOIN

SELECT c.name, o.amount, p.product_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

Self JOIN

-- Find customers with orders whose amount is above the average
SELECT a.name, a.amount
FROM orders a
INNER JOIN orders b ON a.customer_id = b.customer_id
WHERE a.amount > (SELECT AVG(amount) FROM orders WHERE customer_id = a.customer_id);

Performance Tips

• Always use indexes on JOINed columns
• Use EXPLAIN to check the execution plan
• Avoid JOINing very large tables without a filter
• Use aliases to keep queries clear

In the next chapter, we will look at indexes in TiDB.