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.
- 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 :
- 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 | +----+----------+--------+---------------------+
See more types of JOIN:
- LEFT JOIN - Returns all records from the left panel and matching records from the right panel.
- RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
- FULL JOIN - Returns all records in the left panel and the table must be combined.
- 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.
- CARTESIAN JOIN - Returns the Cartesian product of record sets from two or more combined tables.
4 ★ | 1 Vote
You should read it
May be interested
- LEFT JOIN in SQLthe left join in sql is the type of join that returns all records from the left table and matching records from the right table.
- RIGHT JOIN in SQLright join in sql is a type of join that returns all records from the right side table and matching records from the left side table.
- FULL JOIN in SQLfull join in sql returns all records in the left table and the table must combine and fill in that null values for values do not match.
- SELF JOIN in SQLeach join operation must have two tables, but there are cases where you have to perform join on the same table, but you can call this join operation self join.
- CARTESIAN JOIN in SQLcartesian join in sql returns the cartesian product of record sets from two or more combined tables.
- NULL value in SQLnull in sql is the term used to represent a missing value.