MySQL data query connection query

• Connection relationships are the main features of the relational database model. It is also the most important query, mainly including inner joins, outer joins, etc.
• Multiple table queries can be implemented through connection operators.
• In a relational database management system, the relationship between each data does not need to be determined when the table is created. All information of an entity is often stored in one table. When querying data, the information stored in different entities in multiple tables is queried through connection operations. When there are fields with the same meaning in two or more tables, you can use these fields to perform join queries on different tables.

The following introduces three connection methods between multiple tables:

(1)Inner join query
(2)Outer join query
(3)Composite conditions Connection query

(free learning recommendation: mysql video tutorial)

(1), inner join query(inner join)

• Inner joins use comparison operators to compare data in certain (some) columns between tables, and list the data rows in these tables that match the join conditions to form new records, that is, in the inner join query , only records that meet the conditions can appear in the result relationship.

Create the data table suppliers below for demonstration:

mysql> create table suppliers    -> (
    -> s_id int not null auto_increment,
    -> s_name char(50) not null,
    -> s_city char(50) null,
    -> s_zip char(10) null,
    -> s_call char(50) not null,
    -> primary key(s_id)
    -> );Query OK, 0 rows affected (0.17 sec)mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call)
    -> values(101,'FastFruit Inc.','Tianjin','300000','48075'),
    -> (102,'LT Supplies','Chongqing','400000','44333'),
    -> (103,'ACME','Shanghai','200000','90046'),
    -> (104,'FNK Inc.','Zhongshan','528437','11111'),
    -> (105,'Good Set','Taiyuan','030000','22222'),
    -> (106,'Just Eat Ours','Beijing','010','45678'),
    -> (107,'DK Inc','Zhengzhou','450000','33332');Query OK, 7 rows affected (0.07 sec)Records: 7  Duplicates: 0  Warnings: 0

[Example 1] Use inner join query between fruits table and suppliers table.

mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| Field   | Type         | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id    | char(10)     | NO   | PRI | NULL    |       || s_id    | int(11)      | NO   |     | NULL    |       || f_name  | char(255)    | NO   |     | NULL    |       || f_price | decimal(8,2) | NO   |     | NULL    |       |+---------+--------------+------+-----+---------+-------+4 rows in set (0.06 sec)mysql> desc suppliers;+--------+----------+------+-----+---------+----------------+| Field  | Type     | Null | Key | Default | Extra          |+--------+----------+------+-----+---------+----------------+| s_id   | int(11)  | NO   | PRI | NULL    | auto_increment || s_name | char(50) | NO   |     | NULL    |                || s_city | char(50) | YES  |     | NULL    |                || s_zip  | char(10) | YES  |     | NULL    |                || s_call | char(50) | NO   |     | NULL    |                |+--------+----------+------+-----+---------+----------------+5 rows in set (0.00 sec)

You can see that both the fruits and suppliers tables have the same data type field s_id, and the two tables are connected through the s_id field.

Next, query the f_name and f_price fields from the fruits table, and query the s_id and s_name from the suppliers table. The SQL statement is as follows:

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits,suppliers    -> where fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

• Note: Because the fields in the fruits table and suppliers table have the same field s_id, so when comparing, the table name needs to be fully qualified in the format of "table name.column name". If only s_id is given, MySQL will not know which one it refers to and will return an error message.

[Example 2] Between the fruits table and suppliers table, use inner join syntax to perform inner join query. The SQL statement is as follows:

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits inner join suppliers    -> on fruits.s_id = suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

• Use where clause definition The connection conditions are relatively simple and clear, and the inner join syntax is the standard specification of ANSI SQL. Using the inner join connection syntax can ensure that the connection conditions are not forgotten, and the where clause will affect the performance of the query at some point.

If the two tables involved in a join query are the same table, this type of query is called a self-join query. Self-join is a special inner join, which means that the tables connected to each other are physically the same table, but can be logically divided into two tables.

[Example 3] To query the types of fruits provided by the fruit supplier supplying f_id='a1', the SQL statement is as follows:

