SQL JOIN for Beginners with Diagrams & Example Code

SQL JOINs are one of the most powerful features in database querying, allowing you to combine data from multiple tables. If you’re new to SQL or struggling to understand how different types of JOINs work, this comprehensive guide with visual diagrams will help you master them.

💡 New to database relationships? Before diving into JOINs, make sure you understand how tables relate to each other. Check out our comprehensive guide to database relationships to learn the fundamentals of one-to-one, one-to-many, and many-to-many relationships.

Sample Data Setup

Throughout this tutorial, we’ll use two simple tables: employees and departments. This realistic scenario will help you understand how JOINs work in practice.

employees table:

departments table:

Let’s create these tables:

-- Create employees table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT
);

-- Insert sample data
INSERT INTO employees VALUES 
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', 10),
(4, 'Diana', NULL);

-- Create departments table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- Insert sample data
INSERT INTO departments VALUES 
(10, 'Engineering'),
(20, 'Marketing'),
(30, 'Sales');

Now let’s explore each type of JOIN with diagrams and examples.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables.

Example:

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

Explanation: Only employees with valid department IDs are returned. Diana (with NULL dept_id) and the Sales department (with no employees) are excluded.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are returned for the right table columns.

Example:

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Result:

Explanation: All employees are returned. Diana appears with a NULL department name because her dept_id is NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists, NULL values are returned for the left table columns.

Example:

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Result:

Explanation: All departments are returned. The Sales department appears with a NULL employee name because no employees belong to it.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. When there’s no match, NULL values are returned for the missing side.

Example:

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Result:

Explanation: All employees and all departments are returned. Diana has no department, and Sales has no employees.

Note: MySQL doesn’t support FULL OUTER JOIN directly. See our MySQL FULL OUTER JOIN workaround guide for alternatives.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of both tables - every row from the first table combined with every row from the second table.

Example:

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

Result (first 6 rows):

Explanation: Each employee is paired with each department, resulting in 4 × 3 = 12 total rows.

Warning: CROSS JOINs can produce very large result sets. Use with caution!

LEFT ANTI JOIN (Left Excluding Join)

A LEFT ANTI JOIN returns rows from the left table that have no matching rows in the right table.

Example:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Result:

Explanation: Only Diana is returned because she’s the only employee without a valid department.

RIGHT ANTI JOIN (Right Excluding Join)

A RIGHT ANTI JOIN returns rows from the right table that have no matching rows in the left table.

Example:

SELECT d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL;

Result:

Explanation: Only Sales is returned because it’s the only department with no employees.

When to Use Each JOIN Type

• INNER JOIN: When you only want records that exist in both tables
• LEFT JOIN: When you want all records from the main table, even if some don’t have matches
• RIGHT JOIN: When you want all records from the secondary table (less common than LEFT JOIN)
• FULL OUTER JOIN: When you want to see everything from both tables, regardless of matches
• CROSS JOIN: When you need all possible combinations (use sparingly)
• ANTI JOINs: When you want to find records that don’t have matches

JOINs and Database Relationships

Different JOIN types work best with specific database relationship patterns:

One-to-One Relationships

• Use LEFT JOIN to include parent records even without related data
• Example: Users and their profiles (not all users have profiles)

  SELECT u.username, p.bio
  FROM users u
  LEFT JOIN user_profiles p ON u.id = p.user_id;
  

  👉 Learn more about one-to-one relationships

One-to-Many Relationships

• Use INNER JOIN when you need both parent and child data
• Use LEFT JOIN when you want all parents, even those without children
  ```sql
  – All customers with their orders (excludes customers with no orders)
  SELECT c.name, o.total
  FROM customers c
  INNER JOIN orders o ON c.id = o.customer_id;

– All customers, showing order count (includes customers with 0 orders)
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

**👉 [Learn more about one-to-many relationships](/blog/one-to-many-database-relationships-complete-guide)**

### Many-to-Many Relationships
- **Requires joining through junction tables** with multiple JOINs
- Often uses INNER JOINs to connect through the bridge table
```sql
-- Students with their enrolled courses (through enrollments junction table)
SELECT s.name, c.course_name, e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

👉 Learn more about many-to-many relationships

Performance Tips

1. Use indexes on JOIN columns for better performance
2. Filter early using WHERE clauses to reduce the dataset
3. Be careful with CROSS JOINs - they can create massive result sets
4. Consider the order of tables in complex JOINs

Practice with Beekeeper Studio

The best way to master SQL JOINs is through practice. Try these examples and experiment with your own data using Beekeeper Studio, a modern SQL editor that makes it easy to visualize your query results and understand how JOINs work.

Conclusion

SQL JOINs are essential for working with relational databases. By understanding these visual patterns and practicing with real data, you’ll be able to combine tables effectively and extract meaningful insights from your databases.

Remember:

• Start with INNER JOINs for basic relationships
• Use LEFT JOINs when you need to preserve all records from your main table
• Be mindful of NULL values in your data
• Always test your JOINs with sample data first

Continue Learning

Now that you understand SQL JOINs, dive deeper into the database design concepts that make JOINs possible:

• Understanding Database Relationships: Complete Beginner’s Guide - Learn how to design relationships between tables
• One-to-One Relationships: Complete Guide - Master 1:1 relationships with detailed examples
• One-to-Many Relationships: Complete Guide - The most common relationship pattern explained
• Many-to-Many Relationships: Complete Guide - Complex relationships with junction tables

Happy querying!