MySQL Multi-Table query

mysql--Multi-Table query

Connection query:
Cross Connect:
Result: Back basics The number of data rows in the first table, multiplied by the compound query criteria in the second table.
Syntax format: Select query content from table 1 cross join table 2;
Mysql> select * from Boy;
+------+--------+
| HID | bname |
+------+--------+
| A | Apple |
| B | Banana |
| C | Pear |
+------+--------+
3 Rows in Set (0.00 sec)

Mysql> select * from girl;
+------+-----------+
| HID | Gname |
+------+-----------+
| B | Lurry |
| C | Aliea |
| D | Dead House Girl |
+------+-----------+
3 Rows in Set (0.00 sec)

Mysql> SELECT * from the boy cross join girl;
+------+--------+------+-----------+
| HID | bname | HID | Gname |
+------+--------+------+-----------+
| A | Apple | B | Lurry |
| B | Banana | B | Lurry |
| C | Pear | B | Lurry |
| A | Apple | C | Aliea |
| B | Banana | C | Aliea |
| C | Pear | C | Aliea |
| A | Apple | D | Dead House Girl |
| B | Banana | D | Dead House Girl |
| C | Pear | D | Dead House Girl |
+------+--------+------+-----------+
9 Rows in Set (0.00 sec)
The result of a cross join is a combination of all the data in two tables


Internal connections: Also known as simple or natural connections
In an in-connection query, only records that meet the criteria appear in the query results
Syntax format: Select query content from table 1 join table 2 on table 1. Relationship field = Table 2. Relationship Fields
Mysql> select * from con;
+------+------+
| ID | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+------+------+
4 rows in Set (0.00 sec)

Mysql> select * from COM;
+------+------+
| ID | name |
+------+------+
| 1 | A |
| 2 | B |
| 5 | C |
| 7 | D |
+------+------+
4 rows in Set (0.00 sec)

Mysql> Select Con.name, com.name from con joins com on con.id = com.id;
+------+------+
| name | name |
+------+------+
| A | A |
| B | B |
+------+------+
2 rows in Set (0.02 sec)
You can also use the where statement to implement the same functionality
Mysql> Select Con.name, com.name from con, com where con.id = com.id;
+------+------+
| name | name |
+------+------+
| A | A |
| B | B |
+------+------+
2 rows in Set (0.00 sec)

External connection:
Result: Contains not only data that matches the query criteria and join criteria, but also the left table, the right table, or all the data in two tables.
Syntax format: Select query content from table 1 left|right join table 2 on table 1. Relationship field = Table 2. relationship field where condition;
Left join: Returns the result to all the records in the left table and the qualifying records in the right table
Right connection: Returns all records in the right table and qualifying records in the left table

Left join: Empty if a record in the left table does not exist in the right table
Mysql> Select Con.name, com.name from con-left join com on con.id = com.id;
+------+------+
| name | name |
+------+------+
| A | A |
| B | B |
| C | NULL |
| D | NULL |
+------+------+
4 rows in Set (0.01 sec)

Right connection: null if a record in the right table does not exist in the left table
Mysql> Select Con.name, com.name from con right join com on con.id = com.id;
+------+------+
| name | name |
+------+------+
| A | A |
| B | B |
| NULL | C |
| NULL | D |
+------+------+
4 rows in Set (0.00 sec)

You can make the results more accurate by adding constraints

Subquery:
    One query statement nested inside another query statement
        mysql> SELECT * from DEP;
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   1 | a     |
    |   2 | b     |
    |   3 | c     |
    |   5 | d     |
    +-----+-------+
    4 rows in Set (0.00 sec)

Mysql> select * from EMP;
+----+-------+------+-----+
| ID | name | Age | Did |
+----+-------+------+-----+
| 1 |   May |   20 | 1 |
| 2 |   HFDA |   22 | 1 |
| 3 |   SAEF |   20 | 2 |
| 4 |   Qwedd |   20 | 4 |
+----+-------+------+-----+
4 rows in Set (0.00 sec)

Where sub-query: The return value of the inner SQL is used as part of the conditional expression after the where
Example: SELECT * from TableA where ColA = (select ColB from TableB where ...);

Mysql> SELECT * from emp where do = (select Do from dep where Dname= ' A ');
+----+------+------+-----+
| ID | name | Age | Did |
+----+------+------+-----+
| 1 |   May |   20 | 1 |
| 2 |   HFDA |   22 | 1 |
+----+------+------+-----+
2 rows in Set (0.00 sec)

From sub-query: Inner SQL query result, as a table, for the outer SQL statement query again
Example: SELECT * FROM (SELECT * from ...) as TableName where ....

Mysql> SELECT * FROM (SELECT * to emp where age = a) as emps where did = 1;
+----+------+------+-----+
| ID | name | Age | Did |
+----+------+------+-----+
| 1 |   May |   20 | 1 |
+----+------+------+-----+
1 row in Set (0.00 sec)

Sub-query with in keyword:
Compare an inner query statement return value to an outer query statement
Syntax structure: Select query content from table 1 where did [not] in (select Do from table 2 where restriction)
Example:

Mysql> SELECT * from DEP where do in (select Do from emp where age=20);
+-----+-------+
| Did | Dname |
+-----+-------+
| 1 | A |
| 2 | B |
+-----+-------+
2 rows in Set (0.10 sec)
First, the AGE=20 data in the EMP table is isolated, and then the query criteria based on did as the outer query

Mysql> SELECT * from DEP where do not is in (select Do from emp where age=20);
+-----+-------+
| Did | Dname |
+-----+-------+
| 3 | C |
| 5 | D |
+-----+-------+
2 rows in Set (0.08 sec)
The not in keyword is the opposite of the query result using the in key word

Subquery with exists keyword:
Exists key word can be any one subquery, the role of this subquery is equivalent to testing, it does not produce any data, only return TRUE or
False, the outer query executes only if the return value is true.
Syntax structure: Select query content from table 1 where exists (select do from table 2 where restrictions)
Mysql> SELECT * from DEP where exists (SELECT * from EMP where age > 21);
+-----+-------+
| Did | Dname |
+-----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
| 5 | D |
+-----+-------+
4 rows in Set (0.00 sec)
Returns true because the AGE>21 data exists in the table EMP, executing the outer query
Because the EXISTS keyword is more efficient than the IN keyword, it is recommended to use exists when querying large data volumes

Keyword subquery with any:
The Any keyword means that any of these conditions are met, which allows you to create an expression to compare the list of return values for a subquery, as long as it satisfies the inner
Any one of the comparison criteria for a layer subquery, it returns a result as the outer query condition.
Syntax structure: Select query content from table 1 where field 1 relational operator any (select field 1 from table 2)
Mysql> SELECT * from DEP where do > any (select do from EMP);
+-----+-------+
| Did | Dname |
+-----+-------+
| 2 | B |
| 3 | C |
| 5 | D |
+-----+-------+
3 rows in Set (0.03 sec)
Detect all the did in the table EMP First, and then all of the DEP tables, as long as did is greater than any of the did in the query, will change the data isolated

subquery with the ALL keyword:
The all keyword is similar to the ANY keyword, but the all keyword indicates that all subqueries are required to return results
Syntax structure: Select query content from table 1 where field 1 relational operator all (select field 1 from table 2)
Mysql> SELECT * from DEP where do > All (select Do from EMP);
+-----+-------+
| Did | Dname |
+-----+-------+
| 5 | D |
+-----+-------+
1 row in Set (0.01 sec)


Union query: Display the union of all subquery results, typically no longer using order by in a subquery that is not an aggregate query

Mysql> select * from Boy;
+------+--------+
| HID | bname |
+------+--------+
| A | Apple |
| B | Banana |
| C | Pear |
+------+--------+
3 Rows in Set (0.00 sec)

    mysql> select * from girl;
    +------+-----------+
    | hid  | gname      |
    +------+-----------+
    | b    | banana    |
    | c    | aliea     |
    | d    | Dead House Girl     |
    +------+-----------+
    3 rows in Set (0.00 sec)

Mysql> SELECT * from Boy
Union
SELECT * FROM Girl;
+------+-----------+
| HID | bname |
+------+-----------+
| A | Apple |
| B | Banana |
| C | Pear |
| C | Aliea |
| D | Dead House Girl |
+------+-----------+
5 rows in Set (0.00 sec)

MySQL Multi-Table query