Click here to Skip to main content
14,387,532 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,
Posted
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[^]
https://www.tutorialspoint.com/What-is-the-concept-of-a-derived-table-concerned-with-MySQL-subquery[^]

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[^]
   
v4
Comments
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
   
5ed!
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: https://haydenjames.io/mysql-query-cache-size-performance/

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