SQL joins are essential for combining rows from two or more tables, based on a related column between them. This guide will help you understand the different types of joins and how to use them effectively.

Types of Joins

  1. INNER JOIN

    • Returns rows when there is at least one match in both tables.
    • Example: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

  2. LEFT (OUTER) JOIN

    • Returns all rows from the left table, and the matched rows from the right table.
    • If there is no match, the result is NULL from the right side.
    • Example: SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

  3. RIGHT (OUTER) JOIN

    • Returns all rows from the right table, and the matched rows from the left table.
    • If there is no match, the result is NULL from the left side.
    • Example: SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

  4. FULL (OUTER) JOIN

    • Returns rows when there is a match in one of the tables.
    • Example: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;

Practical Example

Let's say you have two tables: employees and departments. The employees table has an id, name, and department_id, while the departments table has an id and department_name.

To get a list of all employees along with their department names, you can use an INNER JOIN:

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

SQL Table Example

For more information on SQL joins, check out our Advanced SQL Joins Guide.