mysql> select f1.f_id,f1.f_name    -> from fruits as f1,fruits as f2    -> where f1.s_id =f2.s_id and f2.f_id ='a1';+------+------------+| f_id | f_name     |+------+------------+| a1   | apple      || b1   | blackberry || c0   | cherry     |+------+------------+3 rows in set (0.00 sec)

(2), Outer join query

• Outer join query will query related rows in multiple tables.
• When inner joining, only the rows that meet the query conditions and connection conditions in the query result set are returned. But sometimes it is necessary to include data in unrelated rows, that is, the returned query result set not only includes rows that meet the join conditions, but also includes the left table (left outer join or left join), the right table (right outer join or right join) ) or all data rows in two edge tables (full outer join). Outer joins are divided into left outer joins or left joins and right outer joins or right joins:

①Left join: Returns all records in the left table and records with equal join fields in the right table.
②Right join: Return records including records in the right table that are equal to the join fields in the left table.

1. Left joinleft join

First create the table orders, the SQL statement is as follows;

mysql> create table orders    -> (
    -> o_num int not null auto_increment,
    -> o_date datetime not null,
    -> c_id int not null,
    -> primary key (o_num)
    -> );Query OK, 0 rows affected (0.11 sec)mysql> insert into orders(o_num,o_date,c_id)
    -> values(30001,'2008-09-01',10001),
    -> (30002,'2008-09-12',10003),
    -> (30003,'2008-09-30',10004),
    -> (30004,'2008-10-03',10005),
    -> (30005,'2008-10-08',10001);Query OK, 5 rows affected (0.06 sec)Records: 5  Duplicates: 0  Warnings: 0

[Example] In the customers table and orders table , query all customers, including customers without orders, the SQL statement is as follows:

mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     || 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotmail.com || 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              || 10004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date              | c_id  |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)mysql>  select customers.c_id , orders.o_num    -> from customers left outer join orders    -> on customers.c_id = orders.c_id;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 |  NULL |+-------+-------+5 rows in set (0.00 sec)

2. Right joinright join

• Right join is the inverse of left join A direct join will return all rows from the right table. If a row in the right table has no matching row in the left table, the left table will return a null value.

[Example] In the customers table and orders table, query all orders, including orders without customers, the SQL statement is as follows:

mysql> select customers.c_id, orders.o_num    -> from customers right outer join orders    -> on customers.c_id = orders.c_id;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 ||  NULL | 30004 || 10001 | 30005 |+-------+-------+5 rows in set (0.00 sec)

(3), compound conditions Connection query

• Composite condition connection query is to limit the query results by adding filter conditions during the connection query process to make the query results more accurate.

[Example 1] In the customers table and orders table, use inner join syntax to query the customers table to summarize the order information of the customer with ID 10001. The SQL statement is as follows:

mysql> select customers.c_id,orders.o_num    -> from customers inner join orders    -> on customers.c_id = orders.c_id and customers.c_id = 10001;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10001 | 30005 |+-------+-------+2 rows in set (0.00 sec)

[ Example 2] Between the fruits table and suppliers table, use inner join syntax to perform inner join query and sort the query results. The SQL statement is as follows:

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits inner join suppliers    -> on fruits.s_id = suppliers.s_id    -> order by fruits.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  101 | FastFruit Inc. | apple      |    5.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  102 | LT Supplies    | orange     |   11.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  103 | ACME           | apricot    |    2.20 ||  103 | ACME           | coconut    |    9.20 ||  104 | FNK Inc.       | lemon      |    6.40 ||  104 | FNK Inc.       | berry      |    7.60 ||  105 | Good Set       | melon      |    8.20 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  106 | Just Eat Ours  | mango      |   15.70 ||  107 | DK Inc         | xxxx       |    3.60 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

More related free learning recommendations ：mysql tutorial(Video)

The above is the detailed content of MySQL data query connection query. For more information, please follow other related articles on the PHP Chinese website!