Yogesh Chauhan's Blog

The SQL UNION Operator

in SQL/MySQL on September 24, 2019

The SQL UNION Operator (Works in MySQL as well)

As per the name suggests, UNION operator merges/combines/joins two or more results of SELECT statements. But you have to be careful while joining the result sets as it has to follow some conditions. For example,

  • You have to select same number of columns for each select statements. It makes sense. You can’t merge 1 column with 2 different columns data. Right?
  • Those columns MUST have same data type. For example, you can’t join columns with INT and VARCHAR data types.
  • The columns needs to be (read as MUST be) in the same order. For example if there are two columns; city column from customer and city column from merchants, which you want to join then it should be in the exact same order in the table. That’s tough one!

Let’s take a look at the syntax:


SELECT column(s) FROM table1
UNION
SELECT column(s) FROM table2;

The UNION operator by default selects distinct values. Means no place or duplicate values. For example, if there is a Chicago in a city columns of customers and merchants both then it will only give us one Chicago city in result set.

To allow duplicates, use UNION ALL. For e.g.


SELECT column(s) FROM table1
UNION ALL
SELECT column(s) FROM table2;

Let’s take a look at the example.


SELECT City FROM Customers
UNION
SELECT City FROM merchants
ORDER BY City;

The example query above will give us all the cities list which are in the city column of Customers table as well as in merchants table.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Understanding the confusing and handy “This” keyword in JavaScriptJavaScriptapply_filters function in WordPressWordPress5 Ways to Loop Through JavaScript ArraysJavaScriptHow to embed YouTube or other video links in WordPress?WordPressIs there a CSS parent selector?CSSWhat’s a Web Storage API in JavaScript?JavaScript