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);
?>
