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; 

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.

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

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:

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; 

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.

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

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:

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; 

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.

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

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:

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; 

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.

Donhang table includes donhang_id, nhacung_id and donhang_ngay.

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:

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.