All language subtitles for 1. Subqueries

Hi everyone
1

In this lecture we will be discussing about subqueries
2

Subqueries are a very powerful tool and you must know this
3

What is a subquery? It is a
4

Query within a query
5

Till now we've been retrieving raw data or very simple processed data
6

If you want to retrieve Complex data we might have to use Complex structured query with a lot of
7

Joins and unions
8

This is where subquery comes in
9

You can use a sub query to reduce your efforts
10

Make the query more readable
11

Reduce the time taken and even save some resources on your database
12

Subqueries can reside within the where clause
13

The from clause or the select clause
14

Syntax I have provided here
15

Is how to use the subquery in the where clause
16

It is like a normal query it is starting with select column names from table name
17

Where you specify the column name
18

Give a comparison operator
19

And after this comes the query
20

This query is part of the main query the outer query
21

And this part is called the sub query
22

Where you write select the column names from table name and give the where condition
23

the output of this subquery will be used
24

as values in the comparison operator
25

Against which the column name 2 the column name 2 written here
26

The values of this column will be compared
27

It will become more clear when we look at the examples
28

Let us say you want to find out
29

the order lines or the sales items
30

where the customer who bought those orders is aged more than 60
31

If we do not know about subqueries how would we do this. We will first find out all the customers
32

Who are aged more than 60
33

Probably store it in a new table
34

and then we'll take an inner join of this sales table and the new table that we have created
35

to find out customers whose age is 60 and who have given orders
36

instead of doing this we'll just use the subquery. first we will find out all the sales
37

From the sales table
38

In the condition we will say
39

Only those customer IDs which belong to this sub query
40

I need this subquery will find out those customer IDs
41

Where customer's age is more than 60
42

so we will get
43

Customer IDs
44

here from this subquery
45

We'll check the customer ID belonging to this set
46

and for only those customer IDs we will get the select result
47

Let's go and write this in the interface
48

So we will select
49

Star from sales
50

Where
51

Customer ID
52

Belongs to
53

where customer ID belongs to a set and we'll define this set by
54

Select
55

customer ID
56

From
57

The customer table
58

Where age is
59

More than 60
60

So if you remember while discussing the IN keyword
61

I told you that I'll be using this IN
62

in the sub queries and there is a lot of
63

Importance of this IN keyword in subqueries. so this is how we use IN
64

during some queries
65

and let us run this query
66

Here you can see
67

I have all the sales
68

Where the customer who has bought is aged more than 60
69

Do note that while selecting in the subquery
70

We had only one column which is customer ID
71

We cannot have the second column because when we are having this IN keyword
72

This IN keyboard wants to compare
73

Customer ID
74

In this
75

range. These are the values that we are giving
76

So it cannot have two columns. It should have only one column
77

Which has the all the values listed in it
78

So this one column
79

Which contains the customer IDs of customers with more than age 60 will be used to compare the customer IDs
80

And give the output from sales table
81

so we just checked how to use the subquery in where
82

Let us now go and learn how to use the subquery within from
83

Now we have a table, product table which list all the products that we have in our inventory
84

Suppose we want to find out how much of each product has been sold
85

I want to find out the quantity of each product sold
86

In the product table I have the product name and categories also
87

So, In future if I have this data I'll be able to find out which category is selling how much
88

So this data may be of use to me
89

If I want to find out this, suppose we are not using the sub queries. I'll first need to find out the sales
90

quantity from the sales table
91

I'll group by
92

the product ID and find out the sum of quantity and save this data in a new table
93

And then I'll do a join of this table with the product table
94

This is how I will get this data
95

What this will lead to is I'll have one additional table which will be taking up resources in my database. Instead of doing this
96

I can do the two things in a single query without saving the data into a table
97

Let us see how subquery helps us in this
98

First the table that we would be saving think of it as a table that you have actually saved
99

That table will be created in the sub query and we'll save that table with some name. you can take out
100

The quantity from the table
101

When we are writing the query we'll select all the columns from the product table
102

And
103

this quantity
104

From the
105

Table that we'll be creating in the subquery
106

After this we'll write the product table and name it as a
107

