The clause to combine JOIN data in SQL

In SQL, the JOIN clause is used to combine records from two or more tables in a Database using common values ​​from each table.

In this article, Quantrimang will give you detailed instructions on how to use the JOIN clause in SQL with specific examples to make it easier to visualize and capture commands better.

Assuming there are two tables, NHANVIEN and TIENTHUONG have 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, we combine these two tables in the SELECT statement as follows:

 SQL> SELECT ID, TEN, TUOI, SOTIEN 
  FROM NHANVIEN, TIENTHUONG 
  WHERE NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID; 

The result is:

 +----+----------+-----+--------+ | ID | TEN | TUOI| SOTIEN | +----+----------+-----+--------+ | 3 | Nga | 23 | 3000 | | 3 | Nga | 23 | 1500 | | 2 | Loan | 25 | 1560 | | 4 | Manh | 25 | 2060 | +----+----------+-----+--------+ 

You can see here that JOIN is executed in the WHERE clause. Some operators can be used to combine tables: =, <,>, <>, <=,> =,! =, BETWEEN, LIKE, and NOT. However, the most commonly used operator is the equal sign (=).

JOIN types in SQL

In SQL, there are several types of JOINs available:

JOIN type Meaning INNER JOIN Returns records with matching values ​​between two tables. LEFT JOIN Returns all records from the left panel and matching records from the right panel. The result is NULL from the right side if there is no match. RIGHT JOIN Returns all records from the right panel and the appropriate records from the left panel. The result is NULL from the left side if there is no matching result. FULL JOIN Returns all records in the left table and the table must be combined. SELF JOIN Used to combine a table with itself as if the table is considered two tables, replace at least one temporary table name in the SQL statement. CARTESIAN JOIN Returns the Cartesian product of record sets from two or more combined tables.

In the following articles, Quantum will discuss with you more about each of the JOIN types mentioned above. Have you remembered to it!

Previous article: Constraints in SQL

Next lesson: UNION clause in SQL