MySQL table join

MySQL table join

JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

 

 

MySQL supports the following types of joins:

  • Cross join
  • Inner join
  • Left join
  • Right join

To join tables, you use the CROSS JOIN, INNER JOIN, LEFT JOIN or RIGHT JOIN clause for the corresponding type of join. The join clause is used in the SELECT statement appeared after the FROM clause.

PHP – MYSQL example:

 

<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = ‘rootpassword’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die(‘Could not connect: ‘ . mysql_error());
}

$sql = ‘SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author’;

mysql_select_db(‘TUTORIALS’);
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
die(‘Could not get data: ‘ . mysql_error());
}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo “Author:{$row[‘tutorial_author’]} <br> “.
“Count: {$row[‘tutorial_count’]} <br> “.
“Tutorial ID: {$row[‘tutorial_id’]} <br> “.
“——————————–<br>”;
}
echo “Fetched data successfully\n”;
mysql_close($conn);
?>