SQL JOIN

JOIN Types: INNER vs LEFT vs RIGHT vs FULL OUTER

SQL joins are used to combine rows from two or more tables based on a related column. Understanding the type of join is crucial for controlling which records are included in the result.

INNER JOIN: Returns only the rows with matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. If no match exists, the right side will contain NULL.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. If no match exists, the left side will contain NULL.
FULL OUTER JOIN: Returns all rows from both tables. If there’s no match on either side, the missing side will contain NULL.

Use the appropriate join type depending on whether you want to include unmatched rows and from which table(s). This is especially helpful for reports, audits, and merging data from multiple sources.

INNER JOIN

Returns only employees that belong to a department.

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id;

LEFT JOIN

Returns all customers, even if they have no orders (NULL for unmatched orders).

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

RIGHT JOIN

Returns all suppliers, including those who have no products listed.

SELECT products.name, suppliers.name
FROM products
RIGHT JOIN suppliers
ON products.supplier_id = suppliers.id;

FULL OUTER JOIN

Returns all authors and books, with NULLs where no match exists.

SELECT a.name, b.title
FROM authors a
FULL OUTER JOIN books b
ON a.id = b.author_id;

Best Practices

Use aliases (e.g., AS a) to make queries cleaner
Clearly define join conditions with ON
Use IS NULL to filter unmatched rows when needed

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu