JOIN in SQL Server

In SQL Server (Transact-SQL) JOIN is used to retrieve data from multiple tables, occurring when two or more tables are connected together in an SQL command. Here are 4 types of JOIN in SQL Server

1. INNER JOIN - also known as simple connection form
2. LEFT OUTER JOIN - or LEFT JOIN
3. RIGHT OUTER JOIN - or RIGHT JOIN
4. FULL OUTER JOIN - or FULL JOIN

Now let's look at the syntax, illustrations and examples to better understand JOIN in SQL Server.

INNER JOIN

Most likely you have already used INNER JOIN in certain commands. This is the most common type of connection, returning all rows from the tables when connection conditions are met.

INNER JOIN syntax

 SELEC T cot 
 FROM bang1 
 INNER JOIN bang2 
 ON bang1. cot = bang2.cot; 

JOIN in SQL Server Picture 1
Illustrated image of INNER JOIN

The returned result is the common records between state1 and bang2.

Example INNER JOIN

 SELECT nhacung.nhacung _id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 INNER JOIN donhang 
 ON nhacung.nhacung_id = d onhang.nhacung_id; 

The result returned in the above example is the row of information in the 'supplier' table and the 'order' table when the value of nhacung_id is equal to 2 tables.

Let's look at an example with specific data to better understand.

The nhacung table has 2 information fields: nhacung_id and nhacung_ten.

nhacung_id nhacung_ten 10000 IBM 10001 Hewlett Packard 10002 Microsoft 10003 NVIDIA

The donhang table consists of 3 fields donhang_id, nhacung_id and donhang_ngay.

donhang_id nhacung_id donhang_ngay 500125 10000 2003/05/12 500126 10001 2003/05/13 500127 10004 2003/05/14

If running the SELECT command (using INNER JOIN) below:

 SELECT nhacung. nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 INNER JOIN donhang 
 ON nhacung.nhacung_ id = donhang.nhacung_id; 

This is the result set returned:

nhacung_id ten IBM donhang_ngay 10000 2003/05/12 10001 Hewlett Packard 2003/05/13

Microsoft and NVIDIA products from the table are dropped because nhacung_id (10002 and 10003) is only available in 1 table. Row 500127 (donhang_id) from the donhang table is removed because nhacung_id 10004 is not in the nhacung table.

INNER JOIN's old syntax

INNER JOIN can be rewritten with the old syntax as below (but it is recommended to use the syntax that contains INNER JOIN keyword)

 SELECT music ung.nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM donhang, nhacung 
 WHERE nhacung.n hacung_id = donhang.nhacung_id; 

LEFT OUTER JOIN

Some databases use LEFT JOIN instead of LEFT OUTER JOIN. This type of connection returns the rows in the left panel in the ON condition and takes only the remaining tables that meet the set conditions.

Syntax LEFT JOIN

 SELE CT cot 
 FROM bang1 
 LEFT [OUTER] JOIN bang2 
 ON bang1.co t = bang2.cot; 

JOIN in SQL Server Picture 2
Illustrate LEFT JOIN as a result of the blue area

The returned result consists of state1 and records located in bang2 assigned to state1 (the blue area in the picture).

Example LEFT JOIN

 SELECT nh acung.nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 LEFT OUTER JOIN donhang 
 ON nhacung.nhac ung_id = donhang.nhacung_id; 

The example above will return all rows from the table and only those rows that meet the requirements in the donhang table. If the nhacung_id value in the table does not exist in the donhang table, all fields in donhang will display the result in the result table.

Below is a specific example. The table contains nhacung_id and nhacung_ten.

nhacung_id nhacung_ten 10000 IBM 10001 Hewlett Packard 10002 Microsoft 10003 NVIDIA

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

donhang_id nhacung_id donhang_ngay 500125 10000 2003/05/12 500126 10001 2003/05/13

If running the SELECT command below:

 SELECT nhacu ng.nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 LEFT OUTER JOIN donhang 
 ON nhacung.nhacun g_id = donhang.nhacung_id; 

The result will be like this:

nhacung_id nhacung_ten 10000 IBM donhang_ngay 2003/05/12 10001 Hewlett Packard 2003/05/13 10002 Microsoft 10003 NVIDIA

Microsoft and NVIDIA rows are still in the result set because they are using LEFT OUTER JOIN (get all tables on the left). Meanwhile, donhang_ngay of these records will be valid.

RIGHT OUTER JOIN

Some databases use RIGHT JOIN instead of RIGHT OUTER JOIN. This type of connection returns the rows in the right table in the ON condition and takes only the remaining tables that meet the set conditions.

Syntax RIGHT JOIN

 SELECT c ot 
 FROM bang1 
 RIGHT [OUTER] JOIN bang2 
 ON bang1.cot = bang2.cot; 

JOIN in SQL Server Picture 3
Illustration RIGHT JOIN

The returned result includes bang2 and the records are in state1 assigned to bang2 (the blue area in the picture).

Example RIGHT JOIN

 SELECT don hang.donhang_id, donhang.donhang_ngay, nhacung.nhacung_ten 
 FROM nhacung 
 RIGHT OUTER JOIN donhang 
 ON nhacung.nha cung_id = donhang.nhacung_id; 

The returned result is all the rows in the donhang table and only the rows that meet the requirements in the nhacung table.

If the value of nhacung_id in the donhang table does not exist in the table, all fields in the list will show up in the result table.

Below is a specific example. The table contains nhacung_id and nhacung_ten.

nhacung_id nhacung_ten 10000 Apple 10001 Google

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

donhang_id nhcung_id donhang_ngay 500125 10000 2003/08/12 500126 10001 2003/08/13 500127 10002 2003/08/14

If running the SELECT command below:

 SELECT donhang.don hang_id, donhang.donhang_ngay, nhacung.nhacung_ten 
 FROM nhacung 
 RIGHT OUTER JOIN donhang 
 ON nhacung.nhacung_id = donhang.nhacung_id; 

The result will be like this:

donhang_id donhang_ngay nhacung_id 500125 2003/08/12 Apple 500126 2003/08/13 Google 500127 2003/08/14

Row 500127 (donhang_id) is still in the result table because the RIGHT OUTER JOIN is in use. Note that nhacung_ten for that record contains value.

FULL OUTER JOIN

Some databases use FULL JOIN instead of FULL OUTER JOIN. This connection type returns all rows in the left and right tables and NULL values ​​set in conditions that do not meet the conditions.

Syntax FULL JOIN

 SELE CT cot 
 FROM bang1 
 FULL [OUTER] JOIN bang2 
 ON bang1.c ot = bang2.cot; 

JOIN in SQL Server Picture 4
Illustrate FULL JOIN with the result of a green area

The returned result includes all records from bang1 and bang2.

Example FULL JOIN

 SELECT nhacung. nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 FULL OUTER JOIN donhang 
 ON nhacung.nhacung_i d = donhang.nhacung_id; 

The above example will return all rows from the table and donhang, but when the condition is not met, the value will be set to the result table.

If nhacung_id in the music panel is not in the donhang table, or the value nhacung_id in the donhang table is not in the music panel, the information fields will be displayed.

Below is a specific example of FULL OUTER JOIN. The table contains nhacung_id and nhacung_ten.

nhacung_id nhacung_ten 10000 IBM 10001 Hewlett Packard 10002 Microsoft 10003 NVIDIA

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

donhang_id nhacung_id donhang_ngay 500125 10000 2003/08/12 500126 10001 2003/08/13 500127 10004 2003/08/14

If running the SELECT command below:

 SELECT nhacung .nhacung_id, nhacung.nhacung_ten, donhang.donhang_ngay 
 FROM nhacung 
 FULL OUTER JOIN donhang 
 ON nhacung.nhacung_i d = donhang.nhacung_id; 

The result will be like this:

nhacung_id nhacung_ten 10,000 donhang_ngay IBM 2003/08/12 10001 Hewlett Packard 2003/08/13 10002 Microsoft 10003 NVIDIA 2003/08/14

Microsoft and NVIDIA products are still in the result set because they are using FULL OUTER JOIN. Then donhang_ngay of these records contains values. The row for nhacung_id is 10004 which is also in the results table but the nhacung_id and nhacung_ten of these records contain values.

Previous article: ALIAS in SQL Server

Next article: BETWEEN conditions in SQL Server