Click here to Skip to main content
14,391,734 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have confused of when to used and why , which is best solution

Just want to know Use Cases of following terms In MySQL

1) View
2) Temporary tables
3) Derived tables
4) Sub queries

I want to know which are use cases for above , when to used above queries

And alternative for above

What I have tried:

i tried to read various tutorial but couldn't find proper explanation,
Updated 7-Nov-19 3:46am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

See the following tutorials:
SQL - Using Views - Tutorialspoint[^]
SQL - Temporary Tables - Tutorialspoint[^]
SQL - Sub Queries - Tutorialspoint[^][^]

A derived Table is just another name for the result of a Subquery.
If you have complicated queries you can save yourself a lot of typing by using Views, it can also be used to restrict access for users.

Temporary tables are not needed if your needs are simple and normal queries suffice.
There can be cases however that temporary tables can be useful, like when you need the results of a query that is very slow several times.
Also temporary tables are often used in Stored Procedures, see: sql - Creating temporary tables in MySQL Stored Procedure - Stack Overflow[^]

Another option is using CTE (Common Table Expressions), see: An Introduction to MySQL CTE[^]
Akshay malvankar 7-Nov-19 23:50pm
Hey i know there definition , but i want use cases for them when i choose to used view or temporary tables, when to used derived table , i read that derived table is alternative for temporary table , that i need to know , because i am get confused what is to used and when??
RickZeeland 8-Nov-19 1:55am
Well that all depends, and they can be used in several ways, I tried to explain briefly the most common uses in the updated solution :)
Maciej Los 8-Nov-19 2:09am
Akshay malvankar 12-Nov-19 23:39pm
one more thing if there is any alternative for temporary tables and derived table
RickZeeland 13-Nov-19 2:02am
In older MySQL versions there was also the option of caching, but this is not recommended, see:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100