Analysis of MySQL multi-table joint query operation examples

This article describes the MySQL multi-table joint query operation. Share with you for your reference, the details are as follows:

MySQL multi-table joint query is a query method of MySQL database. The following introduces the syntax of MySQL multi-table joint query for your reference and learning.

MySQL multi-table joint query syntax:

Copy the code as follows:

 SELECT * FROM insert table LEFT JOIN main table ON t1.lvid=t2.lv_id select * from mytable,title where table name 1.name=table name 2.writer;

For MySQL versions greater than 4.0, use UNION to query. The example is as follows:

SELECT `id`, `name`, `date`, '' AS `type` FROM table_A WHERE conditional statement...
 UNION
SELECT `id`, `name`, `date`, 'Not completed' AS `type` FROM table_B WHERE Conditional statement...
 ORDER BY `id` LIMIT num;

If the MySQL version is less than 4.0, you need to create a temporary table, which is divided into three steps. The example is as follows:

Step 1: Create a temporary table tmp_table_name and insert relevant records in table_A

Copy the code as follows:

 $sql = "CREATE TEMPORARY TABLE tmp_table_name SELECT `id`, `name`, `date`, 'completed' AS `type` FROM table_A WHERE conditional statement... ";

Step 2: Get relevant records from table_B and insert them into the temporary table tmp_table_name

Copy the code as follows:

 INSERT INTO tmp_table_name SELECT `id`, `name`, `date2` AS `date`, 'Not completed' AS `type` FROM table_B WHERE Conditional statement...

Step 3: Get records from the temporary table tmp_table_name

SELECT * FROM tmp_table_name ORDER BY id DESC

Analysis of the differences between union, order by and limit

Code example:

CREATE TABLE `test1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `desc` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. The following query will report an error: [Err] 1221 - Incorrect usage of UNION and ORDER BY

Code example:

select * from test1 where name like 'A%' order by name
union
select * from test1 where name like 'B%' order by name

Modified to:

Code example:

select * from test1 where name like 'A%'
union
select * from test1 where name like 'B%' order by name

Note that in a union, without brackets, only one order by can be used (think: what will happen if the order by columns on both sides of the union have different names?), which will sort the result set after the union.

Modified to:

Code example:

(select * from test1 where name like 'A%' order by name)
union
(select * from test1 where name like 'B%' order by name)

This is also possible. The two order by statements are performed before the union.

2. Likewise

Code example:

select * from test1 where name like 'A%' limit 10
union
select * from test1 where name like 'B%' limit 20

is equivalent to:

Code example:

(select * from test1 where name like 'A%' limit 10)
union
(select * from test1 where name like 'B%') limit 20

That is, the latter limit acts on the result set after the union, not the select after the union.
You can also wrap it in parentheses to get the expected result:

3. Differences between UNION and UNION ALL

Union will filter out duplicate rows in the select result sets on both sides of the union, while union all will not filter out duplicate rows.

Code example:

(select * from test1 where name like 'A%' limit 10)
union
(select * from test1 where name like 'B%' limit 20)

Let's try a complex SQL statement for age group analysis.

(
 SELECT
  '5~19' AS `age`,
  SUM(`impression`) AS impression,
  SUM(`click`) AS click,
  sum(`cost`) AS cost
 FROM
  `adgroup_age_report`
 WHERE
  (
   (
    (`age` <= 19)
    AND (`adgroup_id` = '61')
   )
   AND (`date` >= '2015-11-22')
  )
 AND (`date` <= '2017-02-20')
)
UNION
 (
  SELECT
   '20~29' AS `age`,
   SUM(`impression`) AS impression,
   SUM(`click`) AS click,
   sum(`cost`) AS cost
  FROM
   `adgroup_age_report`
  WHERE
   (
    (
     ((`age` <= 29) AND(`age` >= 20))
     AND (`adgroup_id` = '61')
    )
    AND (`date` >= '2015-11-22')
   )
  AND (`date` <= '2017-02-20')
 )
UNION
 (
  SELECT
   '30~39' AS `age`,
   SUM(`impression`) AS impression,
   SUM(`click`) AS click,
   sum(`cost`) AS cost
  FROM
   `adgroup_age_report`
  WHERE
   (
    (
     ((`age` <= 39) AND(`age` >= 30))
     AND (`adgroup_id` = '61')
    )
    AND (`date` >= '2015-11-22')
   )
  AND (`date` <= '2017-02-20')
 )
UNION
 (
  SELECT
   '40~49' AS `age`,
   SUM(`impression`) AS impression,
   SUM(`click`) AS click,
   sum(`cost`) AS cost
  FROM
   `adgroup_age_report`
  WHERE
   (
    (
     ((`age` <= 49) AND(`age` >= 40))
     AND (`adgroup_id` = '61')
    )
    AND (`date` >= '2015-11-22')
   )
  AND (`date` <= '2017-02-20')
 )
UNION
 (
  SELECT
   '50~59' AS `age`,
   SUM(`impression`) AS impression,
   SUM(`click`) AS click,
   sum(`cost`) AS cost
  FROM
   `adgroup_age_report`
  WHERE
   (
    (
     ((`age` <= 59) AND(`age` >= 50))
     AND (`adgroup_id` = '61')
    )
    AND (`date` >= '2015-11-22')
   )
  AND (`date` <= '2017-02-20')
 )

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

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

You may also be interested in:

MySQL database aggregate query and union query operations
MySQL beginners can say goodbye to the troubles of grouping and aggregation queries
Introduction to the use of MySQL joint query UNION and UNION ALL
Summary of four situations of joint query between two tables in Mysql
Detailed analysis and optimization of Mysql multi-table joint query efficiency
Popular explanation of several MySQL joint queries
MySQL aggregate query and union query operation examples

<<: Solutions to black screen when installing Ubuntu (3 types)

>>: js realizes the magnifying glass function of shopping website