MySQL Basics tutorial 15 — Multiple table queries

The cartesian product

mysql> select * from user;
+----+------+--------+---------+
| id | name | gender | user_id |
+----+------+--------+---------+| | 1 ton male | | 1 | | 2 | bom female | | | 2 | 3 | Lin | | 3 | +----+------+--------+---
mysql> select * from foot;
+----+------+
| id | s    |
+----+------+| | 1 apple | | | | banana 2 | 3 | | + orange----+------+
3 rows in set (0.03SEC) // mysql>select * from user,foot;
+----+------+--------+---------+----+------+
| id | name | gender | foot_id | id | s    |
+----+------+--------+---------+----+------+| | 1 ton | | 1 | 1 male apple | | | | 2 female bom | | | | | 1 apple 2 | 3 | Lin | | 3 | 1 male apple | | | | 1 ton | | 1 | 2 male banana | | | 2 Bom female | 2 | 2 | | | banana | | 3 | Lin | 2 | 3 | | male banana | | | 1 ton | | | 3 | 1 male orange | | 2 | bom female | 2 | 3 | | orange | | 3 | Lin, male | | 3 | 3 | | + orange----+------+--------+---------+----+------+
9 rows in set (0.03 sec)
Copy the code

Taking these two tables as examples, the Cartesian product phenomenon is that each record in Table A matches each record in Table B to form A new record. For example, table A has three records and Table B has three records. When combined, the result is 3*3(9) records.

In the connection

The Cartesian product will display data that we don’t need, so we can filter out the conditions that we don’t need.

Implicit inner join

Select * from table1 where table2;

mysql> select * from user as u,foot as f where u.foot_id = f.id;
+----+------+--------+---------+----+------+
| id | name | gender | foot_id | id | s    |
+----+------+--------+---------+----+------+| | 1 ton | | | | | 1 apple 1 male bom female | 2 | 2 | 2 | | | | banana | 3 | Lin | | 3 | 3 | | + orange----+------+--------+---------+----+------+
3 rows in set (0.03 sec)
Copy the code

Explicit inner join

Select * from table1 [inner] join table2 on;

mysql> select * from user as u inner join foot as f on u.foot_id = f.id;
+----+------+--------+---------+----+------+
| id | name | gender | foot_id | id | s    |
+----+------+--------+---------+----+------+| | 1 ton | | | | | 1 apple 1 male bom female | 2 | 2 | 2 | | | | banana | 3 | Lin | | 3 | 3 | | + orange----+------+--------+---------+----+------+
3 rows in set (0.03 sec)
Copy the code

Implicit concatenation is relatively easy to understand and write, with simple syntax and fewer worrying points. But explicit joins can reduce field scans and have faster execution times. This speed advantage is evident when three or more tables are joined.

Outer join

Add a new record to user with field foot_id null.

mysql> select * from user;
+----+------+--------+---------+
| id | name | gender | foot_id |
+----+------+--------+---------+| | 1 ton male | | 1 | | 2 | bom female | | | 2 | 3 | Lin, male | 3 | | | | Ken | | NULL | female + 4----+------+--------+---------+
4 rows in set (0.04 sec)
mysql> select * from user,foot where user.foot_id = foot.id;
+----+------+--------+---------+----+------+
| id | name | gender | foot_id | id | s    |
+----+------+--------+---------+----+------+| | 1 ton | | | | | 1 apple 1 male bom female | 2 | 2 | 2 | | | | banana | 3 | Lin | | 3 | 3 | | + orange----+------+--------+---------+----+------+
3 rows in set (0.04 sec)
Copy the code

At this time, the inner join will find that the condition field is empty, so we can use the outer join to solve.

Left outer join (left table all and left table intersection)

Select * from 表1 left [outer] join 表2 on;

mysql> select u.*,f.* from user as u left outer join foot as f on u.foot_id = f.id;
+----+------+--------+---------+------+------+
| id | name | gender | foot_id | id   | s    |
+----+------+--------+---------+------+------+| | 1 ton | | | | | 1 apple 1 male female | | 2 | | bom banana | 2 | 2 | 3 | | Lin | | 3 | 3 | orange male | | | 4 Ken | | NULL | NULL | NULL | +----+------+--------+---------+------+------+
4 rows in set (0.03 sec)
Copy the code

Right outer join (right table all and left table intersection)

Select * from 表1 right [outer] join 表2 on;

mysql> select f.*,u.* from user as u right outer join foot as f on u.foot_id = f.id;
+----+------+----+------+--------+---------+
| id | s    | id | name | gender | foot_id |
+----+------+----+------+--------+---------+| | | | 1 apple 1 ton male | | 1 | | 2 | 2 | | banana bom female | | | 2 | 3 | | 3 | orange Lin | | 3 | +----+------+----+------+--------+---------+
3 rows in set (0.03 sec)
Copy the code

Id 4 in user is not displayed because there is no null value for foot_id in foot.

