This is the 8th day of my participation in Gwen Challenge
preface
Join (join) join (join) join (join) join (join) join (join) join (join) join (join) join (join) In database, the usage of JOIN is mainly divided into three kinds, namely left join, right join and inner join. But in practice, there are altogether seven operations between two tables, so let’s start to understand these seven usages today
All of the following ellipses represent two different tables. Assume that table TEST1 is on the left and table Test2 is on the right
Table test1 has three columns: UID, name, and locale
Table test2 has four fields: UID, name, gender, and age
1. All the connection
As shown in the figure, the two tables are fully connected to query. Assume that table A has M records and table B has N records. When table A is fully connected, cartesian product is used to calculate, so M×N records can be queried
Test1; test2; test2
SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid UNION SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid;
Copy the code
Select * from both tables where no data exists
Summary: The contents of the query are all contents of the two tables
2. Go to the intersection
As shown in the figure, the two tables are queried by intersection connection, and the intersection part in the middle is removed from the full connection of the two tables
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid WHERE test2.uid IS NULL
UNION SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid WHERE test1.uid IS NULL
Copy the code
Summary: The contents of the query are the contents of the two tables that have no common parts
3. The left connection
As shown in the figure, these two tables are left join query, according to the same part to add the remaining content of the left table
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid;
Copy the code
If there is any data in the right table, it will be detected. If there is no data in the right table, it will be null.
Mainly see which table is given priority to, here is the left link is given priority to the left table, the data of the left table are queried out, the right table has data is displayed, no is empty
Summary: Query the common portion of both tables plus the remaining portion of the left table
4. The right connection
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid =test2.uid;
Copy the code
Mainly see which table is given priority to, here is the left link is given priority to the left table, the data of the left table are queried out, the right table has data is displayed, no is empty
Summary: Query the common part of the two tables plus the rest of the right table
5. The connection
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 INNER JOIN test2 ON test1.`uid` = test2.`uid`;
Copy the code
Summary: The contents of the query are common to both tables
6. Left alone
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid WHERE test2.uid IS NULL;
Copy the code
Summary: Query the content of the left table does not exist in the right table
7. To the right
SQL statement and result of intersection join for test1 and test2
SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid WHERE test1.uid IS NULL;
Copy the code
Summary: query the content of the right table does not exist in the left table