Commonly used basic operation syntax of mysql (8)~~Multiple table query merge results and inner join query [command line mode]

1. Use union and union all to merge two query results: select field name from tablename1 union select field name from tablename2;

Note that this operation must Ensure that the fields in the two tables are the same and the field data types are also the same. In addition, when using union, duplicate (identical) records will be removed, but union all will not.

Note: The following join queries involve the concept of Cartesian product, that is, if there are two tables, the first one has n records, and the other table has m records, then The number of records obtained by the Cartesian product is n*m; if the number of fields in the first table is a and the number of fields in the other table is b, then the number of fields obtained by the Descartes product is a+b .

2. Use natural join: The premise is that the two tables have the same fields: (This operation will remove duplicate fields)

For this query, my understanding is: retain the data that exists in the related fields (such as department_id here) in these two tables, and remove the data that exists in only one table:

As shown in the above content, there are four pieces of data in company and seven pieces in emp. As a result, no matter which one is placed first, only six pieces come out, because the part_id in company is 4 but not in emp, and the part_id in emp is 5. , and there is none in company.

3. Inner join on equivalent join query:

As I understand it, the difference between this operation and the natural join above is that it is not removed Repeated fields are more flexible and do not require fields with the same field name:

4. Inner join on unequal value join query:

My understanding of this query is that it is the complement of the "=" query, that is, the result set of this query = Cartesian product result set minus " ="The result set of the query; in this example, company has 4 records, emp has 7 records, and the Cartesian product is 4*7=28 records;"="The result of the query is 6 records, once"!= "The query should be for the remaining 22 records.

5. Another kind of query can be developed from the above operation: word connection query, that is, there is actually only one table, and it can be used as two by renaming:

As you can see, according to the Cartesian product, there should be 4*4=16 results, but because of the conditions, four results were filtered out.

The above is the common basic operation syntax of mysql (8)~~The contents of multi-table query merge results and inner join query [command line mode]. For more related content, please pay attention to the PHP Chinese website (www.php.cn )!