HomeProgramSQL

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.

The clause to combine JOIN data in SQL Picture 1

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

5 ★ | 1 Vote
learn sqljoin types in sqljoin sort in sql

You should read it

May be interested

  • The ORDER BY clause in SQL ServerThe ORDER BY clause in SQL Server
    the order by clause is used to filter records in the result set.
  • GROUP BY clause in SQL ServerGROUP BY clause in SQL Server
    the group by clause in sql server (transact-sql) is used in the select statement to retrieve data from multiple records and result groups into 1 or more columns.
  • Sort results in SQLSort results in SQL
    to sort data in sql, we use the order by clause.
  • Guide to blending clouds into beautiful and simple photosGuide to blending clouds into beautiful and simple photos
    with this application, you can pair the clouds into photos, blend the sky into photos in the easiest way
  • How to combine 2 columns Full name in Excel does not lose contentHow to combine 2 columns Full name in Excel does not lose content
    in excel, to be able to merge content in 2 columns into a single column without losing content, we need to use calculation functions.
  • How to combine 2 or more cells in Excel without losing dataHow to combine 2 or more cells in Excel without losing data
    how to combine 2 or more cells in excel without losing data. when merging cells containing data into 1 cell, excel retains the data in the first cell only, data in the remaining cells are deleted. so to retain all data in the cells you need to merge, you cannot use the feature
  • How to combine IF, AND and OR functions to filter dataHow to combine IF, AND and OR functions to filter data
    how to combine if, and and or functions to filter data if you only use the if function, the and function and the or function, you cannot see the full usefulness of these functions in excel. the following article will combine the if function, the and function and the or function to make jars
  • JOIN in SQL ServerJOIN in SQL Server
    join is used to retrieve data from multiple tables, occurring when two or more tables are connected together in an sql statement.
  • HAVING clause in SQLHAVING clause in SQL
    the having clause in sql is used to filter records and retrieve only those records that match the requirements or are actually needed.
  • SELF JOIN in SQLSELF JOIN in SQL
    each 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.

SQL