“This is the fifth day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

MySQL contains two types of joins, inner join and out join, but we have heard of the terms left join and cross join. This article aims to summarize the relationships between these terms.

1. The inner connection

An important property of an inner join is that the result of an inner join query is independent of the order of the table (of course the order may change, but the correspondence is never out of order!!)

1.1 Cross Join

Of course, it has other names, such as cartesian product, cross product, and the strangest of all, no Join.

Use the following command to query both the toy column of the toy table and the boy column of the boys table, resulting in a cross join

SELECT t.toy,b.boy
FROM toys AS t
	CROSS JOIN
	boys AS b;
Copy the code

Where, CROSS JOIN can be omitted and abbreviated as

SELECT t.toy,b.boy
FROM toys AST, boysAS b;
Copy the code

Cross-join back matches each value of the first table with each value of the second table, resulting in the following result

A cross join is a type of inner join, which you can also think of as a cross join after some rows of result data have been filtered out by criteria in the query.

1.2 Equal Connection

Let’s assume that each boy has another toy, that the tables are one-to-one, that toy_id is a foreign key, and that the database table is shown below

If we want to find out what toys each boy has, we can compare the toy_id in boys with the primary key in Toys

SELECT boys.boy,toys.toy
FROM boys
	INNER JOIN
	toys
ON boys.toy_id=toys.toy_id;
Copy the code

1.3 Unequal Connection

We continue with the table structure in 1.2. If we want to find a toy that every boy doesn’t have, we can use the unequal join (in other words = <>, otherwise no difference).

SELECT boys.boy,toys.toy
FROM boys
	INNER JOIN
	toys
ON boys.toy_id<>toys.toy_id
ORDER BY boys.boy;
Copy the code

1.4 Natural Connection

Continue with the 1.2 table structure…

Note: A natural join is only useful if the joined column has the same name in both tables

In essence, a natural join automatically recognizes equal joins of the same column

SELECT boys.boy,toys.toy
FROM boys
	NATURAL JOIN
	toys
ORDER BY boys.boy;
Copy the code

The results are exactly the same as those in 1.2 (maybe in a different order)

2. The outer join

First, an outer join differs from an inner join in that the outer join query is related to the order of the table

2.1 Left external connection

The LEFT OUTER JOIN receives all the rows from the LEFT table and matches them with the right table

Left-outer joins are especially useful when the left table has a one-to-many relationship with the right table. We still use the same table structure as before

Now we use the outer left link to find out which toys each boy has

SELECT b.boy,t.toy
FROM boys b
LEFT OUTER JOIN toys t
ON b.toy_id=t.toy_id;
Copy the code

LEFT OUTER JOIN (boys) on the LEFT; toys on the right; toys on the right

Our query results are the same as when we used inner joins. No way!! Let’s change the structure of the left table boys

We added a new Andy to boys and set his toy_id to 6

Note that 6 does not correspond to toys in the toys table, then run the above program again

We found a NULL, which tells us that there is no row in toys in the right table that matches Andy in boys in the left table

An outer join must provide rows of data, whether or not a row can be found matching in another table

Next, we switch the order of the left and right tables

SELECT b.boy,t.toy
FROM toys t
LEFT OUTER JOIN boys b
ON b.toy_id=t.toy_id;
Copy the code

Conclusion: NULL columns are always columns in the right table

Below is the actual matching process for the left outer join

Toys. toys_is=1,boys. Toy_is =3

Toys. toys_is=1, and boys.toy_is=5

Toy Hula Hoop in Toys was compared to Beaver in boys, toys.toys_is=1,boys. Toy_is =2

The records of Toy Hula Hoop in Toys and Richie in boys were compared. Toys. toys_is=1 and boys. Toy_is =1 were successfully matched

. Omit the matching process with toy_id=2,3

Toys_is =4 and boys.toy_is=3 did not match harmonica’s records with Davey’s in Boys

When comparing the records of Bobby from Toys with those from boys, Toys.toys_is =4 and boys.toy_is=5 did not match harmonica

Toys harmonica compared the records of Beaver in boys to Toys.toys_is =4 and Boys.toy_is =2

When comparing the records of Toys harmonica and Richie in boys, toys.toys_is=4 and boys.toy_is=1 did not match

Create a row with a NULL value for harmonica

. Omit the matching process of baseball cards

2.2 Right outer connection

Exactly the same as the left outer join, except that the left table is evaluated by the right table

Select * from RIGHT OUTER JOIN (！！！！！)

Here is a brief example, refer to the left outer join

SELECT b.boy,t.toy
FROM toys t
RIGHT OUTER JOIN boys b
ON b.toy_id=t.toy_id;
Copy the code

The above code is equivalent to

SELECT b.boy,t.toy
FROM boys b
LEFT OUTER JOIN toys t
ON b.toy_id=t.toy_id;
Copy the code

In both cases, toys is on the right and boys is on the left

The table structure of the experiment is as follows:

Results:

In practice, we tend to use only one, and the other is easy to understand.