Using MySQL left JOIN and right JOIN

MySQL left or right JOIN can be very helpful and it refers to the order in which the tables are put together and the results are displayed.

Left Join

When using LEFT JOIN all rows from the first table will be returned whether there are matches in the second table or not. For example, if table users contains users main information and table options contains users optional information, any records in options table would be tied to a particular id in the users table.

"SELECT name, lastname, optonalinfo FROM users LEFT JOIN options ON users.id = options.id"

The result of this query would return name and lastname values from the users table and all available values from the options table. NULL is returned for non-existing values in options table.

Right Join

RIGHT JOIN works just like the LEFT JOIN but with table order reversed. All rows from the second table are going to be returned whether or not there are matches in the first table. But in case of users and options tables there is only one record in options table. This means that only one of three rows will be displayed.

"SELECT name, lastname, optionalinfo FROM users RIGHT JOIN options ON users.id=options.id"

These are two most common types of JOINS available in MySQL. There are also INNER, CROSS JOIN, STRAIGHT JOIN and NATURAL JOIN. To learn about these visit http://mysql.com

Do you like this or find it useful? Drop me a note or treat me to a double-espresso from my favorite coffee shop.