MySQL learning -- join and union usage _ MySQL

MySQL learning-the use of join and union I feel I have been using the framework since work, and some basic things of the database have been forgotten. I will review the old knowledge through this series of blogs, learn new knowledge.

Today we will start with join and union.

Join is a record set generated by the records with the same conditions in the two tables after the join operation,
Union is the combination of two records to form a new record set.

Join

The following figure shows the join command.

Union

Required: the number of columns in the two queries must be consistent.

Recommendation: the types of columns can be different, but we recommend that you query each column, and you want the corresponding types to be the same

Data from multiple tables: the names of the columns retrieved from multiple SQL statements may be inconsistent. the column names of the first SQL statement shall prevail.

If the rows extracted from different statements are identical (the values of each column are the same), union merges the same rows and retains only one row. We can also understand that union removes duplicate rows.

If you do not want to remove duplicate rows, you can use union all.

If the clause contains order by and limit, enclose them with parentheses. It is recommended to put all the clauses, that is, sort or filter the final merged results.

For example: (select * from a order by id) union (select * from B order id );

In a clause, order by must be used with limit to make sense. If it is not used with limit, it will be removed when the syntax analyzer optimizes the analysis.