INNER JOIN in SQL

This guide covers inner join in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

INNER JOIN Syntax in SQL

The basic Inner Join syntax is as follows:

SELECT cot1, cot2,. cotn
FROM bang1
INNER JOIN bang2
ON bang1.cot_chung = bang2.cot_chung;

Parameters :

• Cot1, cot2, . Cotn : the names of the columns to display in the query results Cots Are separated by commas (, )
• Bang1, bang2 : table names to retrieve data when querying.
• Cot_chung: Usually the foreign key column name Referring to Bang1 To the identifier column in Bang2 Or vice versa.

Example of INNER JOIN in SQL

Suppose the two tables are NHANVIEN And TIENTHUONG With the following records:

Table 1: NHANVIEN

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

Table 2: TIENTHUONG

 +-----+---------------------+-------------+--------+ |TT_ID| NGAY | NHANVIEN_ID | SOTIEN | +-----+---------------------+-------------+--------+ | 102 | 2019-01-08 00:00:00 | 3 | 3000 | | 100 | 2019-01-08 00:00:00 | 3 | 1500 | | 101 | 2019-02-20 00:00:00 | 2 | 1560 | | 103 | 2018-12-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ 

Now, let's join these two tables using INNER JOIN as follows:

SQL> SELECT ID, TEN, SOTIEN, NGAY
 FROM NHANVIEN
 INNER JOIN TIENTHUONG
 ON NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID;

The result is:

 +----+----------+--------+---------------------+ | ID | TEN | SOTIEN | NGAY | +----+----------+--------+---------------------+ | 3 | Nga | 3000 | 2019-01-08 00:00:00 | | 3 | Nga | 1500 | 2019-01-08 00:00:00 | | 2 | Loan | 1560 | 2019-02-20 00:00:00 | | 4 | Manh | 2060 | 2018-12-20 00:00:00 | +----+----------+--------+---------------------+