UNION

Quick reference

• 00:05 In this video, I want to talk about the union operator and the union operator,
• 00:09 it sort of does what it seems like it would do.
• 00:12 It's a union, it combines the results of two or more select statements.
• 00:17 Now usually, you're gonna use this for different tables.
• 00:20 And we'll talk about accessing multiple tables later on.
• 00:24 Right now, I'm just gonna use the union operator with our current table.
• 00:27 So before we get started,
• 00:29 there's a few things you need to know about the union operator.
• 00:32 And this makes it kinda hard to work with sometimes.
• 00:35 But, none the less, you need to know these things.
• 00:37 So, every select statement has to have the same number of columns.
• 00:41 So if you're using two select statements, it has to have the same number.
• 00:44 Both of them have to have the same number of columns,
• 00:46 otherwise you're gonna get an error.
• 00:47 The columns also have to have the same data types.
• 00:51 So if we look at our design thing here,
• 00:57 we have our data types, email address, and country.
• 00:59 We can use a union statement for this two, but not for like, say, age and country,
• 01:04 because they have different data types.
• 01:05 Also, text, you can't use union for text data types.
• 01:09 That's just another shortcoming of the text data type that we
• 01:12 keep running across throughout this course.
• 01:14 So those are the first two things.
• 01:15 The third thing you need to know is that the columns in each select statement
• 01:19 have to be in the same order.
• 01:21 So just three things you need to sort of be aware of, and
• 01:24 a lot of times, those prohibitions, I guess you would call them,
• 01:27 make use using the union operator kind of hard to do, but, nonetheless,
• 01:30 we are going to give it a try, so let's come back here and look at our design.
• 01:35 What did we say email address and country,
• 01:37 they have the same varchar data type so we can use those.
• 01:40 Let's start out with select email address from customers to.
• 01:47 Now, to use the union operator, you just type in UNION, and then go ahead and
• 01:53 write another select statement, because you're joining,
• 01:58 you're unioning two select statements, so our next one was country.
• 02:04 Okay, and we can kinda tab these around.
• 02:07 Make them easier to look at.
• 02:10 So that's it.
• 02:11 So if we execute this, what we get is one column
• 02:14 with everything from our Email Address column and our Country column.
• 02:18 And you can see it's sort of a jumbled mess.
• 02:21 If you look at it, it looks to be in ascending alphabetical order.
• 02:24 So ABC, DEFGHIJ, KLM, PQRS, TU.
• 02:30 So ascending alphabetical order.
• 02:32 So that's the union operator.
• 02:34 So you might be thinking, why not just come up here, instead of using union,
• 02:40 just slap in Country, like this, like we've done in the past.
• 02:43 So if we delete that and run this, you see, well then two columns.
• 02:48 And that may work in some instances, in some it may not.
• 02:51 So you may want all this data in one column for
• 02:54 some specific reason and in that case you would use Union.
• 02:58 And also, like I said at the beginning of this video,
• 03:00 you're going to use Union a lot for pulling data from different tables.
• 03:05 And right now we only have one table to pull from, but you can
• 03:08 sort of imagine if you had three or four or five or 100 different tables and
• 03:12 you wanted to pull data from each one and slap them all into one column like this,
• 03:16 and then do stuff with them, this would be a great and easy way to do it.
• 03:19 And, in fact we can make a table really quickly here and just see if.
• 03:22 Come to Tables, right click, new Table.
• 03:25 Let's go email, email address, keep it simple,
• 03:29 it has to be the same, so varchar50 and now if we just save this.
• 03:34 Yes.
• 03:34 And let's call this Past_Customers.
• 03:40 So if we come back here to our customer reload this table so
• 03:45 we could get this Past_Customers and
• 03:48 let's just really quickly create bob@bob.com,
• 03:53 tim@tim.com, sally@sally.com whatever,
• 03:58 just a few simple email addresses.
• 04:01 So if we close this, now we come back to our statement here and get rid of, or
• 04:06 we could just change this.
• 04:09 Now we want the email addresses from our current customers and
• 04:12 we all want it from our past customers as well.
• 04:14 So Email Address, and we'll just change the name, so
• 04:19 this is what you'll do if you have multiple to pool tables, Past_Customers,
• 04:24 everything else stays the same.
• 04:25 And you notice, well, first I'm gonna save this and execute it.
• 04:30 So we get six results from our Current customer,
• 04:33 now if we add back in this union run it again and
• 04:36 now we get nine results, we get that Bob, Sally and Tim, and we just add.
• 04:41 So that's how you use Union with separate tables, very easy,
• 04:44 very simple to do and this is a pretty good example.
• 04:47 You may have email addresses from current customers, past customers,
• 04:51 revolving customers, old customers, whatever different tables you have,
• 04:56 you might wanna use union to smoosh them all into one big report if you're gonna,
• 05:00 for instance, send out an email, newsletter, or something.
• 05:03 So that's the union operator.
• 05:04 In the next video, we will look at functions.

Lesson notes are only available for subscribers.