Questions tagged [derived-tables]
16 questions
13
votes
4 answers
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 DISTINCT, GROUP BY, JOIN or any other WHERE clause…
Vlad Mihalcea
- 917
- 3
- 9
- 23
9
votes
5 answers
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
t1
WHERE
c1 > 0
)…
Kshitiz Sharma
- 3,357
- 9
- 33
- 35
8
votes
2 answers
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 want to be sure I am using the best choice.
CREATE…
norgematos
- 241
- 2
- 3
- 6
5
votes
1 answer
Merge Delete on joined tables is allowed but has a bug
To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table.
But they cannot be the target of a DELETE, even if columns…
Charlieface
- 17,078
- 22
- 44
3
votes
1 answer
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.
It seems that the query pattern produced by the…
Bryan Rebok
- 1,219
- 9
- 16
2
votes
1 answer
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 rows (In production I would expect that to possibly…
Josh
- 159
- 2
- 8
2
votes
1 answer
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 derived
tables to avoid unnecessary sort operations.…
oals
- 121
- 2
1
vote
0 answers
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 question with two tweaks.
The column 'User' is from…
abn
- 111
- 3
1
vote
1 answer
Is it possible to reduce the duplication in this simple query?
I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string concatenation from the language I'm using to generate the…
user225326
- 13
- 3
1
vote
3 answers
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) SELECT A.post_id from wp_postmeta A
…
HOY
- 121
- 7
0
votes
2 answers
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-memory or the file-system? Does it depend on the…
Cratylus
- 1,013
- 3
- 12
- 18
0
votes
1 answer
MySQL query - Passing variable into subquery's derived table
Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?
I am trying identify "active" organizations, and there are multiple actions that constitute "activity". One is creating an invoice,…
Jared Anderton
- 101
0
votes
2 answers
Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ignored. So, the rows are updateds based in primary…
0
votes
1 answer
mysql query with 1 derived table and 1 inner join takes forever to load
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows of data. This works perfectly fine if I have…
0
votes
1 answer
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
) w2
WHERE
w1.companyname=w2.companyname
it…
mrmagin
- 3
- 3