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.

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:

http://www.devshed.com/c/a/MySQL/Understanding-MySQL-Joins-33755/

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:

[code]DROP TABLE IF EXISTS `test`.`books`; CREATE TABLE `test`.`books` ( `id` int(4) unsigned NOT NULL auto_increment, `title` varchar(100) NOT NULL, `author` varchar(100) NOT NULL, `category` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;[/code]

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:

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

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:

[code]DROP TABLE IF EXISTS `test`.`customers`; CREATE TABLE `test`.`customers` ( `id` int(4) unsigned NOT NULL auto_increment, `first_name` varchar(45) default NULL, `last_name` varchar(45) default NULL, `book_id` int(4) unsigned default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;[/code]

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:

[code]INSERT INTO users (first_name, last_name, book_id) VALUES (NULL, 'Sandra', 'Smith', 1); INSERT INTO users (first_name, last_name, book_id) VALUES (NULL, 'Daniel', 'Norton', 2); INSERT INTO users (first_name, last_name, book_id) VALUES (NULL, 'Susan', 'Wilson', 5); INSERT INTO users (first_name, last_name, book_id) VALUES (NULL, 'Jennifer', 'Adams', 8); INSERT INTO users (first_name, last_name, book_id) VALUES (NULL, 'Joan', 'Wilkinson');[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers, books[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers JOIN books[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers INNER JOIN books ON customers.book_id=books.id ORDER BY customers.first_name[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers, books WHERE customers.book_id=books.id ORDER BY customers.first_name[/code]

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.

[code]Retrieving Records that Don’t Match a Specified Condition: Introducing LEFT Joins[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers LEFT JOIN books ON customers.book_id=books.id ORDER BY customers.first_name[/code]

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:

[code]SELECT customers.first_name, customers.last_name, books.title FROM customers RIGHT JOIN books ON customers.book_id=books.id ORDER BY customers.first_name[/code]

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!

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort