MySql joins

MySql joins simply take two tables and joins them together using a common value 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.

taken from tutedepot

Setting up their database

So to start we need to create a PHP document 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 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 ;  

And out test data.

 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 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 much when i’m developing i preffer left join which we will cover next.

 $sql = "SELECT * FROM `users`,`posts` WHERE `users`.`user_id` = `posts`.`user_id`"; 
 $query = mysql_query( $sql ); 
  
 while( $row = mysql_fetch_array( $query ) ) 
 { 
     echo "<p>" . $row["username"] . " - " . $row["body"] . "</p>"; 
 }  

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 seperate tables.
You can add more 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 ); 
  
 while( $row = mysql_fetch_array( $query ) ) 
 { 
     echo "<p>" . $row["username"] . " - " . $row["body"] . "</p>"; 
 }  

This query will return two rows because there are two rows in the post table which have been posted by 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 ); 
  
 while( $row = mysql_fetch_array( $query ) ) 
 { 
     echo "<p>" . $row["username"] . " - " . $row["body"] . "</p>"; 
 }  

This query will return 4 rows, one with a username 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 ); 
  
 while( $row = mysql_fetch_array( $query ) ) 
     echo "<p>" . $row["username"] . " - " . $row["body"] . "</p>"; 
 }  

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 ); 
 while( $row = mysql_fetch_array( $query ) ) 
 { 
     echo "<p>" . $row["username"] . " - " . $row["body"] . "</p>"; 
 }  

Hopefully you will now have a fair understanding of how mysql joins work and they be able to write your own joins for your applications. If you have any problems feel free to leave a comment here and they’ll try and help.
[via]