SQL databases organize data into tables, and often, related information lives in separate tables. To combine this related data, we use SQL JOINs. This article provides a simple, beginner-friendly guide on how to use them.

Why Use JOINs?

Imagine you have two tables: "Products" and "Categories." The "Products" table holds product details (ID, name, price), while "Categories" stores category information (ID, name). To get a list of products along with their category names, you need to combine data from both tables. This is where JOINs come in.

The Basic Syntax

The general structure of a JOIN statement is:

• column_names: The columns you want to retrieve.
• table1: The first table.
• JOIN_TYPE: Specifies the type of join (e.g., INNER, LEFT, RIGHT).
• table2: The second table.
• join_column: The common column used to link the tables.

Types of JOINs

Here are the most common types of JOINs:

1. INNER JOIN: Returns only rows where there's a match in both tables based on the join condition.

2. LEFT (OUTER) JOIN: Returns all rows from the left table (table 1) and the matching rows from the right table (table 2). If there's no match in the right table, it returns NULL for the right table's columns.

3. RIGHT (OUTER) JOIN: The opposite of LEFT JOIN. Returns all rows from the right table (table 2) and matching rows from the left table (table 1). NULL is used for non-matching left table columns.

4. FULL (OUTER) JOIN: Returns all rows from both tables. If there's no match in either table, NULL is used for the missing columns. (Note: FULL OUTER JOIN is not supported by MySQL directly but can be emulated using UNION).

Example

Let's use these sample tables:

Customers table:

Orders table:

1. INNER JOIN:

Result:

2. LEFT JOIN:

Result:

3. RIGHT JOIN:

Result:

Choosing the right JOIN

• Use INNER JOIN when you only want matching rows from both tables.
• Use LEFT JOIN when you want all rows from the left table and matching rows from the right table.
• Use RIGHT JOIN when you want all rows from the right table and matching rows from the left table.
  
• Use FULL OUTER JOIN (where supported or emulated) when you want all rows from both tables, regardless of matches

Conclusion

JOINs are fundamental for working with relational databases. By understanding the different types of JOINs, you can effectively combine data from multiple tables to get the information you need. Practice using these examples with your own data to solidify your understanding.

To further enhance your SQL skills, join our next data bootcamp!