14 MySQL Tutorial for Beginners: Outer Joins, Cross Joins

14 MySQL Tutorial for Beginners: Outer Joins, Cross Joins


When you code an outer join, you include the LEFT or RIGHT keyword to specify the type of outer join you want to perform. Here because you are using the LEFT keyword, you are performing a left join. An outer join retrieves all rows that satisfy the join condition, plus unmatched rows in the left or right table. Let’s have a look at what happens behind the scenes! First, MySQL performs an inner join and includes in the result set all rows that satisfy the join condition. Then, when all rows that satisfy the join condition are included in the result set, MySQL includes all the missing rows from the left table, even those with unmatched rows. As you can see, the result set includes also all the unmatched rows from the left table. Note that when a row with unmatched columns is retrieved, any columns from the other table are given null values. As you can see, Donna and John don’t have a corresponding last name in the last_names table. That’s why, null values are returned for
them. You could of course specify which columns should be retrieved. Let’s make a little change to the code… This example uses a right join. Here, because you’re using the RIGHT keyword to perform a right join, the result set includes all the unmatched rows from the right table. In this case for instance, all of the rows from the second_names table are included in the result set, even those with unmatched rows. As you can see, Helen and Brian don’t have a corresponding last name in the last_names table. That’s why null values are returned for
them. Sometimes you will see the OUTER keyword as shown by this example. This keyword is optional and can be omitted. When coding outer joins, it’s a common practice to avoid using right
joins. Therefore, you should always rephrase a right join to a left join. To do that, reverse the order of the tables in the FROM clause and use the LEFT keyword instead of RIGHT. You can also use outer joins to work with more than two tables. To do that, you use skills similar to those that you use to work with inner joins. You can also combine inner joins and outer joins in the same query, as shown by this example. This statement combines data from the first_names and last_names tables. A cross join produces a result set that includes each row from the first table joined with each row from the second table. The result is a table that includes 16 rows. That’s each of the four rows in the first_names table combined with each of the four rows in the last_names table.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *