MySQL Inner Join

[Linux Hint]

MySQL is a well-known database derived from standard SQL. It is one of the most popular databases out there. MySQL allows you to perform CRUD operations and all other major commands needed when managing a database. When you need data from different tables based on specific conditions, MySQL provides joins to handle these types of tasks. This article covers the MySQL inner join in detail.

What is an inner join? An inner join is the same as a simple join. An inner join returns common records or rows from the provided condition(s) and tables. We can use any of these clauses and we will still have the same results. Let us look at some examples to show you how to correctly use inner joins in MySQL.

Examples

Before learning the usage of inner joins, We can get the result from two different tables based on the condition(s) by using the SELECT statement and the WHERE clause. In the following example, ‘books’ and ‘authors’ are two different tables in a database.

DESC books;

DESC authors;

In the ‘books’ table, we have the foreign author_id key from the ‘authors’ table.

To get all the columns from both tables, we set books.author_id = authors.author_id. The SELECT query would be as follows:

SELECT * FROM books, authors

WHERE books.author_id = authors.author_id;

As you can see in the image above, we have obtained all columns from both tables. Often, it does not look good to have all the columns, even if they are not needed. So, if you want to obtain only certain columns from both tables, you will need to mention the column names in the SELECT statement, as follows:

SELECT books.book_name, books.category, authors.author_fname, authors.author_lname

FROM books, authors

WHERE books.author_id = authors.author_id;

As you can see, we have a clean and clear-cut output of the four provided columns from both tables.

Now, we will perform the same task using the INNER JOIN clause.

To join two tables using the INNER JOIN clause, the SELECT query would be as follows:

SELECT books.book_name, books.category, authors.author_fname, authors.author_lname

FROM books INNER JOIN authors

ON books.author_id = authors.author_id;

As you can see in the screenshot above, we have obtained the same output, but using the INNER JOIN clause this time.

As stated earlier, the INNER JOIN clause is the same as a simple JOIN clause. This means that we can use the JOIN clause instead of the INNER JOIN clause and still obtain the same results. The SELECT query with the simple JOIN clause would be as follows:

SELECT books.book_name, books.category, authors.author_fname, authors.author_lname

FROM books JOIN authors

ON books.author_id = authors.author_id;

As you can see in the image above, we have obtained the same results. This shows you that the simple JOIN and INNER JOIN clauses are the same. You can get the same results using any of these join clauses.

The concept of the inner join does not stop here. In previous examples, we applied the join on two tables on the basis of the author_id key. Since we know that the author_id key is already the foreign key in the ‘books’ table, we may shorten the syntax by using the USING clause with the join. The syntax for using the USING clause with the JOIN clause is as follows:

SELECT books.book_name, books.category, authors.author_fname, authors.author_lname

FROM books JOIN authors

USING (author_id);

It can be observed that this query has generated the same results with the USING clause.

Similarly, we can apply a condition along with applying the join between two tables using the WHERE clause. For example, to get the same four columns from both tables  in which the author’s last name is equal to ‘Hill,’ the query for getting such output will be:

SELECT books.book_name, books.category, authors.author_fname, authors.author_lname

FROM books JOIN authors

USING (author_id)

WHERE authors.author_lname = 'Hill';

As you can see in the image above, we only obtained two rows in which the last name of the author is ‘Hill.’

So, now, you have seen some examples of the different ways to use an inner join to get the results you want in MySQL.

Conclusion

In this article, we tried a couple of different examples of using the inner join to provide a more complete understanding of the concept. You also learned how to use the USING and WHERE clauses with the inner join, as well as how to obtain the required results in MySQL. For more useful content like this, keep visiting our website, linuxhint.com.

View the full article