1
00:00:05,360 --> 00:00:11,480
This lecture is about subqueries and we're going to be working in SQL Management Studio.

2
00:00:11,480 --> 00:00:18,380
I just have a query window opened up, and if I ask you to write a query to give me the last name,

3
00:00:18,380 --> 00:00:24,110
first name and salary of all employees, you would probably come up with something like this and that

4
00:00:24,110 --> 00:00:26,480
would give you the information here.

5
00:00:26,480 --> 00:00:35,670
And then if I ask you to give me the average salary of employees, you might come up with an aggregate

6
00:00:35,670 --> 00:00:39,510
something like this, and that would give you the information.

7
00:00:39,510 --> 00:00:47,430
But what if I ask you to give me the last name and first name and salary from employees, where their

8
00:00:47,430 --> 00:00:51,360
salary was greater than the average salary for the company?

9
00:00:51,360 --> 00:00:56,160
Well, in this case, we could actually combine these two.

10
00:00:56,160 --> 00:01:01,590
And so I'll just put where salary is greater than.

11
00:01:01,590 --> 00:01:06,480
And then we can just put our subquery in parentheses.

12
00:01:06,810 --> 00:01:11,700
And the way this works is first it will return the value from this.

13
00:01:11,700 --> 00:01:17,850
One important note about subqueries is subqueries always have to return only one value.

14
00:01:17,850 --> 00:01:20,230
So in this case we are returning one value.

15
00:01:20,230 --> 00:01:23,860
I can run this whole thing now and execute this.

16
00:01:23,860 --> 00:01:30,070
And this is a list of all employees who have salaries greater than the average.

17
00:01:30,670 --> 00:01:37,120
And of course we can use order by statements here and we will do salary.

18
00:01:37,120 --> 00:01:40,300
If we want to do descending we can do that.

19
00:01:40,300 --> 00:01:41,650
I'll execute this.

20
00:01:41,650 --> 00:01:47,540
So essentially a subquery is when you place a query within another query.

21
00:01:47,540 --> 00:01:53,540
And this is called the predicate because this is going to answer either true false or unknown.

22
00:01:53,540 --> 00:01:57,800
But you can also place it in a column name as we'll see.

23
00:01:57,800 --> 00:01:59,960
So I'm just going to put a comment up here.

24
00:01:59,960 --> 00:02:02,720
I'm going to save this for the lecture notes.

25
00:02:02,720 --> 00:02:05,390
You'll be able to go through and kind of play around with these queries.

26
00:02:05,390 --> 00:02:08,240
And let's do another example.

27
00:02:08,240 --> 00:02:14,280
Let's say we start out with wanting to get a list of all employees.

28
00:02:14,280 --> 00:02:16,800
And so we would do select star from employees.

29
00:02:16,800 --> 00:02:24,570
And then let's say we want to ask the question I want employees live in the same state as my customer

30
00:02:24,570 --> 00:02:25,680
Amanda Joy.

31
00:02:25,680 --> 00:02:29,250
And that is customer ID of 168.

32
00:02:29,250 --> 00:02:34,710
So let's say that I don't know what state this is, but I know that I have a customer with this name

33
00:02:34,710 --> 00:02:39,480
so I could get the primary key, which is always the best way to do this.

34
00:02:39,480 --> 00:02:46,170
And then to my query, I could just add where the state ID is equal to.

35
00:02:46,170 --> 00:02:50,730
And then I just write a query to go get the state for my customer.

36
00:02:50,730 --> 00:02:53,970
So my customer here lives in California.

37
00:02:53,970 --> 00:03:00,160
And so when I put these two queries together, I get a list of all employees that live in the same state

38
00:03:00,160 --> 00:03:03,280
as Amanda, Joy or California.

39
00:03:03,280 --> 00:03:05,530
So I'm just going to add the commentary here.

40
00:03:05,530 --> 00:03:07,900
That was a subquery at the Where clause.

41
00:03:07,900 --> 00:03:09,940
For our next example.

42
00:03:09,940 --> 00:03:11,530
Let's use the in clause.

43
00:03:11,530 --> 00:03:16,030
So this is going to be a subquery using in clause.

44
00:03:16,150 --> 00:03:20,680
And let's say that we want a list of customers.

45
00:03:20,680 --> 00:03:23,470
And so we can do select star from customers.

46
00:03:23,470 --> 00:03:25,520
That's going to give us a list of all our customers.

47
00:03:25,520 --> 00:03:33,140
And we want to only list customers who happen to live in a state where we have an employee.

48
00:03:33,140 --> 00:03:38,660
And so this is a SQL query that gives us a distinct list of employee states.

49
00:03:38,660 --> 00:03:43,250
So without the distinct keyword, then we're going to get all the states.

50
00:03:43,250 --> 00:03:45,890
But you're going to have a bunch of repetitions okay.

51
00:03:45,890 --> 00:03:47,030
We don't want that.

52
00:03:47,030 --> 00:03:48,890
We want to use distinct.

53
00:03:48,890 --> 00:03:55,230
And so we only get the selected states that we use in with no repetitions.

54
00:03:55,230 --> 00:04:00,450
And so if I go in I can also put this in my Where clause.

55
00:04:00,450 --> 00:04:02,010
And it's going to be where state.

56
00:04:02,010 --> 00:04:03,840
Here's our in keyword.

57
00:04:03,840 --> 00:04:07,590
And so it's saying where the state is in this list.

58
00:04:07,590 --> 00:04:15,240
So if we do this whole thing together now we have a list of customers who live in states where we have

59
00:04:15,240 --> 00:04:16,170
employees.

60
00:04:16,170 --> 00:04:21,570
For the next example, we are going to look at using the Exists statement.

61
00:04:21,570 --> 00:04:28,140
And the exist statement is a statement in SQL that always takes a subquery as an argument.

62
00:04:28,140 --> 00:04:34,680
You're basically asking if something exists, like a customer who lives in a state or if a table exists

63
00:04:34,680 --> 00:04:35,910
or a column exists.

64
00:04:35,910 --> 00:04:42,760
So let's say that we wanted a list of vendors who lived in the same state as one of our customers,

65
00:04:42,760 --> 00:04:45,550
and so we could write a query like this.

66
00:04:45,550 --> 00:04:47,320
And I am selecting vendors.

67
00:04:47,320 --> 00:04:49,540
Notice that I'm giving that an alias.

68
00:04:49,540 --> 00:04:53,830
And I can actually use the alias in the subquery.

69
00:04:53,830 --> 00:04:56,200
So this is a list of all vendors.

70
00:04:56,200 --> 00:05:04,520
And then when I add the exist statement now I can't run this by itself because it doesn't know what

71
00:05:04,520 --> 00:05:05,660
the state ID is.

72
00:05:05,660 --> 00:05:12,530
I could probably put vendors and it would work, but if we run this whole thing, this is going to be

73
00:05:12,530 --> 00:05:17,030
a list of all vendors who live in the same state as a customer.

74
00:05:17,030 --> 00:05:19,280
And notice there's 18 rows.

75
00:05:19,280 --> 00:05:22,700
So I could also put a not here.

76
00:05:22,700 --> 00:05:29,670
I am going to actually just copy this and I am going to put the not keyword.

77
00:05:29,670 --> 00:05:37,680
And so this query gives me a list of vendors who do not live in a state where we have a customer, and

78
00:05:37,680 --> 00:05:40,350
that is going to give us two rows.

79
00:05:40,350 --> 00:05:44,640
And if I just do a select from vendors, that gives us 20 rows.

80
00:05:44,640 --> 00:05:53,580
So that kind of adds up because this one, 18 of our vendors live in the same state as customers to

81
00:05:53,580 --> 00:05:54,720
do not.

82
00:05:54,990 --> 00:06:00,780
So the exist statement is just another way to do certain queries or ask certain questions.

83
00:06:00,810 --> 00:06:06,480
A lot of times there's going to be multiple ways you can use a join statement, you can use a subquery,

84
00:06:06,510 --> 00:06:10,470
you can use different clauses to get the same results.

85
00:06:10,470 --> 00:06:17,220
And so for this last example let's do a subquery as a column value in a query.

86
00:06:17,220 --> 00:06:22,090
And so let's say that we want to select information from our customers table.

87
00:06:22,090 --> 00:06:24,280
I'll just put a quick query up here.

88
00:06:24,280 --> 00:06:28,660
And this is just selecting some columns from customers.

89
00:06:28,660 --> 00:06:31,180
And I've given it an alias of C.

90
00:06:31,180 --> 00:06:38,290
And let's say that we want to add a column that will give us the total number of orders they have placed

91
00:06:38,290 --> 00:06:38,950
with us.

92
00:06:38,950 --> 00:06:41,530
And I'm going to put this on the next line.

93
00:06:41,530 --> 00:06:49,760
And we would just say select count order ID from orders where the customer ID.

94
00:06:49,790 --> 00:06:54,410
So our order table does have a customer ID is equal to.

95
00:06:54,410 --> 00:06:57,500
And that's going to be this lines customer ID.

96
00:06:57,530 --> 00:07:00,830
So we have to say see customer ID.

97
00:07:00,830 --> 00:07:05,690
And let's give it an alias as number orders.

98
00:07:05,690 --> 00:07:13,770
And now we have this really nice SQL query that gives us customer information and the total number of

99
00:07:13,770 --> 00:07:15,000
orders that they have.

100
00:07:15,000 --> 00:07:21,180
Now, of course, I could do an equivalent join statement where I join the order tables and do an aggregate

101
00:07:21,180 --> 00:07:21,930
on that.

102
00:07:21,930 --> 00:07:26,100
So there's usually several ways to do a certain query.

103
00:07:26,100 --> 00:07:32,430
You typically choose the one that's best for you, easiest to maintain and hopefully most efficient.

104
00:07:32,970 --> 00:07:35,910
So that's a wrap on the lecture for subqueries.

105
00:07:35,910 --> 00:07:40,260
Coming up, we have an assignment where you will get plenty of practice writing subqueries.

106
00:07:40,260 --> 00:07:47,490
I will make the SQL file available to you as a class resource, and I'll see you in the next assignment.