I feel that I have been using the framework since my work, and some basic things of the database have been forgotten. This time I used this series of blogs to review the old knowledge and learn a little 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.