Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

Learn SQL Union All Query Operators with Examples



Introduction

SQL is the most  popular and used query language among database programmers. SQL queries use many operators and clauses. Here, an operator is a reserved word in the SQL, that is used to perform any specific operation on the SQL statements. The operations like arithmetic or comparison operations are performed with the help of various operators. In this article, we are going to discuss commonly used SQL operators and Especially Union and Union All operators. Once you will go through this article, you must have complete information about the commonly used SQL operators and its uses. Also, if you think that you want to gain some in-depth knowledge of SQL, then enroll yourself in a course or just go for a demo session for the time being.

Read: MSBI Interview Questions & Answers for Fresher, Experienced

Nowadays, SQL is much popular and even to get the result from huge or large data sets we may need to execute complex queries. SQL operators help us to retrieve the selected data records from huge data sets quickly. Whenever a Select operation is performed on the records then there may be a certain clause for selecting the records and for this the operators are used. Here, we are going to discuss all types of operators used in SQL and will describe UNION operators  in detail, that are issued to merge two data tables. Apart from this, there are a few other operators as well as in the SQL. So, what is SQL union? Let us understand!

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

What are SQL Operators?

SQL Operators usually specify the conditions in an SQL statement and work as a conjunction to join multiple conditional statements. You can also use the operators in SQL statements and mainly below-listed operators are used by SQL statements:

  • Comparison Operators
  • Arithmetic Operators
  • Logical Operators
  • Negate Condition Operators

SQL Comparison Operators

There is a list of comparison operators in SQL that are listed below:

  • Equal to operator OR "="
  • Not Equal To OR "!=" OR "<>"
  • Greater Than OR ">"
  • Less Than OR "<"
  • Greater Than Equal To OR ">="
  • Less Than Equal To OR "<="

Read: How to Use Like Operator in SQL Server?

SQL Arithmetic Operators

For two variables of numeric values we can use following arithmetic operators: Following arithmetic operators are used by the SQL statements:

  • Addition or "+"
  • Subtraction or "-"
  • Multiplication "*"
  • Division "/"
  • Modulus "%"

SQL Logical Operators

Following listed logical operators are frequently used by the SQL statements:

SQL Logical Operators

SQL queries allow us to choose some of the specific information from a large amount of data. As all required may not be stored in a single table and sometimes we may need to find some data from multiple tables and we may need to merge the data from multiple tables to the single table by finding common elements from the tables.  For this, we may also have to use various operators like UNION, INTERSECT, and MINUS and here we are going to describe UNION operator:

Read: What Is Average Salary Of Database Admin In Philippine?

What is SQL UNION Operator?

UNION operator of SQL is used to unite or combine all data of two separate tables. Through UNION operators we can perform this operation on two tables. Merging two tables results in this operation. Through this operation, only UNIQUE values are returned by the operations. The vein diagram is shown below to represent UNION operation and how it  happens . If we want to use UNION operator then in the following way we can use it: Select * from Table1 UNION Select * from Table2 UNION Select * from Table3; Following are the characteristics of UNION operator:

  • There must be an equal number of columns in each statement of SELECT operator
  • Column data types must be same
  • Order of each column of the SELECT statement must be similar

UNION operator syntax

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

SQL UNION ALL Operator

UNION operator selects only distinct values, while in case if you also want to have duplicate values in the query result of UNION statement then  you can use UNION ALL statement. The syntax of UNION ALL statement is like the one listed below: Select column_name(s) FROM table1 UNION ALL Select column_name(s) FROM table2; Here the name of columns in the resulting database is as per the name of the first SELECT statement of UNION operator.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

List of SQL Union All Query and Examples

Let’s suppose we have two tables table1 and table2 like shown below, let the name of the first table is Movies and the other one is Books that contains the title of books and movies and an associated ID number with both of them. Here some of the title names of both tables may be same, now we can perform the two operations that are UNION and UNION ALL (basically an SQL union vs union ALL) on these tables: Let the table structure with data of the first table is:

Table: BOOKS

ID TITLES
1 Harry Potter
2 The Witcher
3 The Great Gatsby
4 Nineteen Eight-Four

