MySQL table: Examples of left join and RIGHT JOIN,

MySQL table: Examples of left join and RIGHT JOIN,

Left join syntax usage and Examples
MySQL left join syntax
SQL (MySQL) LEFT JOIN Retrieves all records from the LEFT table (table1) even if no matching record exists in the right table (table2. The basic syntax of left join is as follows:

... FROM table1 LEFT JOIN table2 ON condition ...

MySQL left join usage example
The following are two original data tables:
Articles:

User table:

We will list all the articles and their corresponding users, even if they are not.
The SELECT... left join... ON statement is as follows:

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid

The returned query result is as follows:

As you can see, the obvious difference with inner join is that all records in the left table are taken out, even if there is no matching record in the right table.
Prompt
Here, records are retrieved by "all", which is relative to the inner join restriction. In fact, you can add a WHERE condition or LIMIT keyword to the end of the preceding SQL statement to LIMIT the range of the result set like the general SQL statement.
IS NULL
In the preceding example, if no matched data record exists in the right table, all its columns are set to NULL, therefore, to query this part of the record (for example, in the above example, the search aid = 4 class does not have the corresponding user's article record), you can add the is null condition:

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid WHERE user.uid IS NULL

Right join syntax usage and Examples
MySQL right join syntax
SQL (MySQL) RIGHT JOIN Retrieves all records from the RIGHT table (table2) even if no matching record exists in the left table (table2. The basic syntax of right join is as follows:

... FROM table1 RIGHT JOIN table2 ON condition ...

MySQL right join usage example
The following are two original data tables:
Articles:

User table:

We list all users and articles they may have.
The SELECT... right join... ON statement is as follows:

SELECT article.aid,article.title,user.username FROM article RIGHT JOIN user ON article.uid = user.uid

The returned query result is as follows:

Compare the query results returned by left join, and the results returned by right join are exactly "Opposite ".
IS NULL
In the preceding example, if no matching data record exists in the left table, all columns are set to NULL, therefore, to query this part of the record (for example, in the above example, we find all users without corresponding articles such as username = Jack), we can append the is null condition:

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid WHERE article.aid IS NULL

Articles you may be interested in:

• Mysql left connection, right connection, and internal connection
• Simple optimization tutorial for table connection query in MySQL
• MySQL left join table JOIN tutorial
• MySQL basic multi-Table connection query tutorial