We will left join this
108

Why we are left joining, you should be knowing
109

All the values of product table will be listed even if it is not part of this second table which will be the subquery
110

So we are using left join
111

This is the 2nd table
112

We'll select the product ID
113

sum the quantity and we group it by the
114

Product ID
115

and we save it as b
116

we are joining these two
117

With the common predicate product ID
118

and we order this with the quantity
119

Let's go and write this and see the result
120

we will select
121

Product ID
122

Product name
123

Add category
124

from the second table
125

That we'll be creating, we need
126

the quantity ok
127

This quantity is actually
128

This quantity
129

Is also an alias so from
130

The table is product
131

which is saved as a
132

After this we will left join
133

this is the subquery part now. select product ID
134

Sum of
135

The quantity
136

As quantity
137

Now we have
138

the Alias
139

From sales
140

Table
141

Grouped by the product ID
142

this whole subquery part
143

Is giving us a table and that table is named as b
144

and we will use the
145

Condition of
146

Product ID of A is same as product ID of B
147

This is the condition that we need to give for the left join
148

Ordered by
149

Quantity in the descending order
150

You can see
151

for
152

We now have all the products
153

from the product table
154

And the quantities of that product sold
155

Mapped against each product
156

you can see that First row has actually null quantity
157

You might be wondering why it is at the top
158

Because null is always considered as the largest number
159

Whenever you order it in descending order Null will always be at top if you order it in ascending order
160

Null will always be at the bottom
161

The same thing you could have done
162

by
163

Creating two different queries
164

Saving the table in between
165

Instead of doing that we just clubbed the two queries
166

One query become a sub query for the other outer query
167

and this saved us some space on the database
168

Now let us look at the third use case of
169

Subquery
170

Here the subquery will be written within the
171

Select part
172

First we wrote it in the where part next we wrote it in the from part now we are writing it in the select
173

part
174

Writing a subquery in the select part is just
175

The same as the left join
176

For example in left join we found out for each order line what is the customer name and age
177

Same thing you can do by using the sub queries also
178

How
179

We'll select customer ID and order line
180

these are directly coming from the sales table
181

but the 3rd column which is the customer name
182

Will come from the customer table and it'll be matched
183

Using the customer ID
184

So customer ID order line
185

And there is a subquery
186

Giving us the customer name
187

from the customer table
188

Let us go and write this in the
189

Interface
190

So we select
191

Customer ID
192

orderline
193

and then we'll write
194

the subquery where we'll find out the customer name
195

Which is matching the customer id from the sales table
196

Customer ID
197

Customer dot customer ID is equal to sales dot customer ID
198

These customer IDs are matched and we get the
199

Customer name
200

from the sales table
201

by customer
202

Select this
203

run it
204

so we get the data
205

These are all the customer IDs in the sales table
206

And wherever customer name is available from the customer table it is mapped here
207

The same thing we did using the
208

Left join
209

However, whenever you have the option of using left join
210

vs subquery
211

I will suggest that we go with the
212

Joins, since they perform better in the database
213

subqueries may take up more resources of the database while performing and it may give result after taking a long time
214

There are few rules
215

I'll mention two or three important ones
216

First is subqueries must be enclosed within parentheses
217

We have been putting brackets around the subqueries
218

So always remember to put brackets
219

and the second point
220

A subquery can have only one column in the select clause
221

Where multiple columns are in the main query for the query subquery to compare date selected column so what this point means is
222

Like we discussed here
223

In this example
224

When we were discussing this IN keyword
225

Here only one column should be present
226

It cannot be be more than one columns
227

That is what this point is signifying
228

So whenever you are comparing values using the subquery
229

It should have only one column
230

Another important point is this last point where between operator
231

It can be used within a subquery
232

It cannot be used with a subquery, so if within a query you are having a subquery you cannot use
233

Between operator like we used IN operator
234

Between cannot be used there
235

But within a subquery you can use between operator
236

These are some important points
237

We will not be discussing other points
238

Just read them. this presentation will be part of the resources of this lecture
239

And that is all for subqueries
240

In the next video we will learn about views and how they are useful