Interview Sansar
IT/Software Jobs Interview Preparation Source
Menu
Explain UNION and UNION ALL SQL Clause with
example
March 9, 2017 by Rakesh Singh
(Last Updated On: April 24, 2019)
Answer includes concept and difference between union and union all sql clause with
example. Also, difference between union and join as a note has been included.
Answer:
UNION and UNION ALL: Used to combine the result set of 2 or more SELECT
statements.
UNION sql statement returns sorted result set with unique (UNION effectively
performs SELECT DISTINCT) rows.
UNION ALL sql statement returns unsorted result set including duplicate rows.
NOTE:
To use UNION & UNION ALL, number of columns, data types and the order of the
columns must be same in each select statement. However, number of equal
rows is not required.
If all the rows are already unique, use UNION ALL instead of UNION, it gives faster
results as UNION ALL doesn’t perform SELECT DISTINCT. ORDER BY clause can be
used to sort the result.
Union and Union All Example
Below are the C++ and JAVA project tables for Employees. We have to List the
employees who are assigned to either C++ or JAVA projects.
C++
NAME POST
Scott Software Engg.
Amy Technical lead
Lisa Manager
JAVA
NAME POST
Ben Software Engg.
Amy Technical lead
On performing union query on table C++ and Java following will be the result
SELECT NAME FROM C++
UNION
SELECT NAME FROM JAVA
NAME POST
Amy Technical lead
Ben Software Engg
Lisa Manager
Scott Software Engg
On performing Union All query on table C++ and Java following will be the result
SELECT NAME FROM C++
UNION ALL
SELECT NAME FROM JAVA
NAME POST
Scott Software Engg
Amy Technical lead
Lisa Manager
Ben Software Engg
Amy Technical lead
To Focus:
ORDER BY with UNION ALL:
ORDER BY should be used only on the last select statement in the UNION ALL queries
otherwise it will throw an error.
SELECT NAME FROM C++
UNION ALL
SELECT NAME FROM JAVA
ORDER BY POST
UNION Vs UNION ALL:
UNION removes duplicate rows, but UNION ALL doesn’t.
UNION performs sort before removing duplicates. Hence, slower performance
than UNION ALL as it doesn’t sort result set?
UNION Vs JOIN
UNION combines result set of 2 or more SELECT statements whereas JOIN
retrieves data from 2 or more tables based on logical relationship (Primary Key &
Foreign Key) between tables.
In other words, UNION combines rows from 2 or more tables, where JOIN
combines columns.
Related Posts:
SQL WHERE Clause
SQL HAVING Clause
FOR UPDATE and WHERE CURRENT OF Clause in Oracle
Explain Default Constraint in SQL with Example and Uses
Learn Big Data & Analytics - CTE - Common Table
BITS Pilani Alumni Status Expression in PL-SQL
Ad upgrad.com interviewsansar.com
JanusGraph - open source Query to find the data which
graph database not common in two tables
Ad github.com interviewsansar.com
Proof that string is immutable MCQ – SQL Database multiple
in C# Programming choice questions with answers
interviewsansar.com interviewsansar.com
Decode function vs Case MCQ on JVM and Java Memory
statement in PL-SQL management
interviewsansar.com interviewsansar.com
Java Collections short What is time complexity for
interview FAQs with answers... offer, poll and peek methods...
interviewsansar.com interviewsansar.com
SQL Database
sql union, sql union all
What is Use of Private Constructor in C# ? Scenarios Example
What is Comparable and Comparator in java?
Leave a Comment
Name *
Email *
Website
I'm not a robot
reCAPTCHA
Privacy - Terms
Post Comment
C/C++ Interview Questions
C
C++ Basic
C++ Advanced
C++ Programming
C# Interview Questions
C# Basic
C# Advanced
C# Multi-threading
C# Programming
C# program exercises
Java Interview Questions
Core Java
Java Collections
Java Multithreading
Java Notes
Java Tutorial
Multiple Choice Questions
MCQ-C++
MCQ-Java
MCQ-SQL
MCQs Algorithms and Data Structure
Others
C NOTES
SQL Database
Design Patterns and Principles
Networking General
UML Diagram
Mathematical Puzzles
Author
Rakesh Singh is highly experienced IT professional & founder of Interview Sansar, an
excellent resource for interview preparation…Read more
Looking for
Career
Transition
upgrad.com
Switch to Data Science
Become a Post Graduate
without quitting your job.
Online Power Learning
from upGrad.com
OPEN
Recent Posts
Query to find the data which not common in two tables
Query to find average fee paid by students in department wise
Select the rows that contain Input value
CTE – Common Table Expression in PL-SQL
SQL DUAL Table
SUBSCRIBE
© 2019 Interview Sansar • Powered by GeneratePress