Left outer join is essentially the same as right outer join by changing the position of the table, for example:

// left outer joinselect * from user as u left outer join foot as f onu.foot_id = f.id; // right outer joinselect * from foot as f right outer join user as u on u.foot_id = f.id; 
Copy the code

These two SQL statements execute in the same way.

Since the connection

Self – connections can be used with inner – outer connections.

mysql> select * from yg;
+----+------+------+
| id | name | t_id |
+----+------+------+| | 1 zhang SAN | NULL | | 2 | li si | 1 | | 3 | fifty | 1 | | | | | 2 bear + 4----+------+------+
4 rows in set (0.05 sec)
Copy the code

The ID corresponding to the t_id field indicates the superior relationship. To query the corresponding superior of each person, you need to use the self-link.

mysql> select a.name,b.name from yg as a left outer join yg as b on a.t_id = b.id;
+------+------+
| name | name |
+------+------+| |, dick, and harry zhang SAN | | fifty | zhang SAN | | bear big | |, dick, and harry | NULL | | zhang SAN +------+------+
4 rows in set (0.03 sec)
Copy the code

Self-join is essentially a table with two different aliases used by two tables.

The joint query

When we query multiple tables, we can use a federated query to bring the data together on a single table.

Select columns from table 1.... Union [all] select * from table 2... ;

Suppose we want to query the information of person 2 in the user table and person 3 in the YG table together in one table.

mysql> select u.id,u.name,u.gender from user as u where id = 2
    -> union all 
    -> select * from yg where id = 3;
+----+------+--------+
| id | name | gender |
+----+------+--------+| | 2 female bom | | | 3 | fifty and | | + 1----+------+--------+
2 rows in set (0.03 sec)
Copy the code

Note: The number of fields in the field list after multiple select should always be.

In these cases, the results of the two queries will overlap. If we do not want to have duplicate data, we can remove all after union.

The subquery

Scalar quantum query

Select * from table 1 where a = (select a from the where condition (table 2).

If we want to query the fruit in the foot table for a person with ID 1 in the user table.

mysql> select user.foot_id from user where id = 1; 
+---------+
| foot_id |
+---------+| | + 1---------+
1 row in set (0.03 sec)
mysql> select foot.s from foot where id = 1;
+------+
| s    |
+------+| | + apple------+
1 row in set (0.02SEC) // mysql>select foot.s from foot where id = (select user.foot_id from user where id = 1);
+------+
| s    |
+------+| | + apple------+
1 row in set (0.04 sec)
Copy the code

Example query

The data in the subquery is columns.

Select * from table 1 where a in (select a from table 2 where condition)

Select * from table1 where a > all/some/any(select * from table2 where a > all/some/any);

In /not in/any/some/all, where any is the same as some.

Select * from foot where user = ‘user’;

mysql> select * from user where id in (select foot.id from foot);
+----+------+--------+---------+
| id | name | gender | foot_id |
+----+------+--------+---------+| | 1 ton male | | 1 | | 2 | bom female | | | 2 | 3 | Lin | | 3 | +----+------+--------+---------+
3 rows in set (0.02 sec)
Copy the code

Muck query

The data in the subquery is rows.

Select * from table_name where (a,b) = (select a,b from table_name);

mysql> select * from user;
+----+------+--------+---------+
| id | name | gender | foot_id |
+----+------+--------+---------+| | threes male | | 1 | | | | 2, dick, and harry female | | 2 | 3 | fifty and male | 3 | | | | 4 bear big female | NULL | | +----+------+--------+---------+
4 rows in set (0.03 sec)
mysql> select * from yg;
+----+------+------+
| id | name | t_id |
+----+------+------+| | 1 zhang SAN | NULL | | 2 | li si | 1 | | 3 | fifty and | | + 1----+------+------+
3 rows in set (0.03SEC) // mysql>select * from user where (id.name) in (select yg.id,yg.name from yg);
+----+------+--------+---------+
| id | name | gender | foot_id |
+----+------+--------+---------+| | threes male | | 1 | | | | 2, dick, and harry female | | 2 | 3 | fifty and | | 3 | +----+------+--------+---------+
3 rows in set (0.03 sec)
Copy the code

A table subquery

Select * from 表名 where (a,b) in (select a,b from 表名 where name = 'bom' or name = 'Tom ');

mysql> select * from user where (user.id,user.name) in (select yg.id,yg.name from yg where name = 'Joe' or name = 'Cathy');
+----+------+--------+---------+
| id | name | gender | foot_id |
+----+------+--------+---------+| | threes male | | 1 | | 3 | fifty and | | 3 | +----+------+--------+---------+
2 rows in set (0.04 sec)
Copy the code

You can also use the queried subtable as the main clause query table, for example:

Select e.*, d.* from (select * from table where date > '2006-5-23') e left join dept d on e.dept_id = d. ID;

(Click to enter the column for a detailed tutorial)