MySQL: Union Operator

UNION combines different sets of SELECT statement data into a single record set in MySQL.

Rules for using UNION:

• The respective column name must be equal to that of the other column name of the union match.
• The respective columns should have similar data types too.

Syntax

SELECT column names from TableName
UNION
SELECT column names from TableName;

If you wanna a query recordset of tables with duplicate values too then UNION ALL is used.

SELECT column names from TableName
UNION ALL
SELECT column names from TableName;

So let's dive into a real-time example.

Say I want a recordset of two tables with information about movie titles, id, and release year where the year of the release date should be less than or equal to 2000.

So that's what we are gonna use so for this we will be using tables movies and shows so here's what they look like.

Example

select movie_id as id, movie_title as title,year as release_date from movies where year <= 2000
union
select show_id as id, show_title as title, initial_year as release_date from shows where initial_year <= 2000;

So here using union we can get a record set of two select statements into a single record by selecting the columns and aliasing it for our advantage and using the WHERE clause to get a certain release year.