Copyright

SQL Complex Queries: Functionality & Examples

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: SQL Views: Definition & Example

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 SQL Complex Queries
  • 0:22 Inner Join
  • 1:03 Outer Join
  • 2:39 Union
  • 2:58 Stored Procedures
  • 3:51 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

In this lesson, you'll learn some more advanced functions of SQL such as joins and stored procedures. These techniques are defined and working code examples are provided.

SQL Complex Queries

Consider the huge amount of data that would be present in a music database like Spotify. Sometimes you want to search for more than just a single artist or album, or maybe you want to pull together different subsets of data. Let's first take a look at how this might work if you were a database administrator working with an inner join method.

Inner Join

Let's say that you have a music database. Albums, artist, genres, reviews, etc. are all stored in separate tables. If you want to get all records from two tables that are related, you can use a simple inner join. This selects data where the values match in both tables. Check out these tables. The first one is some sample data and the second one is the albums table:

Sample Data

artistID artistName
1 Journey
2 Meat Loaf
3 Enya
4 Kate Wolf
5 Aerosmith

Albums table

albumID artistID albumTitle
1 1 Raised on Radio
2 1 Greatest Hits
3 2 Bat out of Hell
4 2 Dead Ringer
5 3 The Celts
6 4 Poet's Heart

This code is how the inner join would appear:

SELECT *
FROM tblAlbum
INNER JOIN tblArtist ON
tblAlbum.artistID = tblArtist.artistID;

Now, with this new table we can see that the result is 6 rows because we only care about those records where the IDs match.

albumIID tblAlbum.artistID albumTitle tblArtist.artistID artistName
1 1 Raised on Radio 1 Journey
2 2 Greatest Hits 1 Journey
3 2 Bat out of Hell 2 Meat Loaf
4 2 Dead Ringer 2 Meat Loaf
5 3 The Celts 3 Enya
6 4 Poet's Heart 4 Kate Wolf

Now, here is a graphic representing an inner join. Looks pretty simple, right?


SQL inner join graphic


Outer Join

Now, let's take a closer look at using the outer join method. A left outer join returns ALL matching records in the left-hand table, plus the records that match in the middle (the inner join).

The SQL statement is as follows:

SELECT * FROM tblArtist
LEFT OUTER JOIN tblAlbum ON
tblArtist.artistID = tblAlbum.artistID;

This generates the seven rows because now we get the record for Aerosmith. This band doesn't have any albums entered yet, since there are so many to add. However, when running the left outer join, we get to see their entry.

tblArtist.artistID artistName albumID tblAlbum.artistID albumTitle
1 Journey 1 1 Raised on Radio
1 Journey 2 1 Greatest Hits
2 Meat Loaf 3 2 Bat out of Hell
2 Meat Loaf 4 2 Dead Ringer
3 Enya 5 3 The Celts
4 Kate Wolf 6 4 Poet's Heart
5 Aerosmith

And now, here is a graphic that represents the left outer join:


SQL left outer join graphic


Right Outer Join

Now, let's take a look at the right outer join method. Let's say that you had an album called Unknown, and it wasn't yet tied to an artist quite yet. To retrieve this data element, you can use the right outer join. This is like the left outer, except you retrieve matching records plus those in the right-hand table.

Here is the SQL statement:

SELECT *
FROM tblArtist RIGHT OUTER JOIN tblAlbum ON tblArtist.artistID = tblAlbum.artistID;

Here is a graphic that shows a right outer join:


SQL right outer join graphic


Full Outer Join (Rows That Don't Join)

Now let's take a look a full outer join, in which we have rows that don't join. Consider that we've inserted some new albums and some new artists in the database, but we haven't joined them together. You can see this playing out in the code:

SELECT * FROM tblArtist
FULL OUTER JOIN tblAlbum ON
tblArtist.artistID = tblAlbum.albumID
WHERE
tblArtist.artistID = NULL or tblAlbum.artistID = NULL;

This would result in something like this table:

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