SQL UNION Operator

SQL UNION Operator SQL Union A Comprehensive Guide on the UNION Operator Back Home Categories Online Courses Mock Interviews Webinars NEW Community Write for Us Categories Artificial Intelligence AI vs Machine Learning vs Deep LearningMachine Learning AlgorithmsArtificial Intelligence TutorialWhat is Deep LearningDeep Learning TutorialInstall TensorFlowDeep Learning with PythonBackpropagationTensorFlow TutorialConvolutional Neural Network TutorialVIEW ALL BI and Visualization What is TableauTableau TutorialTableau Interview QuestionsWhat is InformaticaInformatica Interview QuestionsPower BI TutorialPower BI Interview QuestionsOLTP vs OLAPQlikView TutorialAdvanced Excel Formulas TutorialVIEW ALL Big Data What is HadoopHadoop ArchitectureHadoop TutorialHadoop Interview QuestionsHadoop EcosystemData Science vs Big Data vs Data AnalyticsWhat is Big DataMapReduce TutorialPig TutorialSpark TutorialSpark Interview QuestionsBig Data TutorialHive TutorialVIEW ALL Blockchain Blockchain TutorialWhat is BlockchainHyperledger FabricWhat Is EthereumEthereum TutorialB lockchain ApplicationsSolidity TutorialBlockchain ProgrammingHow Blockchain WorksVIEW ALL Cloud Computing What is AWSAWS TutorialAWS CertificationAzure Interview QuestionsAzure TutorialWhat Is Cloud ComputingWhat Is SalesforceIoT TutorialSalesforce TutorialSalesforce Interview QuestionsVIEW ALL Cyber Security Cloud SecurityWhat is CryptographyNmap TutorialSQL Injection AttacksHow To Install Kali LinuxHow to become an Ethical Hacker?Footprinting in Ethical HackingNetwork Scanning for Ethical HackingARP SpoofingApplication SecurityVIEW ALL Data Science Python Pandas TutorialWhat is Machine LearningMachine Learning TutorialMachine Learning ProjectsMachine Learning Interview QuestionsWhat Is Data ScienceSAS TutorialR TutorialData Science ProjectsHow to become a data scientistData Science Interview QuestionsData Scientist SalaryVIEW ALL Data Warehousing and ETL What is Data WarehouseDimension Table in Data WarehousingData Warehousing Interview QuestionsData warehouse architectureTalend T utorialTalend ETL ToolTalend Interview QuestionsFact Table and its TypesInformatica TransformationsInformatica TutorialVIEW ALL Databases What is MySQLMySQL Data TypesSQL JoinsSQL Data TypesWhat is MongoDBMongoDB Interview QuestionsMySQL TutorialSQL Interview QuestionsSQL CommandsMySQL Interview QuestionsVIEW ALL DevOps What is DevOpsDevOps vs AgileDevOps ToolsDevOps TutorialHow To Become A DevOps EngineerDevOps Interview QuestionsWhat Is DockerDocker TutorialDocker Interview QuestionsWhat Is ChefWhat Is KubernetesKubernetes TutorialVIEW ALL Front End Web Development What is JavaScript â€" All You Need To Know About JavaScriptJavaScript TutorialJavaScript Interview QuestionsJavaScript FrameworksAngular TutorialAngular Interview QuestionsWhat is REST API?React TutorialReact vs AngularjQuery TutorialNode TutorialReact Interview QuestionsVIEW ALL Mobile Development Android TutorialAndroid Interview QuestionsAndroid ArchitectureAndroid SQLite DatabaseProgramming A Comprehens... MySQL (55 Blogs) Become a Certified Professional AWS Global Infrastructure Introduction to SQL What is a Database? Definition, Types and ComponentsWhat is SQL and how to get started with it?SQL Basics â€" One Stop Solution for BeginnersWhat are SQL Operators and how do they work?Understanding SQL Data Types â€" All You Need To Know About SQL Data TypesSQL Tutorial : One Stop Solution to Learn SQLDBMS Tutorial : A Complete Crash Course on DBMSCREATE TABLE in SQL â€" Everything You Need To Know About Creating Tables in SQLWhat is a Schema in SQL and how to create it?What is a Cursor in SQL and how to implement it?Top 10 Reasons Why You Should Learn SQLLearn how to use SQL SELECT with examplesSQL Functions: How to write a Function in SQL?What is SQL Regex and how to implement it?SQL UPDATE : Learn How To Update Values In A TableSQL Union â€" A Comprehensive Guide on the UNION OperatorWhat are Triggers in SQL and how to implement them?INSERT Query SQL â€" All You Need to Know about the IN SERT statementHow To Use Alter Table Statement In SQL?What is Normalization in SQL and what are its types?How to perform IF statement in SQL?What are SQL constraints and its different types?Learn How To Use CASE Statement In SQLPrimary Key In SQL : Everything You Need To Know About Primary Key OperationsForeign Key SQL : Everything You Need To Know About Foreign Key OperationsSQL Commands - A Beginner's Guide To SQLHow To Rename a Column Name in SQL?How to retrieve a set of characters using SUBSTRING in SQL?What is the use of SQL GROUP BY statement?How To Use ORDER BY Clause In SQL?How to use Auto Increment in SQL?Everything You Need to Know About LIKE Operator in SQLWhat is an index in SQL?Understanding SQL Joins â€" All You Need To Know About SQL JoinsDifferences Between SQL A Comprehensive Guide on the UNION Operator Published on Oct 14,2019 1.8K Views Avinash A Bookmark 16 / 37 Blog from Introduction to SQL Become a Certified Professional While handling data in databases , we often tend to use the set operators in SQL, to retrieve data according to our requirements by combining two or more SELECT statements. In this article on SQL UNION, I will discuss the UNION operator used to retrieve data in the following sequence:What is the SQL UNION operator?Syntax:UNION SyntaxUNION ALL SyntaxDifferences between UNION and UNION ALLSQL UNION Examples:UNION Operator exampleUNION ALL operator exampleUNION with SQL AliasesUNION with WHERE clauseUNION ALL with WHERE clauseUNION with JOINSUNION ALL with JOINSLet us get started!What is the SQL UNION operator?As the name suggests, this operator/ clause is used to combine the results of two or more SELECT statements. Here each SELECT statement used within the UNION statement must have the same number of columns in the same order. Also, all the columns present in the SELECT statements must have similar data types.The UNION clause gives only unique values as output. Just in case, you want duplicate values, then you have to use the UNION ALL clause.Moving on in this article on SQL UNION, let us understand the syntax.SQL UNION SyntaxUNION Syntax SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1 UNION SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2; UNION ALL Syntax SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1 UNION ALL SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2; Moving on in this article, let us understand the differences between UNION and UNION ALL.Differences between SQL UNION and UNION ALLUNIONUNION ALLCombines two or more result set and does not keep the duplicate values.Combines two or more result set and keeps the duplicate values.Syntax: UNIONSyntax: UNION ALLNext, in this article on SQL UNION, let us understand the different ways to use this operator.Examples of SQL UNION and UNION ALLFor your better understanding, I will be considering the following tables to show you different examples.Employee TableEmpIDNameEmpAgeCityPostalCodeCountry1Emma23Berlin12109Germany2Rahul26Mumbai400015India3Aayra24New York10014USA4John32LondonE1 7AEUK5Derek29New York10012USAProjects TableProjectIDNameWorkingDaysCityPostalCodeCountry1Project 110Berlin12109Germany2Project 27Mumbai400015India3Project 320Delhi110006India4Project 415Mumbai400015India5Project 528Berlin12109GermanyLet us get started with examples.SQL UNION ExamplesUNION Operator ExampleWrite a query to retrieve distinct cities from the Employees and Projects table. SELECT City FROM Employees UNION SELECT City FROM Projects ORDER BY City; Output:CityBerlinDelhiLondonMumbaiNew YorkUNION ALL Operator ExampleWrite a query to retrieve cities from the Employees and Projects table. Here, duplicate values must be included. SELECT City FROM Employees UNION ALL SELECT City FROM Projects ORDER BY City; Output:CityBerlinBerlinBerlinDelhiLondonMumbaiMumbaiMumbaiNew YorkNew YorkNext in this article, let us understand how to use the UNION clause with SQL Aliases.UNION with SQL AliasesSQL Aliases are used to give a table or a column a temporary name. So, let us write a query to list all the unique employees and projects. SELECT 'Employee' AS Type, Name, City, Country FROM Employees UNION SELECT 'Project', Name, City, Country FROM Projects; Output:TypeNameCityCountryEmployeeEmmaBerlinGermanyEmployeeRahulMumbaiIndiaEmployeeAayraNew YorkUSAEmployeeJohnLondonUKEmployeeDerekNew YorkUSAProjectProject 1BerlinGermanyProjectProject 2MumbaiIndiaProjectProject 3DelhiIndiaProjectProject 4MumbaiIndiaProjectProject 5BerlinGermanyUNION with WHERE clauseWrite a query to retrieve the distinct Indian cities and their postal codes from both the Employees and Projects table. SELECT City, PostalCode, Country FROM Employees WHERE Country='India' UNION SELECT City, PostalCode, Country FROM Projects WHERE Country='India' ORDER BY City; Output:CityPostalCodeCountryDelhi110006IndiaMumbai400015IndiaUNION ALL with WHERE clauseWrite a query to retrieve Indian cities and their postal codes from both the Employees and Projects table, where duplicate values are allowed SELECT City, PostalCode, Country FROM Employees WHERE Country='India' UNION ALL SELECT City, PostalCode, Country FROM Projects WHERE Country='India' ORDER BY City; Output:CityPostalCodeCountryDelhi110006IndiaMumbai400015IndiaMumbai400015IndiaMumbai400015IndiaMoving forward in this article, let us understand how to use the UNION and UNION ALL clauses with JOINS. JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables.UNION with JOINSThe SQL UNION operator can be used with SQL JOINS to retrieve data from two different tables. I am going to consider the following table along with the Employees table for the example.ProjectDetails tablePIDWorkingDaysEmpIDCostforProject11124200002216335000333016000044253450005521150000 SELECT EmpID, Name, CostforProject FROM Employees LEFT JOIN ProjectDetails ON Employees.EmpID = ProjectDetails.EmpID UNION SELECT EmpID, Name, CostforProject FROM Employees RIGHT JOIN ProjectDetails ON Employees.EmpID = ProjectDetails.EmpID; Output:EmpIDNameCostforProject1Emma600001Emma500002RahulNULL3Aayra350003Aayra450004John200005DerekNULLUNION ALL with JOINSWrite a query to retrieve the EmpID, Name and CostforProject from Employees and ProjectDetails table, where duplicate values are allowed. SELECT EmpID, Name, CostforProject FROM Employees LEFT JOIN ProjectDetails ON Employees.EmpID = ProjectDetails.EmpID UNION ALL SELECT EmpID, Name, CostforProject FROM Employees RIGHT JOIN ProjectDetails ON Employees.EmpID = ProjectDetails.EmpID; Output:EmpIDNameCostforProject1Emma600001Emma500002RahulNULL3Aayra350003Aayra450004John200005DerekNULL4John200003Aayra350001Emma600003Aayra350001Emma50000By this, I come to the end of this article on SQL UNION. I hope you enjoyed reading this article on SQL UNION. We have seen the different ways to use the UNION and UNION ALL commands to help you write queries.If you wish to learn more aboutMySQLand get to knowthis open source relational database, then check out ourMySQL DBA Certification Trainingwhichcomes with instructor-led live training and real-life project experience.This training will help you understand MySQL in depth and help you achieve mastery over the subject.Got a question for us? Please mention it in the comments section of SQL UNION and I will get back to you.Recommended videos for you Build Application With MongoDB Watch Now Introduction to MongoDB Watch NowRecommended blogs for you Rising popularity of Hadoop and MongoDB ® in the industry Read Article How to use Auto Increment in SQL? Read Article SQL Tutorial : One Stop Solution to Learn SQL Read Article Introduction to Column Family with Cassandra Read Article SSIS Tutorial For Beginners: Why, What and How? Read Article Primary Key In SQL : Everything You Need To Know About Primary Key Operations Read Article Understanding MongoDB Architecture Read Article SQL Server Tutorial â€" Everything You Need To Master Transact-SQL Read Article Understanding Journaling in MongoDB Read Article Learn how to use SQL SELECT with examples Read Article MongoDB Vs Cassandra Read Article PL/SQL Tutorial : Everything You Need To Know About PL/SQL Read Article SQL Pivot â€" Know how to convert rows to columns Read Article What is SQLite browser and how to use it? Read Article Foreign Key SQL : Everything You Need To Know About Foreign Key Operations Read Article Top Apache Cassandra Interview Questions You Must Prepare In 2020 Read Article What is SQL Regex and how to implement it? Read Article SQL Views: How to work with Views in SQL? Read Article Introduction to Snitches in Cassandra Read Article SQL Union A Comprehensive Guide on the UNION Operator Read Article Comments 0 Comments Trending Courses in Databases SQL Essentials Training Certification6k Enrolled LearnersWeekend/WeekdaySelf Paced Reviews 5 (2400)