Analysis of MySQL multi-table joint query operation examples

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

Recommend

CSS achieves the effect of rotating the outermost layer of a multi-layer nested structure while keeping other layers unchanged

There is such a scenario: a circular container, t...

Detailed explanation of Vue's props configuration

<template> <div class="demo"&g...

The difference between KEY, PRIMARY KEY, UNIQUE KEY, and INDEX in MySQL

The problem raised in the title can be broken dow...

Example of using CSS to achieve semi-transparent background and opaque text

This article introduces an example of how to use ...

An article to understand the creation, uploading, pulling and deployment of Docker images

Table of contents 1. Images 1. What is a mirror? ...

Mysql5.7.17 winx64.zip decompression version installation and configuration graphic tutorial

1. Download mysql-5.7.17-winx64.zip; Link: https:...

Writing Snake Game with Native JS

This article shares the specific code of writing ...

jQuery custom magnifying glass effect

This article example shares the specific code of ...

Explore how an LED can get you started with the Linux kernel

Table of contents Preface LED Trigger Start explo...

Detailed explanation of Linux file permissions and group modification commands

In Linux, everything is a file (directories are a...

javascript:void(0) meaning and usage examples

Introduction to void keyword First of all, the vo...

CSS Tricks to Create Wave Effects

It has always been very difficult to achieve wave...

Steps to transplant the new kernel to the Linux system

1. Download the ubuntu16.04 image and the corresp...

The 6 Most Effective Ways to Write HTML and CSS

This article shares the 6 most effective methods,...

How to add, delete and modify columns in MySQL database

This article uses an example to describe how to a...