INNER JOIN in SQL

INNER JOIN in SQL is the most important and often used JOIN type. This is a query clause with a return of a set of data that satisfies the general condition from two tables. This query compares each row in state1 with each row in bang2 to find the matching pairs of conditions.

INNER JOIN in SQL Picture 1

1. JOIN clause in SQL

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 :

1. cot1, cot2, . cotn : the names of the columns to display in the query results. Cots are separated by commas (,)
2. bang1, bang2 : table names to retrieve data when querying.
3. 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 | +----+----------+--------+---------------------+ 

See more types of JOIN:

1. LEFT JOIN - Returns all records from the left panel and matching records from the right panel.
2. RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
3. FULL JOIN - Returns all records in the left panel and the table must be combined.
4. SELF JOIN - Combine a table with itself as if the table is considered two tables, change the temporary name at least one table in the SQL command.
5. CARTESIAN JOIN - Returns the Cartesian product of record sets from two or more combined tables.