The UNION and UNION ALL commands in SQL retrieve data from numerous tables. They combine the data from two or more tables and return the records, but there is a slight difference. Let's look at that difference below:
UNION: Returns unique records from all tables.UNION ALL: Returns all records from all tables (the results can have duplicates).Note: To use
UNIONandUNION ALLwe need to select the same columns from all tables. Moreover, they should have the same datatype.
The syntax for UNION and UNION ALL commands is shown below.
SELECT column1, column2, ....FROM table_a[WHERE condition]UNION (or) UNION ALLSELECT column1, column2, ....FROM table_b[WHERE condition]
Let's understand each of them with an example.
UNIONcommandIn the example below, we first create the database and tables. Then we run the query using UNION.
Note: Lines 1 - 30 will create the database and tables. To check the data from individual tables, uncomment lines 32 and 33 and then run the query.
create database test;use test;CREATE TABLE recipesone (recipe_id INT NOT NULL,recipe_name VARCHAR(30) NOT NULL,PRIMARY KEY (recipe_id),UNIQUE (recipe_name));INSERT INTO recipesone(recipe_id, recipe_name)VALUES(1,"Tacos"),(2,"Tomato Soup"),(3,"Grilled Cheese");CREATE TABLE recipestwo (recipe_id INT NOT NULL,recipe_name VARCHAR(30) NOT NULL,PRIMARY KEY (recipe_id),UNIQUE (recipe_name));INSERT INTO recipestwo(recipe_id, recipe_name)VALUES(1,"Tacos"),(5,"Pizza"),(3,"Grilled Cheese");-- SELECT * from recipesone;-- SELECT * from recipestwo;SELECT recipe_id, recipe_name from recipesoneUNIONSELECT recipe_id, recipe_name from recipestwo;
UNION command.UNION ALLcommandNote: Lines 1 - 30 will create the database and tables. To check the data from individual tables, uncomment lines 32 and 33 and run the query.
create database test;use test;CREATE TABLE recipesone (recipe_id INT NOT NULL,recipe_name VARCHAR(30) NOT NULL,PRIMARY KEY (recipe_id),UNIQUE (recipe_name));INSERT INTO recipesone(recipe_id, recipe_name)VALUES(1,"Tacos"),(2,"Tomato Soup"),(3,"Grilled Cheese");CREATE TABLE recipestwo (recipe_id INT NOT NULL,recipe_name VARCHAR(30) NOT NULL,PRIMARY KEY (recipe_id),UNIQUE (recipe_name));INSERT INTO recipestwo(recipe_id, recipe_name)VALUES(1,"Tacos"),(5,"Pizza"),(3,"Grilled Cheese");-- SELECT * from recipesone;-- SELECT * from recipestwo;SELECT recipe_id, recipe_name from recipesoneUNION ALLSELECT recipe_id, recipe_name from recipestwo;
UNION ALL command.