SQL JOIN

SQL join is used to combine rows from two or more tables.

The diagram below shows 7 different types of joins related to LEFT JOIN, RIGHT JOIN, INNER JOIN, and OUTER JOIN.

SQL JOIN

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

The most common type of JOIN is SQL INNER JOIN (simple JOIN). SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

Syntax:

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

Parameter Description:

• column1, column2, ...: The names of the fields to select. Multiple fields can be specified. If no field names are specified, all fields will be selected.

• table1: The first table to join.

• table2: The second table to join.

• condition: The join condition, used to specify the join method.

Demonstration Database

In this tutorial, we will use the tutorialpro sample database.

Below are the data from the "Websites" table:

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

Below are the data from the "access_log" website access log 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)

Note that the "id" column in the "Websites" table points to the "site_id" field in the "access_log" table. These two tables are linked through the "site_id" column.

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

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

The SQL output for the above query is as follows:

Different SQL JOINs

Before we proceed with the examples, here are the different types of SQL JOINs you can use:

• INNER JOIN: Returns rows when there is at least one match in both tables.

• LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.

• RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.

• FULL JOIN: Returns rows when there is a match in one of the tables.