Introduction to MySQL Joins

MySQL joins simply takes two tables and joins them together using one or more common values which they both share. For example if you had a table users and a table posts and you wanted to get all posts by a certain user you could use a query to get all posts where posts.userid = users.user_id.

Setting up our database

So to start we need to create a PHP script connecting to our database.

$host = "localhost";
$user = "root";
$pass = "";
$name = "joins";
$link = mysql_connect($host, $user, $pass);
if (!mysql_select_db($name, $link)) {
    die("there was an error! ". mysql_error());
}

For this example I have created two straight forward tables users and posts the table field they both share in common is user_id.

CREATE TABLE joins.users (
user_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
username VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE joins.posts (
post_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
user_id INT( 11 ) NOT NULL ,
body TEXT NOT NULL
) ENGINE = MYISAM ;

Next we need to add some test data to both database tables:

INSERT INTO joins.users ( user_id , username )
VALUES ( NULL , 'martynj' ), ( NULL , 'bob' ), (NULL , 'dave');

INSERT INTO joins.posts (post_id, user_id, body) VALUES (NULL, '1', '
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum in leo leo. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed vulputate nunc quis lacus elementum fringilla. Donec nec nisl sapien. Sed non nisi sed lorem ultricies euismod ac ut velit. Etiam nec lorem et risus volutpat fringilla sit amet sollicitudin ligula. Nullam vel elit at lorem euismod lobortis.
'), (NULL, '1', '
Curabitur sed purus felis, vitae rhoncus sem. Curabitur ut massa nulla. Vivamus mollis ante et libero varius sed condimentum dolor malesuada. Aliquam non nibh in sem porttitor bibendum fringilla eu nulla. Etiam porta nibh felis, vitae euismod ligula. Aliquam condimentum nibh vitae purus tincidunt ultrices. Etiam felis est, porta in vulputate in, ultrices quis sem.
'), (NULL, '2', '
Nunc sed lorem sem, ut iaculis leo. In mollis felis nec mauris consectetur quis egestas leo dapibus. In hac habitasse platea dictumst. In et malesuada odio. Ut eleifend justo at massa adipiscing gravida. Etiam posuere diam justo, vitae dapibus odio.
');

Now we can start testing the different joins on our tables.

Inner joins

An inner join will return all rows regardless of common values in both tables. Generally I don’t use these types of joins very often when I’m developing. I prefer the left join which we will cover next.

$sql = "SELECT * FROM users,posts WHERE users.user_id = posts.user_id";
$query = mysql_query( $sql );

So as you can see for this query it will return three rows because there are three rows in out posts table it will have also joined the data from posts and users because we can use both “username” and “body” even though there both fields from separate tables.

You can add more conditions to the where clause in this query without it breaking, for example.

$sql = "SELECT * FROM users,posts WHERE users.user_id = posts.user_id && users.username = 'martynj'";
$query = mysql_query( $sql );

This query will return two rows because there are two rows in the post table which have been posted by the user martynj.

Left joins

The difference between inner joins and left joins is that while an inner join returned all users which had a post in the posts table, a left join will return all users regardless of rows in the posts table.

$sql = "SELECT * FROM users LEFT JOIN posts ON users.user_id = posts.user_id";
$query = mysql_query( $sql );

This query will return 4 rows, one with a user name dave but because there were no posts by that user in the posts table, body will be set to NULL.

Once again we can add a where clause if necessary.

$sql = "SELECT * FROM users LEFT JOIN posts ON users.user_id = posts.user_id WHERE users.username = 'martynj'";
$query = mysql_query( $sql );

Right joins

The only difference between left joins and right joins is that with a right join it will return all rows from the second table (posts) regardless of common values in the first table, it will then join data from the first table which matches. So it works like a left join in reverse.

$sql = "SELECT * FROM users RIGHT JOIN posts ON users.user_id = posts.user_id";
$query = mysql_query( $sql );

Use the following code snippet in your PHP test script to show the results without using phpmyadmin for your example queries:

while( $row = mysql_fetch_array( $query ) ) {
	echo $row["username"] . " - " . $row["body"] .PHP_EOL;
}

Hopefully you will now have a fair understanding on how a MySQL joins works and you’re able to write joins for your applications. If you have any problems feel free to leave a comment here and we’ll try and help.