Home arrow MySQL arrow MySQL Left and Right Joins

MySQL Left and Right Joins

In this MySQL database tutorial, you will learn how to perform LEFT and RIGHT joins using a sample database and some hands-on examples.

By: Alejandro Gervasio
Rating: starstarstarstarstar / 0
August 04, 2011

print this article



Not that I want to sound pessimistic, but using SQL without understanding the basics of joins is pretty much like trying to drive a car that has only three wheels - youíre not getting the most out of the language! That being said,joins are fairly easy to master -at least at a basic level.To prove this,in the course of the introductory part of this series, I developed a few easily understandable examples, which showed how to perform FULL and INNER joins using a couple of sample MySQL tables. If you missed that article or need a refresher, you can find it here:


As a quick recap, a FULL join (aka Cartesian product) between two tables will return all the possible combinations of rows from the involved tables. On the other hand, an INNER join - including a specified condition -will retrieve all the rows from the target tables that meet the given condition. This shows in a nutshell that INNER joins can be really useful, even in the simplest use cases.

Of course, there are situations where itís necessary to fetch table rows that donít satisfy a specified predicate. In such cases, LEFT and RIGHT joins can be your best friends, as they permit you (with some subtle Ėyet important differences), to perform this task in a straightforward manner.

Therefore, in this final installment Iíll be creating some additional examples, which will teach you in an approachable way, how to perform some simple LEFT and RIGHT joins using the pair of MySQL tables defined in the preceding tutorial.

Review Time: Working with FULL and INNER Joins

In case you still havenít had the opportunity to take a peek at the first chapter of this tutorial, where I explained how to perform FULL and INNER joins utilizing the sample tables mentioned in the introduction, below I will reintroduce the group of examples created in the first article, so that you can study them in detail and understand their driving logic.

With that being said, the first thing that needs to be done is to show the definitions of the pertaining tables. Hereís the first one, which comes in handy for storing data about some books:

