SQL connection (JOIN)
SQL join is used to combine rows from two or more tables.
SQL JOIN
The SQL JOIN clause is used to combine rows from two or more tables based on common fields between the tables.
The most common JOIN type: SQL INNER JOIN (simple JOIN). SQL INNER JOIN returns all rows from multiple tables that satisfy the JOIN conditions.
Demo Database
In this tutorial, we will use the php sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | //m.sbmmt.com/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+------+---------+
The following is the data of the "access_log" website access record table:
+-----+--------- +-------+------------+
| aid | site_id | count | date |
+-----+------ ---+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016 -05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 201 6-05 -16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+----------+------+--- ---------+
9 rows in set (0.00 sec)
Please note that the "id" column in the "Websites" table points to Field "site_id" in the "access_log" table. The two tables above are linked through the "site_id" column.
Then, if we run the following SQL statement (including INNER JOIN):
Example
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
The output result of executing the above SQL is as follows:
Different SQL JOIN
Before we continue with examples, let’s list the different SQL JOIN types you can use:
INNER JOIN: Return rows if there is at least one match in the table
LEFT JOIN: Even if there is no match in the right table, Also returns all rows from the left table
RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
FULL JOIN: As long as there is a match in one of the tables, rows
