The MySQL Inner join

A MySQL Join is when data is collected from two or more tables and “joined” on keys to return as one, saving you doing multiple queries.

There are several types of joins, inner is the most common and it is summed up with this diagram

Inner join is from two tables where a key from table1 = key from table2, returning selected values from both tables.

SELECT table1.col, table1.col, table2.col, table2.col
FROM table1
INNER JOIN table2 ON table1.user_id = table2.user_id;

You can return as many or little (atleast 1 obviously) from either tables.

Example

For this post a small example database has been setup, Consisting of a users and login_data table. The users table has user_id, username, email, posts and score. login_data has user_id, result and when.

I want to get the login_data values for users whilst also returning their username. The login_data table does not store the username just the user_id making it a perfect scenario to do an inner join.

I can pair the user_id values from users and login_data

SELECT users.user_id, users.username, login_data.when, login_data.result
FROM users
INNER JOIN login_data ON users.user_id = login_data.user_id

Returns

Had i need to only view the login data fro a certain user i can filter with a WHERE on their user_id like:

SELECT users.user_id, users.username, login_data.when, login_data.result
FROM users
INNER JOIN login_data ON users.user_id = login_data.user_id WHERE users.user_id = '264';

For further reading here is the official docs.