This article uses examples to illustrate the principles and usage of MySQL join queries, union queries, and subqueries. Share with you for your reference, the details are as follows:

In this article:

• Connection query
• Joint Query
• Subqueries
  • from subquery
  • where subquery
  • exists subquery

Release date: 2018-04-11

Connection query:

• A join query is to query multiple tables together. The join query methods include inner join, outer join, natural join, and cross join. Join queries allow you to view data from multiple tables at the same time.
  • Inner join: conditional join, multiple tables are connected based on specified conditions, and the matching result is to retain the records that meet the matching results.
  • Outer join: Different from inner join, it keeps all the data regardless of the matching characters. The outer join mode determines which table to keep. For example, if the left table is kept, then when the left table cannot match the right table, the left table data is kept, and the right table field data is set to null.
  • Natural connection: conditional connection, automatically connected based on "fields with the same name" (multiple fields with the same name are used as conditions).
  • Cross join: unconditional connection, connecting each record with each record of another table (Cartesian product). The result is that the number of fields is equal to the sum of the original number of fields, and the number of records is equal to the product of the number of records in each table.

-- Experimental table structure create table student(
id int,
name varchar(15),
gender varchar(15),
cid int
);
create table class(
cid int,
cname varchar(15)
);
drop table student,class;
-- Experimental table data:
insert into student values(1,"lilei","male",1),(2,"hanmeimei","male",2),(3,"jack","male",1),(4,"alice","female",4); --Here is a special creation of 4 which is not in the class
insert into class values(1,"linux"),(2,"python"),(3,"java"),(5,"html5");--Here we create a class 5 that is not in student.
select * from student;
select * from class; 

Inner join:

• Take each record from the left table, match it with all the records in the right table, keep the successfully matched records, and concatenate the two records.
• Syntax: select field list from left table [inner] join right table on left table.field = right table.field;
  • When the on condition is not used, the result is the same as the cross join

  -- Inner join -- select * from student inner join class; --The result is the same as the cross join select * from student join class on student.cid = class.cid;
  select * from student inner join class on student.cid = class.cid; 

  

Outer Join:

• Unlike inner join, the main table records are retained regardless of whether the matching characters are met. There are two ways: left outer join and right outer join. Left outer join retains the left table, and right outer join retains the right table.
• grammar:
  • Left outer join: select field list from left table left join right table on left table.field = right table.field;
  • Right outer join: select field list from left table right join right table on left table.field = right table.field;

  select * from student left join class on student.cid = class.cid; 

  

select * from student right join class on student.cid = class.cid; 

Natural connection:

• Automatically match connection conditions. The system uses field names as matching patterns (fields with the same name are used as conditions, and multiple fields with the same name are used as conditions)
  • Natural inner join: Similar to inner join, but no join conditions are provided.
  • Natural outer join: Similar to outer join, but does not provide join conditions.
• grammar:
  • Natural inner join: select field list from table name natural join table name;
  • Natural outer join: select field list from table name natural left\right join table name;

  select * from student natural join class; 

  

select * from student natural left join class; 

Cross connection:

• Join each record with each record in another table
• grammar:
  • select field list from table name cross join table name;
  • select field list from table name, table name;

  select * from student cross join class;
  select * from student,class; 

  

Replenish:

• In order to distinguish each table among multiple tables and facilitate use, you can use table aliases.

  select * from student inner join class on student.cid = class.cid;-- Original result select id,name,gender,c.cid,cname from student as s inner join class as c on s.cid = c.cid;-- Use table alias

• Multiple connections are possible.
• Outer join can simulate natural join. You only need to change the join condition on left table.field = right table.field to "using field name".

Joint query:

• A joint query is to join multiple query results on records. (It is equivalent to connecting the query record results of other tables to the back of the first table) [Because it is splicing, the number of fields in multiple query results must be the same ] [Splice does not care about the data type, for example, the first field of the first table is int, but the varchar in the subsequent table can still be spliced ​​into the first column]
• Syntax: select statement union select statement…;

  select name,gender from student
  union 
  select * from class;
  -- Because class has only two fields, the first one only selects two fields 

  

Replenish:

• Union can have options, which are added after union. The all option does not remove duplicates, while the distinct option does.
• Union query is generally used to process different data in the same table in different ways. (For example, if you want to query two subjects (here we assume they are python and linux) in a student's score table at the same time)
• Error when using union and order by at the same time
  • If you want to sort a query result in a union query, you need to enclose the select statement in parentheses. [In addition, due to the concatenation mechanism of joint query, it is necessary to add a limit clause after order by, and the limit number can be a very large value. 】
  • If it is for the final joint query result, use order by in the last select statement [it is recommended to add brackets to the last field and then add order by, and it is used when there are fields with the same name]

Subquery:

• A subquery is a query that is nested within a query statement.
• Subqueries can be divided into three categories according to where they appear:
  • From subquery: The subquery follows from; it is generally used in the situation of "finding the two-dimensional table first, then processing it".
    • for example:

      -- This is a meaningless example. Just for example use select cid,cname from (select * from class where cname="python") as c;

  • Where subquery: The subquery follows the where condition; generally used in the case of "querying the specified conditions first and then querying"
    • for example:

      select * from student where cid=(select cid from class where cname="python");

  • Exist subquery: The subquery is in the exist statement; it is generally used for "only if it exists" situations
    • for example:

      -- This is an example of not outputting the corresponding course information if the student has not selected the course with cid=1 select * from class where exists(select * from student where cid=1) and cid=1;

  • In fact, some people think that the query following a union is also a subquery , but these are not considered subqueries here. Only the above few that are very closely related to "queries" are considered subqueries.

Replenish:

• In fact, you can also categorize subqueries based on their results:
  • Scalar subquery, the result of the subquery is one row and one column, which usually occurs when the where subquery only queries one row and one column.
  • Column subquery, the result of the subquery is one column with multiple rows, which usually occurs when the where subquery queries one column with multiple rows.
  • Row subquery, the result of the subquery is multiple columns and one row (or multiple rows and multiple columns). This usually happens when the where subquery queries multiple columns and one row.
  • Table subquery, the result of the subquery is multiple rows and columns, which usually occurs in the from subquery
• Sometimes other keywords are used in where subqueries, such as any, all, and some, but for =, = can basically achieve their functions.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.