The JOIN command combines records from two tables by using values common to each. Usually, it is used an ID column in both tables that associates their values.
There are various types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN; they are used with an ON clause that sets the join condition.

- In the examples presented in this tutorial we'll use the following two tables, named "categories" and "links".
categories
idcategory
1 PHP-MySQL
2 HTML
3 JavaScript
links
idlink
1 http://coursesweb.net/php-mysql/arrays
1 http://coursesweb.net/php-mysql/strings
2 http://coursesweb.net/html/html-tables

INNER JOIN

INNER JOIN returns rows when there is at least one match in both tables (can be regarded as the default join-type).
Syntax:
SELECT column(s) FROM `table1` INNER JOIN `table2` ON `table1`.`column`=`table2`.`column`
If there are rows in "table1.column" that do not have matches in "table2.column", those rows will NOT be listed.

- Example: Select the "category" column from 'categories', and the "link" column from the 'links' table, where the value of the "id" columns is the same in both tables, ordered by "category".
SELECT `categories`.`category`, `links`.`link` FROM `categories` INNER JOIN `links` ON `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`
Result:
|  category |                link                  |
----------------------------------------------------
| HTML      | http://coursesweb.net/html/html-tables  |
| PHP-MySQL | http://coursesweb.net/php-mysql/arrays  |
| PHP-MySQL | http://coursesweb.net/php-mysql/strings |

• INNER JOIN is the same as JOIN, and can also be replaced with a WHERE clause. So, the following queries returns the same results as the example above.
- Without the INNER keyword:
SELECT `categories`.`category`, `links`.`link` FROM `categories` JOIN `links` ON `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`
- Or, with WHERE:
SELECT `categories`.`category`, `links`.`link` FROM `categories`, `links` WHERE `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`

LEFT JOIN

The LEFT JOIN returns all rows from the "left" table (table1), even if the join-condition does not find any matching record in the "right" table (table2).
Syntax:
SELECT column(s) FROM `table1` LEFT JOIN `table2` ON `table1`.`column`=`table2`.`column`
If there is no matching row for the "table2" in the ON part, the join will still return a row in the result, but with NULL in each column from "table2".

Example: We select again the "category" column from 'categories', and the "link" column from the 'links' table, where the value of the "id" columns is the same in both tables, but this time using LEFT JOIN.
SELECT `categories`.`category`, `links`.`link` FROM `categories` LEFT JOIN `links` ON `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`
Result (compare with the result of the example above to see the diference):
|  category  |               link                   |
-----------------------------------------------------
| HTML       | http://coursesweb.net/html/html-tables  |
| JavaScript | NULL                                 |
| PHP-MySQL  | http://coursesweb.net/php-mysql/arrays  |
| PHP-MySQL  | http://coursesweb.net/php-mysql/strings |

• Because LEFT JOIN returns all the rows from the left table (categories), even if there are no matches in the right table (links), we can use this fact to find rows in a table that have no counterpart in another table.
In the next example we'll find all rows in "categories" with an "id" value that is not present in "links" table:
SELECT `categories`.* FROM `categories` LEFT JOIN `links` ON `categories`.`id`=`links`.`id` WHERE `links`.`id` IS NULL
Result:
| id |  category  |
-------------------
| 3  | JavaScript |

RIGHT JOIN

The RIGHT JOIN returns all the values from the "right" table (table2) and matched values from the "left" table (table1) (NULL in case of no matching join predicate).
Syntax:
SELECT column(s) FROM `table1` RIGHT JOIN `table2` ON `table1`.`column`=`table2`.`column`

Example: We select again the "category" column from 'categories', and the "link" column from the 'links' table, where the value of the "id" columns is the same in both tables, but this time using RIGHT JOIN.
SELECT `categories`.`category`, `links`.`link` FROM `categories` RIGHT JOIN `links` ON `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`
Result (compare with the result of the example above to see the diference):
|  category  |               link                   |
-----------------------------------------------------
| HTML       | http://coursesweb.net/html/html-tables  |
| PHP-MySQL  | http://coursesweb.net/php-mysql/arrays  |
| PHP-MySQL  | http://coursesweb.net/php-mysql/strings |

• In practice, RIGHT JOIN is rarely used, since it can always be replaced with LEFT JOIN (with the table order switched). The result above is produced also with a LEFT JOIN:
SELECT `categories`.`category`, `links`.`link` FROM `links` LEFT JOIN `categories` ON `categories`.`id`=`links`.`id` ORDER BY `categories`.`category`

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute is used in <a> tag for the address of the link?
src href rel
<a href="http://coursesweb.net/" title="CoursesWeb.net">CoursesWeb.net</a>
Which CSS property sets the type of the text font?
font-family text-decoration font-size
h2 {
  font-family:"Calibri",sans-serif;
}
What instruction selects all the <div> tags with class="cls"?
querySelector("div.cls") getElementsByTagName("div") querySelectorAll("div.cls")
var elm_list = document.querySelectorAll("div.cls");
var nr_elms = elm_list.length;       // number of selected items
alert(nr_elms);
Indicate the function that can be used to get the sum of values in an array.
array_sum() array_diff() array_shift()
$arr = array(1, 2, 3, 4);
$arr_sum = array_sum($arr);
echo $arr_sum;       // 10
MySQL INNER JOIN, LEFT JOIN, RIGHT JOIN

Last accessed pages

  1. Baloons (277)
  2. Chicago Car Parking (386)
  3. Ajax-PHP Chat Script (29341)
  4. Skill Games (3914)
  5. Auto Moto Games (3996)

Popular pages this month

  1. Qwop (4763)
  2. Bubbles3 (4405)
  3. Butterfly Kyodai (3134)
  4. Zuma Deluxe (2904)
  5. Backgammon (1486)