SQL: UNION vs. JOIN

Instructor: Kaitlin Oglesby

Kaitlin has a BA in political science and experience teaching.

When you have to compare data from different tables, you have three choices. You can make a new table, or you can use either UNION or JOIN. This lesson shows you which of those two to use.

UNION vs. JOIN

While it may not always seem that way, there are times when you'll want to create a way to view data without building a new table. Maybe a new table would slow your database down too much, or perhaps it would simply be easier to pick and choose the data that you need in the moment to get things done. In that case, you'll want to know about the UNION and JOIN options in SQL. However, as this lesson will demonstrate, the two functions do rather different tasks, and confusing them is not advisable.

What Does UNION Do?

Let's start with UNION because it is perhaps the more straightforward of the two. UNION simply adds the relevant parts of two tables together. This way you don't have to constantly flip between tables, but instead can make a quick UNION to get all the information you need. Extra information will just appear below the existing information, sorted by the original table. Let's see this in action.

Example of UNION

Imagine that the university that you work at has begun a major fund-raising initiative, and rather than just ask each college or school to help get more funding, they've asked you to build a master table of all major donors for the entire university. Now you could spend hours and hours creating this new database, but it would be much easier to just use a UNION command. Here's how you'd do it.

Let's say you're combining data from two different tables, 'undergrad_college' and 'business_school'. Now, you could just list these two, but there may be some overlap - maybe someone who studied English as an undergrad returned for her MBA? Instead, you'll want to sort out any other names. To do that, we'll use social security numbers. As such, try this command:

SELECT name FROM undergrad_college
UNION
SELECT name FROM business_school
WHERE ssn NOT IN
(SELECT name FROM undergrad_college);

It's that simple - you'll soon have your combined list with no redundant names!

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support