Different types of joins in mysql with examples

Different types of joins in MySQL with examples

Until now we have learned that how you can get data from one table by using MySQL queries or by Codeigniter queries just the syntax difference little bit but the concept is same. In real world scenario, you need to get data from multiple tables in a single SQL query. Joins are the concept that you retrieve data from multiple tables with minimum one thing same in both tables so that you can get the data from both tables. You can use either SELECT, UPDATE OR DELETE the data from multiple tables with one query. Simple join example is this:

$dbhost = 'localhost:8080';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$query = 'SELECT a.twotutorial_id, a.twotutorial_author, b.twotutorial_count
        FROM twotutorials_tbl a, twotcount_tbl b
        WHERE a.twotutorial_author = b.twotutorial_author';
mysql_select_db('TWOTUTORIAL');
$retval = mysql_query( $query, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['twotutorial_author']}  <br> ".
         "Count: {$row['twotutorial_count']} <br> ".
         "Tutorial ID: {$row['twotutorial_id']} <br> ".
         "--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);

There are basically five types of joins but the three of them are

INNER JOIN 

LEFT JOIN 

RIGHT JOIN

INNER JOIN

an inner join is a join that is used for the matching of the two rows with some comparison columns from two tables. The inner join will select all the data from both the tables that match.

Codeigniter syntax

simple/normal join
$this->db->select("*"):
$this->db->from("mytable");
$this->db->where(s.id=c.id)
$this->db->join("student", "student.id = course.id");
$query = $this->db->get();

MySQL syntax

select s.id, s.name, c.coursename
from student s
inner join course c
on s.id=c.id;

LEFT JOIN

Left join has just the little bit difference from the simple join that means simple join is working like inner join that select all the fields or write the fields that you only require. While left join has the simple little difference that you join the two tables with the same id or another field then it will only select all the columns from the left table and return to you. The simple syntax for left join is:

SELECT a.twotutorial_id, a.twotutorial_author, b.twotutorial_count
FROM twotutorials_tbl a LEFT JOIN twotcount_tbl b
ON a.twotutorial_author = b.twotutorial_author;

RIGHT JOIN

Left join has just the little bit difference from the simple join that means simple join is working like inner join that select all the fields or write the fields that you only require. While right join has the simple little difference that you join the two tables with the same id or another field then it will only select all the columns from the right table and return to you. The simple syntax for right join is:

SELECT a.twotutorial_id, a.twotutorial_author, b.twotutorial_count
FROM twotutorials_tbl a RIGHT JOIN twotcount_tbl b
ON a.twotutorial_author = b.twotutorial_author;

These are the same names you can search outpost by just typing types of joins in MySQL, different types of joins in MySQL with examples, joins in SQL, joins in MySQL PHP, joins in MySQL pdf, joins in MySQL tutorial pdf, joins in MySQL with example, joins in MySQL w3schools you will get the result. If you have still any query or problem you can ask in the comment box we will help you. You can further read about the reading the CSV files using Codeigniter.
Import CSV file using Codeigniter....!

This Post Was Last Updated On September 30, 2016, By Author: Mahira Khan.