MySQL INNER JOIN

Summary: In this chapter, we will learn how to use MySQL INNER JOIN clause to select data from multiple tables based on join conditions.

Introducing MySQL INNER JOIN clause

The MySQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. MySQL INNER JOIN returns all rows from multiple tables where the join condition is met.

The MySQL INNER JOIN clause selects all rows from both tables as long as there is a match between the columns in both tables.

The syntax of the MySQL INNER JOIN clause is as follows:

1 2 3 4

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

OR

1 2 3 4

SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

Note: INNER JOIN is the same as JOIN

Let’s explain the above syntax that we are joining two tables table1 and table2 using the INNER JOIN clause.

For each row in the table1, the MySQL INNER JOIN clause compares it with each row of the table2 to check if both of them satisfy the join condition. When the join condition is matched, it will return the row that combine columns in both table1 and table2.

Notice that the rows in both table1 and table2 have to be matched based on the join condition. If no match found, the query will return an empty result set. This logic is also applied if we join more than 2 tables.

The above Venn Diagram illustrates how the MySQL INNER JOIN clause works. The rows in the result set must appear in both tables.

How to Avoid ambiguous column error in MySQL JOIN

If we join multiple tables and we have the same column name, Always should use table aliases in the query. so we can achieve same name column using the table qualifiers.

MySQL INNER JOIN examples:

We will continue with the employees and branches table in the sample database as shown in the picture below.

For example, we want to get employees data along with branches data also means we need to select data from both tables and match rows by comparing the branchid column from the employees table with the branchid column from the branches table as the following query:

1 2 3 4 5 6

SELECT   employees.firstname, employees.lastname, employees.jobtitle,   branches.city, branches.country FROM employees INNER JOIN branches ON employees.branchid=branches.branchid;

Above query is lengthy, we can reduce and make more readable using table alias and qualifiers. See below optimized query:

1 2 3 4 5 6

SELECT   e.firstname, e.lastname, e.jobtitle,   b.city, b.country FROM employees AS e INNER JOIN branches AS b ON e.branchid=b.branchid;

In this chapter, we have learned how to use MySQL INNER JOIN and also learned how to use table qualifier to avoid ambiguous column error.

---

MySQL ALIAS MySQL LEFT JOIN

---