How to use nested select commands in Mysql?

 Hello readers we are back with article . Hope all are doing well in this pandemic . Explore all the stuff in our blog and we need your support.

I had previously written an article based on Introduction to SQL and ER diagram in SQL. In this present article we are gonna learn about nested select commands.

SELECT command is a command in mysql which is used to retrieve data from a table based on a condition. It comes with a syntax :

SELECT column/column name from table name WHERE condition;  

For example in the below table named 'shoe' you are given a query to select a brand where price is less than 2000.

Shoeid	Brand	Price
463947	Puma	8999
639575	Sparx	1999
732832	adidas	1499
102837	Puma	2983
987282	adidas	4999
929833	Fila	1499

The code will be :

SELECT Brand FROM shoe WHERE Price<2000; 

Result:

Shoeid
639575
732832

now consider another table 'size chart' given below:

Shoe id	Brand	size
463947	Puma	8
639575	Sparx	8
732832	adidas	9
102837	Puma	8
987282	adidas	9
929833	Fila	10

For example  in the above table you are asked to select brand which has shoe size 8 the code will be:

SELECT Brand FROM size chart WHERE size=8;

Result:

Brand
Puma
Sparx
Puma

Now you are asked to solve a query using both the table in this case you will need to use the nested select statements to solve sub queries.

Now let us take the query as:

retrieve Shoeid, Brand and Price from table shoe where Shoeid has the size 8.

In this query we have to retrieve the columns present in table 'shoe' based on the column 'size' present in 'size chart'.

Looking complex? Come on let's try this out!!

SELECT shoeid, Brand, Price FROM shoe WHERE Shoeid in (SELECT shoeid FROM sizechart WHERE size=8);

Result:

Shoe id	Brand	price
463947	Puma	1999
639575	Sparx	1499
102837	Puma	2983

 

Other query:

Retrieve shoe's Brand and size where shoe's brand's prize is greater than 2000. Try this out.

SELECT Brand, size from size chart WHERE Brand in (SELECT Brand FROM shoe WHERE price>2000);

Note : You use '=' symbol to check for integers like price=100. If you want to check for strings don't use '=' just use in ('string') example: Brand in ('puma').

I hope this article was useful and please be visiting our blog.

Thanks for reading.