SQL INNER JOIN

SQL INNER JOIN statement selects data present in both (or more) tables according to the common field between them. It matches all the rows in table 1 with all the rows with table 2 for the JOIN condition and returns all the pair of rows which match the criteria. Therefore, a row from table 1 appears if and only if the corresponding row from table 2 is present. In this article, we will discuss the SQL INNER JOIN Keyword. Also, we will discuss a few examples of writing queries.

Note: INNER JOIN is also known as EQUIJOIN.

Syntax

SELECT table1.column1, table2.column2 … FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

  • table1.column1: The columns which you need to fetch from table1.
  • table2.column2: The columns which you want to fetch from table2.
  • table1: The name of the first table.
  • table2: The name of the second table.
  • table1.common_field = table2.common_field: Rows which have the common field matching in both the tables will return.

Also, you can use SQL Aliases in order to give a temporary name to table1 and table2. This can help in using short memorable names for the tables.

SELECT t1.column1, t2.column2 … FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.common_field = t2.common_field;

Additionally, we can write JOIN instead of INNER JOIN in SQL. It is same as INNER JOIN.

SQL INNER JOIN Vein Diagram
INNER JOIN

Demo Tables

Refer the following tables for all the examples in this article. We have three tables: users, orders and user_description.

mysql> SELECT * FROM users;
+---------+----------+------------------+
| user_id | username | email_id         |
+---------+----------+------------------+
|       1 | john     | [email protected]   |
|       2 | emily    | [email protected]  |
|       3 | monica   | [email protected] |
|       4 | clark    | [email protected]  |
+---------+----------+------------------+

mysql> SELECT * FROM orders;
+----------+---------+-------------+------------+
| order_id | user_id | order_total | order_date |
+----------+---------+-------------+------------+
|        1 |       1 |         500 | 2019-07-01 |
|        2 |       1 |         400 | 2019-06-01 |
|        3 |       4 |        1000 | 2018-07-15 |
|        5 |       3 |         600 | 2019-06-05 |
+----------+---------+-------------+------------+

mysql> SELECT * FROM user_description;
+----------------+---------+----------+---------+
| description_id | user_id | city     | country |
+----------------+---------+----------+---------+
|              1 |       1 | Delhi    | India   |
|              2 |       2 | New York | USA     |
|              3 |       3 | Mumbai   | India   |
|              4 |       4 | Delhi    | India   |
+----------------+---------+----------+---------+

Examples

Consider the following examples to learn more about SQL INNER JOIN.

Example 1: Join Two Tables

For instance, we want to fetch the username and email ids of the user who have placed an order. We can write an INNER JOIN Query on the order and user table.

SELECT o.order_id, u.user_id, u.email_id 
FROM orders AS o 
INNER JOIN users AS u ON o.user_id = u.user_id;
+----------+---------+------------------+
| order_id | user_id | email_id         |
+----------+---------+------------------+
|        1 |       1 | [email protected]   |
|        2 |       1 | [email protected]   |
|        5 |       3 | [email protected] |
|        3 |       4 | [email protected]  |
+----------+---------+------------------+

In the above query, we fetch the details of users who have placed an order. You can observe, the SQL INNER JOIN fetches those rows which have a match in both the tables. It does not return a row if it does not have a match in either of the tables. The user_id 4 does not have an order in the orders table and does not get returned.

Example 2: Join Three Tables

Similarly, we can also join three tables using SQL INNER JOIN. For instance, let’s also fetch data from the user_description table.

mysql> 
SELECT o.order_id, u.user_id, u.email_id, ud.city 
FROM orders AS o 
INNER JOIN users AS u ON o.user_id = u.user_id 
INNER JOIN user_description ud ON ud.user_id = u.user_id;
+----------+---------+------------------+---------+
| order_id | user_id | email_id         | city    |
+----------+---------+------------------+---------+
|        1 |       1 | [email protected]   | Delhi   |
|        2 |       1 | [email protected]   | Delhi   |
|        5 |       3 | [email protected] | Mumbai  |
|        3 |       4 | [email protected]  | Delhi   |
+----------+---------+------------------+---------+

The query returns the rows which have a match in all the three tables.

Conclusion

In conclusion, we discussed the SQL INNER JOIN. You can read more about it on Wikipedia. Additionally, you can also learn about SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *