Tutorial 40 - Subqueries Video Lecture | Postgresql: A Complete Tutorial - Database Management

LOL welcome to our tutorial of
PostgreSQL in the students I'm gonna
tell you about subways so first of all
let me tell you what our sub queries
sub query is a query within another
query so what do we mean here is a sub
query or it is also called an inner
query is a query within another query
and which is within of where clause so
if you want to query something first and
according to that query you wanna filter
your results then you could use a sub
query where the sub query runs first and
then the main query runs so a sub query
it returns data that is used in the main
query and it basically applies for the
restrictions on your data that could be
used with any of the operations such as
insert delete or update so we will start
with a normal select sub query and we
will step by step see it with the update
and delete as well so let me tell you by
starting with the Select subway so first
of all let me show you that table there
are a select star from company and I
have added some more results so I have
103 results now so I'll be using a
sub-query now and I'll be selecting
something so if I use this a query that
is select star from company where H in
and again a select age from company
where salary is greater than 4,000 so as
you could see that I have two queries
there and you must be confused that
which query would be running first or
what is this happening
so this way inside the paranthesis is
called the sub-query and this query
would be running first and the output of
this query would be used as the input in
this query so here select age from
company would be selecting age and it
will be returning age from the company
table where the salary of a person is
greater than four thousand here and then
it would give me the age a particular
age of people that is it would be in a
range a particular range so we have
learned about the inquiry so it selects
a particular set of results that are in
the in the input domain that we give so
this query would be running first and it
would give me a set of ages from this
query which would be used as an input in
this query and it would select star from
the company means it would select all
the records from the company where the
age is in this domain so let me run this
and you will see that the output is
restricted to 95 columns rows now so as
you could see that it returned the ages
of those persons whose salary was
greater than 4000 and it gives me a star
of those
so this was an example of a sub-query
now we could use it with the another
thing such as an update query so you
will just do by update company set
salary equal to salary into 1.50 their
age in select age company where age is
greater than or equal to 27 so if you
are running this query we are selecting
age from the company table their age is
greater than 27 so we'll be selecting
those ages that are greater than 27 in
this case and we will update the table
where the age in this domain and the
salary would be updated to 1.15 of the
current salary and you run this you see
that it had written successfully and 75
rows were affected you will do a select
star from company and you would see that
people having age greater than 27 there
sorry got updated in 21.5 so this is an
example you could use normal method you
can use a normal bear with this and you
don't need to use sub query but sub
query is very efficiently used sometimes
when you wanna you know query data from
some another table so what I mean here
is if you want to query something from
another table like I have a user's table
here or some another table here anything
you know product anything I have so I
can do anything from any table you know
in this sub query and this would return
me the data of that table so this is the
actual power of the sub query that I'm
talking about so the last thing we will
talk about is with a delete operation we
could just do delete from company where
H in and select age
company where age is greater than 50 so
this would delete all employees where
the age is greater than 50 this would
return me a set of ages and from this it
will compare the age and delete so you
can see that a basic idea of what a
sub-query is all about so you could use
sub-query in a more efficient or more
you know productive way rather than
using it like this so I'm telling you to
just give a basic idea of what a
sub-query is the real power of sub-query
comes when you use some other tables for
querying or you know to restrict the
data if you have a restriction on some
other table and you want to use it on
some other table then you use sub
queries mainly so this is just to give
you a basic idea of what is the power of
the sub query so if you run this you
would see that one row has been affected
and burned row has been deleted if you
do a select star from company you would
see that now I'm having a hundred and
two rows so yes it has been deleted so
thank you for industrial subscribe for
more and stay tuned