UNION and JOIN multi-table joint query method in MySQL

Preface

In MySQL, UNION and JOIN are the two main ways of multi-table query. They have different applicable scenarios and syntaxes

The differences between the two are as follows:

characteristic	JOIN	UNION
Data combination	Horizontal combination, merge two table data into one row	Combination vertically, merge data from two tables into multiple rows
Data structure requirements	Relevant conditions are required between tables, such as foreign keys, etc.	The number of columns and data types must be consistent
Result data volume	The number of result records is usually less than the table data	The number of results records may increase
Common scenarios	Data association, such as order and user information query	Data summary, such as a similar record combination in multiple tables

Specific usage choices:

Using JOIN:

When you need to associate the data of two tables according to some logic, select JOIN

Using UNION:

When it is necessary to merge data with the same structure but no direct relationship in multiple tables, select UNION

1. JOIN

JOIN is used to associate data between multiple tables according to certain conditions.

JOIN query combines tables horizontally through specified conditions (such as foreign key relationships)

SELECT column_names
FROM table1
[INNER|LEFT|RIGHT] JOIN table2
ON table1.column_name = table2.column_name
WHERE conditions;

The basic types are as follows:

JOIN Type

INNER JOIN (Internal Connection)

Return only two records that meet the ON condition in the table
If a record has no match in any table, it will not be included in the result.
Commonly used to extract relevant data

LEFT JOIN (left connection)

Returns all records in the left table, and records in the right table that match the left table record
If there is no match in the right table, the field in the right table of the record in the result is NULL
Commonly used to find data in the left table even if there is no match for the right table

RIGHT JOIN (right link)

Returns all records in the right table, and records in the left table that match the right table record
If there is no match in the left table, the field of the record left table in the result is NULL
Not as commonly used as LEFT JOIN

FULL OUTER JOIN (full connection) (MySQL is not directly supported, but can be simulated through UNION)

Returns all records in both tables, regardless of whether there is a match
If not supported, use the following substitute:

SELECT ...
FROM table1
LEFT JOIN table2 ON condition
UNION
SELECT ...
FROM table1
RIGHT JOIN table2 ON condition;

2. UNION

UNION is used to combine two or more query results into a result set vertically. It has requirements for the number of columns and data types of the query

The basic syntax is as follows:

SELECT column_names
FROM table1
WHERE conditions
UNION [ALL]
SELECT column_names
FROM table2
WHERE conditions;

The precautions are as follows:

Deduplication by default:

UNION will remove duplicate records in the result set
If you need to keep all duplicate records, use UNION ALL

Number of columns and data type requirements:

All SELECT statements participating in the query must have the same number of columns.
The data type of each column must be compatible

Result sort:

The overall result can be sorted by adding ORDER BY in the result set after UNION

This is the article about UNION and JOIN multi-table joint query methods in MySQL. For more related mysql union and join content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!