SQL INNER JOINKeywords

INNER JOIN is one of the most commonly used join methods in SQL, used to extract matching records from multiple tables based on their relationships.

The INNER JOIN keyword returns rows when there is at least one match in the tables. It returns the intersection of the two tables that satisfies the join condition, i.e., data that exists in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

Parameter description:

• columns: The column names to display.
• table1: The name of table1.
• table2: The name of table2.
• column_name: The column name used for the join in the tables.

Note:INNER JOIN is the same as JOIN.

Suppose there are two tables: Students and Enrollments.

Students table：

Enrollments table：

Query using INNER JOIN

SELECT Students.Name, Enrollments.Course
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

The result is as follows:

Explanation：INNER JOINWhat is returnedStudentsandEnrollmentsfrom the tablesStudentIDis the matching data. Records without a match (such as Charlie and the record with EnrollmentID 103) will be excluded.

Demonstration Database

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

Below is data selected 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  | Example      | http://www.example.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is data from the "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)

SQL INNER JOIN Example

The following SQL statement will return the access records of all websites:

Example

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

Executing the above SQL outputs the following results:

Note:The INNER JOIN keyword returns rows when there is at least one match in the tables. If a row in the "Websites" table has no match in "access_log", those rows will not be listed.