Detailed explanation of various join summaries of SQL

SQL Left Join, Right Join, Inner Join, and Natural Join Summary of Various Joins

In SQL, there are various Joins, including Left Join, Right Join, Inner Join, and Natural Join. Beginners must be confused about what they are. What are the differences and connections between them? Let's use a picture to illustrate:

The above picture illustrates the difference between Left Join, Right Join, Inner Join, and Full Outer Join. Let's use a simple example to help us understand and distinguish them. Now there are two tables Person and Address:

-- Table Person
+ -- --------+-----------+----------+
| PersonId | FirstName | LastName |
+ -- --------+-----------+----------+
| 1 | Zhang | San |
| 2 | Li | Si |
| 3 | Wang | Wu |
| 4 | Yang | Liu |
+ -- --------+-----------+----------+

--Table Address
+ ---------- +----------+---------------+-------+
| AddressId | PersonId | City | State |
+ ---------- +----------+---------------+-------+
| 1 | 2 | San Francisco | CA |
| 2 | 3 | Los Angeles | CA |
| 3 | 1 | San Diego | CA |
+ ---------- +----------+---------------+-------+

Let’s look at each one of them below:

Left Join： returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

Left intersection: Returns all rows of the left table and the matching rows of the right table. If there is no match, use NULL.

SELECT * FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City | State |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| 2 | Li | Si | 1 | 2 | San Francisco | CA |
| 3 | Wang | Wu | 2 | 3 | Los Angeles | CA |
| 1 | Zhang | San | 3 | 1 | San Diego | CA |
| 4 | Yang | Liu | NULL | NULL | NULL | NULL |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+

Right Join: returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Right intersection: Returns all rows of the right table and the matching rows of the left table. If there is no match, use NULL.

SELECT * FROM Person RIGHT JOIN Address ON Person.PersonId = Address.PersonId;
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City | State |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| 1 | Zhang | San | 3 | 1 | San Diego | CA |
| 2 | Li | Si | 1 | 2 | San Francisco | CA |
| 3 | Wang | Wu | 2 | 3 | Los Angeles | CA |
| NULL | NULL | NULL | 4 | 5 | Memphis | TN |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+

Inner Join: selects all rows from both tables as long as there is a match between the columns in both tables.

Internal intersection: Select the rows where the keywords match in the left and right tables.

SELECT * FROM Person INNER JOIN Address ON Person.PersonId = Address.PersonId; + -- --------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City | State |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| 1 | Zhang | San | 3 | 1 | San Diego | CA |
| 2 | Li | Si | 1 | 2 | San Francisco | CA |
| 3 | Wang | Wu | 2 | 3 | Los Angeles | CA |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+

Full Join: returns all rows from the left table (table1) and from the right table (table2), and it combines the result of both LEFT and RIGHT joins.

Full intersection: Returns all rows of the left table and all rows of the right table, which is the union of the left intersection and the right intersection.

Note that since there is no Full Join command in MySQL, we can also UNION the results of Left Join and Right Join:

SELECT * FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId UNION
SELECT * FROM Person RIGHT JOIN Address ON Person.PersonId = Address.PersonId; + -- --------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City | State |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+
| 2 | Li | Si | 1 | 2 | San Francisco | CA |
| 3 | Wang | Wu | 2 | 3 | Los Angeles | CA |
| 1 | Zhang | San | 3 | 1 | San Diego | CA |
| 4 | Yang | Liu | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | 5 | Memphis | TN |
+ -- --------+-----------+----------+-----------+-----------+---------------+-------+

Natural Join: creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join , a LEFT OUTER join , or a RIGHT OUTER join . The default is INNER join .

Natural intersection: Create an implicit join operation based on the common columns of the left and right tables. The common columns are the two columns with the same column names in the two tables. Natural intersection can be inward intersection, left intersection or right intersection. The default is internal.

SELECT * FROM Person NATURAL JOIN Address;

+ -- --------+-----------+----------+-----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | City | State |
+ -- --------+-----------+----------+-----------+---------------+-------+
| 1 | Zhang | San | 3 | San Diego | CA |
| 2 | Li | Si | 1 | San Francisco | CA |
| 3 | Wang | Wu | 2 | Los Angeles | CA |
+ -- --------+-----------+----------+-----------+---------------+-------+

Finally, note that the keywords on both sides of the equal sign below are equivalent:

A LEFT JOIN B = A LEFT OUTER JOIN B
A RIGHT JOIN B = A RIGHT OUTER JOIN B
A FULL JOIN B = A FULL OUTER JOIN B
A INNER JOIN B = A JOIN B

This is the end of this article on the detailed explanation of various join summaries of SQL. For more relevant content on various join summaries of SQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!