Union and Union All

Saad Qureshi
A free video tutorial from Saad Qureshi
Computer Scientist || Freelancer || Programmer
3.9 instructor rating • 8 courses • 8,824 students

Learn more from the full course

SQLite Fundamentals - Comprehensive SQL course on SQLite

A comprehensive course on SQLite Fundamentals and SQL

04:53:21 of on-demand video • Updated August 2019

  • Learn to write SQL queries in SQLite database
  • Hands on experience of all the major concepts.
  • Answer variety of database problems through SQL
  • At the end of this course, You will be able to use SQL statements in any database management system.
English [Auto] In this lecture we're going to discuss union and union all operator so in a school board. Union and Union all operator are used to combined the results sets off two or more select statements. So but the difference between these two operators are union function. Remove the duplicate value whereas when it comes to union all function it includes the duplicate value. So let me give you an example here. Select staff from department. So this is my first table. So my next table will be location Okay location. So let me query this table. So in this table I have a column called location underscore idea. So let me query this location table. So in this table I have a column location underscore right. Okay. So I'm going to take a union off this table's location I deserve it. This table's location idea. Okay. So let me query this table first query this location. Underscore rightly. So in this column I have different values one twenty five six seven eight nine. Okay so location I'd select McQueary this table. Okay so I'm going to take a union of this statement with this statement. Okay. So union this. So as I said union function will remove the duplicate values. Look all unique values one twenty five six seven eight nine thirty detective five. Okay. So if I take union all it will include the duplicate value. Let me query this. Look. One twenty five six seven eight nine. So wait a second. Or thereby one ascending order. So now created this look these are duplicate values. Okay. It will include the duplicate value 127. So these are duplicate values. So another important thing it is then you are using these functions number of columns should be same like here in the first statement. Number of column is 1 in the second statement. Number of column is 1. I cannot have a different number of columns. Look. Okay. Let's say I cannot do like this okay. Look different number of columns. So number of columns should be same. So let's change your location and let's go right. Okay. So this will not work. So in terms of performance which is better union function or union all function. So since union function has to do extra effort to remove the duplicate value. So therefore union all function is better because it doesn't have to do anything. Okay. It will include all the duplicate values. So hope you understood the concept of union and union all function. So thank you so much and have a great learning.