Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [derived-tables]

The tag has no usage guidance.

1
vote
3answers
129 views

Improve Query Performance of Select Statment inside IN Statment

My query is: (3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in ( (2)SELECT post_id FROM ( (1) ...
0
votes
1answer
46 views

SQL query does not recognise derived table

So I've made a simple SQL query: SELECT companyname FROM works w1, ( SELECT companyname,sum(salary) AS sumsal FROM works GROUP BY companyname ...
3
votes
1answer
52 views

Delete Joined to Derived Table Causes Index Scan and Table Spool

Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates. ...
11
votes
4answers
2k views

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
9
votes
5answers
13k views

Difference between inline view and WITH clause?

Inline views allow you to select from a subquery as if it were a different table: SELECT * FROM /* Selecting from a query instead of table */ ( SELECT c1 FROM ...
0
votes
2answers
263 views

Update statement using Case statement, derived table, and ROW_NUMBER

Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their ...
2
votes
1answer
55 views

mysql group by on table from two derived non-indexable

Working with mysql 5.7. I've got a query that ends up joining two derived tables and grouping on one of the columns. The query without the grouping runs pretty fast.... .5 seconds and returns 15K ...
2
votes
0answers
222 views

How does the use of derived tables speed up sorting?

In the article 45 Ways To Speed Up Firebird Database, it states that Use derived tables to optimize SELECT with ORDER BY/GROUP BY Another way to optimize SQL query with sorting is to use ...
1
vote
0answers
65 views

Get unique rows based on max of a column in mysql

I came across many solutions like the one in this question. However, they all are restricted to data from one table. I'm very very new to SQL. I'd like to get the same results as in the linked ...
0
votes
2answers
239 views

How are derived table implemented in MySQL

When MySQL makes a derived table as a result of SELECT (SELECT ...) etc or SELECT * FROM a JOIN (SELECT * from B) etc or SELECT * FROM (SELECT ...) Are these derived temporary tables created in-...
7
votes
2answers
18k views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...