Latest web development tutorials

SQL connection (JOIN)

SQL join for the line from two or more tables together.

SQL JOIN

SQL JOIN clause to the rows from two or more tables together, based on common fields between the tables.

The most common type of JOIN: SQL INNER JOIN (simple JOIN). SQL INNER JOIN JOIN returns all rows that satisfy the conditions from multiple tables.

The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data: 

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 本教程      | http://www.w3big.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data "access_log" website access record table: 

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| 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 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

Please note, "Websites" table "id" column point "access_log" field in the table "site_id". The above two tables by "site_id" column linking.

Then, if we run the following SQL statement (including INNER JOIN):

Examples

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id = access_log.site_id;

Execute the above SQL output results are as follows:


Different SQL JOIN

Before we continue to explain instance, let's list the different SQL JOIN types you can use:

  • INNER JOIN: If the table has at least one match, then return row
  • LEFT JOIN: even if there is no match in the right table, also returns all rows from the left table
  • RIGHT JOIN: even if there is no match left table, also returns all rows from the right table
  • FULL JOIN: Just one of the tables there is a match, it returns the row