This lecture is about subqueries and we're going to be working in SQL Management Studio.
I just have a query window opened up, and if I ask you to write a query to give me the last name,
first name and salary of all employees, you would probably come up with something like this and that
would give you the information here.
And then if I ask you to give me the average salary of employees, you might come up with an aggregate
something like this, and that would give you the information.
But what if I ask you to give me the last name and first name and salary from employees, where their
salary was greater than the average salary for the company?
Well, in this case, we could actually combine these two.
And so I'll just put where salary is greater than.
And then we can just put our subquery in parentheses.
And the way this works is first it will return the value from this.
One important note about subqueries is subqueries always have to return only one value.
So in this case we are returning one value.
I can run this whole thing now and execute this.
And this is a list of all employees who have salaries greater than the average.
And of course we can use order by statements here and we will do salary.
If we want to do descending we can do that.
I'll execute this.
So essentially a subquery is when you place a query within another query.
And this is called the predicate because this is going to answer either true false or unknown.
But you can also place it in a column name as we'll see.
So I'm just going to put a comment up here.
I'm going to save this for the lecture notes.
You'll be able to go through and kind of play around with these queries.
And let's do another example.
Let's say we start out with wanting to get a list of all employees.
And so we would do select star from employees.
And then let's say we want to ask the question I want employees live in the same state as my customer
Amanda Joy.
And that is customer ID of 168.
So let's say that I don't know what state this is, but I know that I have a customer with this name
so I could get the primary key, which is always the best way to do this.
And then to my query, I could just add where the state ID is equal to.
And then I just write a query to go get the state for my customer.
So my customer here lives in California.
And so when I put these two queries together, I get a list of all employees that live in the same state
as Amanda, Joy or California.
So I'm just going to add the commentary here.
That was a subquery at the Where clause.
For our next example.
Let's use the in clause.
So this is going to be a subquery using in clause.
And let's say that we want a list of customers.
And so we can do select star from customers.
That's going to give us a list of all our customers.
And we want to only list customers who happen to live in a state where we have an employee.
And so this is a SQL query that gives us a distinct list of employee states.
So without the distinct keyword, then we're going to get all the states.
But you're going to have a bunch of repetitions okay.
We don't want that.
We want to use distinct.
And so we only get the selected states that we use in with no repetitions.
And so if I go in I can also put this in my Where clause.
And it's going to be where state.
Here's our in keyword.
And so it's saying where the state is in this list.
So if we do this whole thing together now we have a list of customers who live in states where we have
employees.
For the next example, we are going to look at using the Exists statement.
And the exist statement is a statement in SQL that always takes a subquery as an argument.
You're basically asking if something exists, like a customer who lives in a state or if a table exists
or a column exists.
So let's say that we wanted a list of vendors who lived in the same state as one of our customers,
and so we could write a query like this.
And I am selecting vendors.
Notice that I'm giving that an alias.
And I can actually use the alias in the subquery.
So this is a list of all vendors.
And then when I add the exist statement now I can't run this by itself because it doesn't know what
the state ID is.
I could probably put vendors and it would work, but if we run this whole thing, this is going to be
a list of all vendors who live in the same state as a customer.
And notice there's 18 rows.
So I could also put a not here.
I am going to actually just copy this and I am going to put the not keyword.
And so this query gives me a list of vendors who do not live in a state where we have a customer, and
that is going to give us two rows.
And if I just do a select from vendors, that gives us 20 rows.
So that kind of adds up because this one, 18 of our vendors live in the same state as customers to
do not.
So the exist statement is just another way to do certain queries or ask certain questions.
A lot of times there's going to be multiple ways you can use a join statement, you can use a subquery,
you can use different clauses to get the same results.
And so for this last example let's do a subquery as a column value in a query.
And so let's say that we want to select information from our customers table.
I'll just put a quick query up here.
And this is just selecting some columns from customers.
And I've given it an alias of C.
And let's say that we want to add a column that will give us the total number of orders they have placed
with us.
And I'm going to put this on the next line.
And we would just say select count order ID from orders where the customer ID.
So our order table does have a customer ID is equal to.
And that's going to be this lines customer ID.
So we have to say see customer ID.
And let's give it an alias as number orders.
And now we have this really nice SQL query that gives us customer information and the total number of
orders that they have.
Now, of course, I could do an equivalent join statement where I join the order tables and do an aggregate
on that.
So there's usually several ways to do a certain query.
You typically choose the one that's best for you, easiest to maintain and hopefully most efficient.
So that's a wrap on the lecture for subqueries.
Coming up, we have an assignment where you will get plenty of practice writing subqueries.
I will make the SQL file available to you as a class resource, and I'll see you in the next assignment.