Table: MOVIES

ID TITLES
1 INREDIBLES1
2 MATRIX
3 HARRY POTTER
4 Iron Man

Now let’s perform UNION operation on these two tables: If we want to get all data of table BOOK and Movie tables then we can perform UNION operation. As it merges the result of two SELECT statements. Here, UNION statements return only Unique values. The result of the operation is shown below:

ID TITLES
1 Harry Potter
2 The Witcher
3 The Great Gatsby
4 Nineteen Eight-Four
5 Incredibles1
6 Matrix
7 Iron Man

It can be shown that only unique values are returned by this statement. The book and movie names Harry Potter appeared twice once in the Books table and another in Movies table, but in the resulting table it appeared only one. So, we can say that the distinct values are returned by this operator. In case if we also want to have duplicate values then we can perform UNION ALL operation.

Read: View vs Table In SQL: A Comprehensive Insight You Have To Know

UNION ALL operation is similar to UNION operation, only one exception is there. Through UNION ALL operator all table data is returned, whether it is duplicate or not. Let's see the result of UNION ALL operation on these two tables: Select * From Books UNION ALL Select * From Movies;

Through the above operation, following result will be returned by the query:

ID TITLES
1 Harry Potter
2 The Witcher
3 The Great Gatsby
4 Nineteen Eight-Four
5 Incredibles1
6 Matrix
7 Harry Potter
8 Iron Man

Here, you can see that the value that is the same  in the two tables appears twice in the resulting table.

When to Use UNION and UNION ALL Operators?

Now we have seen the main difference between both the operators but we may get confused for them as when and where we should use each of the operators? To get the answer to this question the most important fact that you should know is that the efficiency of both the operators is hugely different.

In case if we have to merge two large tables like the one having more than 10,000 elements the UNION operator will eliminate the duplicate values and will sort the unique items, so here the sorting process may take lots of time if there will be a large number of elements.

Do you know 57% of the techies are utilizing online training to learn specific technology!

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

For addition operation UNION can be four times slower operation than UNION ALL, and UNION ALL do not eliminate duplicate data and sort the elements, so in case if you want to work faster and do not care about duplicate values than UNION ALL may be a better option. On the other hand, if you want to have only unique values in the resulting database and do not bother about time that the UNIQUE operator can be used to merge the two tables.

Other Commonly Used SQL Operators

Like Union operator, two merge the two tables we can also use Minus and Intersect operators. Here Minus will eliminate the values from the first part of Select statements. Like if we will perform Minus operation on the above-listed two tables then the query: SELECT * FROM BOOKS MINUS SELECT * FROM MOVIES Will result in only those books' names  that are not Movie titles in the Movies  table. So, the result will be:

ID TITLES
1 The Witcher
2 The Great Gatsby
3 Nineteen Eight-Four

In case if you want to know that what common values are there in both the tables then you can use Intersect operator. As shown below: Select * From Books INTERSECT Select * From Movies The result of the above query will be:

ID TITLES
1 Harry Potter

Read: A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced

Final Words:

Understood what sql union is? Now you have seen the result of all merge operators like UNION, INTERSECT, and MINUS. These operators will execute the query and return the sorted values of resulting tables, while UNION ALL will not sort the values. We can also use ORDER BY Clause if we want to sort certain query results. Here in SQL, SET operators are also used for query execution and it can be used for any type of query.

In this query, there is no need to select all of the records from both of the tables, we can use the result of an existing query.  In order to get the real experience of the operators of SQL language, you should try to use them practically. All the best and happy learning with JanBask Training.  Sign up for an amazing SQL course right now!

Read: Future Growth of a SQL Server Developer

SQL Tutorial Overview

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

2 days 22 Mar 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

1 day 21 Mar 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

2 days 22 Mar 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

2 days 22 Mar 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

2 days 22 Mar 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

9 days 29 Mar 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

3 days 23 Mar 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

9 days 29 Mar 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

3 days 23 Mar 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

17 days 06 Apr 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

30 days 19 Apr 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

9 days 29 Mar 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews