intermediateCore SQLMySQL Specific100% Free

SQL JOINs in MySQL

Definition

MySQL implements all standard SQL JOIN types with its own optimizer that uses hash joins, block nested loop joins, and index-based nested loops for performance.

Introduction to SQL JOINs in MySQL

MySQL 8.0 introduced hash join support, dramatically improving performance for large JOIN operations that lack usable indexes. Understanding MySQL's join execution strategies and available optimizer hints lets you write joins that scale to millions of rows.

Syntax

SQL

-- Standard JOIN syntax
SELECT t1.col, t2.col
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.t1_id;

-- STRAIGHT_JOIN: forces left table to be read first
SELECT STRAIGHT_JOIN e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

Examples

MySQL 8.0 Hash Join

-- MySQL 8.0+ uses hash join automatically when no indexes exist
-- Force hash join with optimizer hint:
SELECT /*+ HASH_JOIN(o, c) */ o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 1000;

MySQL 8.0 introduced hash joins. The optimizer chooses the best strategy, but you can hint with /*+ HASH_JOIN() */ for specific scenarios.

Try in Playground

LEFT JOIN to Find Non-Matching Records

-- Find customers who have never placed an order
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

LEFT JOIN + WHERE IS NULL is the standard SQL pattern for anti-join — finding rows in the left table that have no match in the right table.

Try in Playground

Common Mistakes

MySQL does not support FULL OUTER JOIN natively — simulate with LEFT JOIN UNION RIGHT JOIN

Joining on columns with different charsets/collations — disables indexes and causes implicit conversion

Not having an index on the JOIN column in the right-hand (inner) table — massive performance impact

Using USING() with ambiguous column names in complex queries

Frequently Asked Questions

Does MySQL support FULL OUTER JOIN?

No, MySQL does not natively support FULL OUTER JOIN. Simulate it with: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id; The UNION removes duplicates from matched rows.

What is STRAIGHT_JOIN in MySQL?

STRAIGHT_JOIN forces MySQL to join tables in the order written in the query, overriding the optimizer's join order decision. Use it when you know a specific join order is more efficient than what the optimizer chooses. Useful when optimizer statistics are stale or misleading.

What join algorithms does MySQL 8 use?

MySQL 8.0 uses: Nested Loop Join (default — iterates outer table, looks up inner with index), Block Nested-Loop Join (batches outer rows to reduce inner table reads when no index), and Hash Join (builds hash table from smaller input — new in MySQL 8.0, very effective for large equi-joins).

Practice This in the SQL Playground

Write real queries, see live results, and master SQL JOINs in MySQL hands-on. 100% free.

Open SQL Playground Browse All Lessons