DROP TABLE IF EXISTS `test`.`books`; CREATE TABLE `test`.`books` ( `idint(4unsigned NOT NULL auto_increment, `titlevarchar(100NOT NULL, `authorvarchar(100NOT NULL, `categoryvarchar(100NOT NULLPRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The structure of the books table is pretty easy to understand, so move along to the following code snippet, which is responsible for populating it with a few real programming books:

INSERT INTO books (idtitleauthorcategoryVALUES (NULL'PHP and MySQL Web Development (4th Edition)''Luke Welling and Laura Thomson''PHP Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'PHP Solutions: Dynamic Web Design Made Easy''David Powers''PHP Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'Learning Java''Patrick Niemeyer and Jonathan Knudsen''Java Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'Effective Java (2nd Edition)''Joshua Bloch''Java Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'C++ Primer Plus (5th Edition)''Stephen Prata''C++ Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'C++ How to Program (7th Edition)''Paul Deitel and Harvey M. Deitel''C++ Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'Pro C# 2010 and the .NET 4 Platform''Andrew W. Troelsen''C# Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'C# 4.0 in a Nutshell: The Definitive Reference''Joseph Albahari and Ben Albahari''C# Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'C# in Depth, Second Edition''Jon Skeet''C# Programming');
INSERT INTO books (idtitleauthorcategoryVALUES (NULL'Microsoft Visual C# 2010 Step by Step''John Sharp''C# Programming');

Now that the earlier ďbooksĒ table is in a usable state, the next step that must be taken is to create a second one, so that we can have some fun by joining them. This additional table is called ďcustomersĒ and its definition looks like this:

DROP TABLE IF EXISTS `test`.`customers`; CREATE TABLE `test`.`customers` ( `idint(4unsigned NOT NULL auto_increment, `first_namevarchar(45) default NULL, `last_namevarchar(45) default NULL, `book_idint(4unsigned default NULLPRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As shown above, aside from storing the first and last names of some customers, this brand new table defines a foreign key called ďbook_idĒ, which permits you to determine which book has been purchased by a particular customer. So far, this is nothing complicated, so itís time to fill in this table with a few records. The following batch of INSERTS does exactly that:

INSERT INTO users (first_namelast_namebook_idVALUES (NULL'Sandra''Smith'1);
INSERT INTO users (first_namelast_namebook_idVALUES (NULL'Daniel''Norton'2);
INSERT INTO users (first_namelast_namebook_idVALUES (NULL'Susan''Wilson'5);
INSERT INTO users (first_namelast_namebook_idVALUES (NULL'Jennifer''Adams'8); 
INSERT INTO users (first_namelast_namebook_idVALUES (NULL'Joan''Wilkinson');

Mission accomplished. If at this point, you browse the two tables just created, they should look identical to the ones depicted by the following screen capture:

Now that the tables in question are ready to be tested, itís time to make things a bit more interesting and see how we can joining them via a FULL join, only for demonstrative purposes.

The query below uses the shortest syntax to achieve this. Check it out:

SELECT customers.first_namecustomers.last_namebooks.title FROM customersbooks

Without a doubt, the above SQL statement is pretty simple to grasp. In short, it takes all the rows from the target tables and creates a new one containing all ofthe possible combinations of them. As I said before, however, this isnít very useful in the real world, but it comes in handy for demonstrating how to perform the simplest form of join between two tables.

In addition, itís possible to produce an identical result using the JOIN keyword, without specifying a condition. The following query shows how to accomplish this in a nutshell:

SELECT customers.first_namecustomers.last_namebooks.title FROM customers JOIN books

Effectively, both expressions are semantically equivalent, which means that they should generate the same result. But is this really true? Well, regardless of what query you run in your MySQL client program, you should get the following output table:

So far, so good. At this moment, Iím sure that you understood the logic that stands behind using FULL joins. Given that, the next logical step that must be taken is to show how to retrieve the rows from the previous tables that meet a specified condition. This is known as an INNER join (or EQUI join), and itís possible to perform it by using the following expression:

SELECT customers.first_namecustomers.last_namebooks.title FROM customers INNER JOIN books ON customers.book_id=books.id ORDER BY customers.first_name

As one might expect, the above query will generate a new table with the rows of both tables that satisfy the given condition. This result is especially helpful if we want to find out what customers bought a book. Of course, the best way to understand this process is by looking at the table returned by the earlier query. Here it is:

Itís possible to perform an INNER join without having to specify explicitly the INNER keyword. If youíre wondering how to achieve this, the following code bit should be pretty illustrative:

SELECT customers.first_namecustomers.last_namebooks.title FROM customersbooks WHERE customers.book_id=books.id ORDER BY customers.first_name

There you have it.So far, I demonstrated how to fetch table rows that do match a specified condition, which is all well and fine,but there will becertain cases where youíll need to retrieve records that donít. What if you want to list all the customers who purchased a book along with the ones who didnít?

Hereíswhere the LEFT joins come into play. In the following section Iíll be showing you how to perform this new type of join with the two previous tables.

Retrieving Records that Donít Match a Specified ConditionIntroducing LEFT Joins

LEFT joins are of great help when it comes to fetching table rows that donít match a given condition. To elaborate a bit further, say that you need to retrieve from the previous tables all the customers, including the ones who didnít buy a book. In a case like this, the query could be formulated in the following way:

SELECT customers.first_namecustomers.last_namebooks.title FROM customers LEFT JOIN books ON customers.book_id=books.id ORDER BY customers.first_name

If the above expression looks rather confusing to you, essentially all that it says is: go get me all the records from the ďcustomersĒ table even if thereíre rows in the ďbooksĒ table donít match the specified predicate. Obviously, this can be seen more clearly in the following image, which shows the result generated by the earlier query:

From the above snapshot, it becomes evident how a LEFT joins works; even though our friend Joan Wilkinson didnít purchase any books, she does appear nonetheless. Furthermore, if we wanted to list all the books available in the corresponding table, no mater if they were bought or not by a given customer, this could be achieved via the counterpart of a LEFT join -the RIGHT join.

Therefore, in the following section Iíll be creating a final example, which will demonstrate how to perform the aforementioned RIGHT join with the two sample tables.

Setting up a Final Example: Using RIGHT Joins

To be frank, performing a RIGHT join with our sample tables isnít very different from running a LEFT join. In plain English, a RIGHT join will return all of the rows from the right (or second) table, even if there arerecords in the first one that donít satisfy the given condition. In this particular case, where weíre dealing with books and customers, this type of join could be used for listing all of the available books, no matter if they were bought by a customer or not.

Not surprisingly, a RIGHT join requires the use of the RIGHT keyword, and it can be performed in the following manner:

SELECT customers.first_namecustomers.last_namebooks.title FROM customers RIGHT JOIN books ON customers.book_id=books.id ORDER BY customers.first_name

Definitively, the syntax of the above RIGHT join is fairly easy to follow. Nevertheless, for you to understand more quickly the result that it produces, take a look at the below image, which will be hopefully quite demonstrative:

There you have it. At this point, you can see how simple it is to run a few basic joins with MySQL.Of course, the examples showcased in this article are somewhat contrived, as they only use a couple of trivial tables. However, they should be quite instructive, especially if youíre interested in digging deeper into the extensive terrain of joins. So, feel free to tweak all of the code samples included in this tutorial, something that will provide you with a more solid background on the topic.

Final Thoughts

Itís hard to admit it, but weíve come to the end of this roundtrip on understanding joins in MySQL. As you saw for yourself in the bunch of examples coded in the series, performing the most common types of joins is a fairly straightforward process that can be mastered with minor effort. Naturally, in the real word youíll be most likely confronted with more complex scenarios, which will require you to join more than two tables or fetch rows that must suit more elaborate and twisted conditions.

Nevertheless, if you already picked up the driving logic of simpler joins, you should be table to tackle more challenging use cases and end up succeeding in such demanding situations.

See you in the next MySQL tutorial!

>>> More MySQL Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: