RIGHT JOIN in SQL

RIGHT JOIN in SQL is a type of JOIN that returns all records from the right side table (table 2) and matching records from the LEFT side table (table 1). If the ON clause does not match any record in the left table, then RIGHT JOIN will still return a row in the result, but the value is NULL in each column from the left table.

This means that the RIGHT JOIN returns all values ​​from the right table, plus the appropriate values ​​from the left table or NULL in the case of no matching values.

1. JOIN clause in SQL

Syntax RIGHT JOIN in SQL

The basic syntax of RIGHT JOIN is as follows:

 SELECT cot1, cot2,. cotn 
 FROM bang1 
 RIGHT 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 RIGHT 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 the RIGHT JOIN as follows:

 SQL> SELECT ID, TEN, SOTIEN, NGAY 
 FROM NHANVIEN 
 RIGHT 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. INNER JOIN - Returns records with matching values ​​between two tables.
2. LEFT JOIN - Returns all records from the left panel and matching records from the right 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.