MySQL Joins

A join is how you combine rows from two or more tables based on a related column (for example, objectid, employeeid, or zipcode). In a normalized database, related data lives in separate tables, and joins let you pull it back together in a single query. MySQL supports four main join types, each with a slightly different rule for which rows appear in the result set.

INNER JOIN

Returns only the rows where there is at least one match in both tables. If a row in either table has no corresponding match, it is excluded from the result.

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN

Returns all rows from the left table, and the matching rows from the right table. If there is no match on the right side, the right-side columns appear as NULL.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

Returns all rows from the right table, and the matching rows from the left table. If there is no match on the left side, the left-side columns appear as NULL.

SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN

Returns rows when there is a match in either table, and fills in NULL for the side that has no match. Note that MySQL does not support FULL OUTER JOIN directly. The common workaround is to combine a LEFT JOIN and a RIGHT JOIN with a UNION.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Quick Comparison

Choosing the right join type depends on what you need in the result set. If you only care about records that exist in both tables, INNER JOIN is the simplest choice. If you need every record from one side regardless of whether a match exists, reach for LEFT or RIGHT instead.

• Technology

• Sql
• Mysql