Stack Overflow
Stack Overflow

Questions tagged [derived-table]

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

188 questions
567
votes
6 answers

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I…
700 Software
  • 85,281
  • 83
  • 234
  • 341
87
votes
5 answers

Create a SQL query to retrieve most recent records

I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the…
mattruma
  • 16,589
  • 32
  • 107
  • 171
71
votes
2 answers

Derived account balance vs stored account balance for a simple bank account?

Like our normal bank accounts we have a lot of transactions which result in inflow or outflow of money. The account balance can always be derived by simply summing up the transaction values. What would be better, storing the updated account balance…
Anmol Gupta
  • 2,797
  • 9
  • 27
  • 43
57
votes
5 answers

SELECT INTO USING UNION QUERY

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 Query2: Does not Work. Error: Msg 170, Level 15, State 1,…
Sekhar
  • 5,614
  • 9
  • 38
  • 44
21
votes
4 answers

How can I further optimize a derived table query which performs better than the JOINed equivalent?

UPDATE: I found a solution. See my Answer below. My Question How can I optimize this query to minimize my downtime? I need to update over 50 schemas with the number of tickets ranging from 100,000 to 2 million. Is it advisable to attempt to set all…
hobodave
  • 28,925
  • 4
  • 72
  • 77
14
votes
3 answers

SQL - Relationship between a SubQuery and an Outer Table

Problem I need to better understand the rules about when I can reference an outer table in a subquery and when (and why) that is an inappropriate request. I've discovered a duplication in an Oracle SQL query I'm trying to refactor but I'm running…
user2858650
11
votes
2 answers

Error Code: 1248. Every derived table must have its own alias No solution found for query

I am getting an error while using this query in MySQL. The query logic is correct and I have tried it in Oracle and it's running fine, but I'm getting an error when running this in MySQL. I looked at previous questions on StackOverflow, but didn't…
user1930857
9
votes
3 answers

SQL Left Join (Multiple Join Condition)

I have two derived tables named Check Ins and Check Outs Check Ins CheckDate CheckIn ---------- --------- 08/02/2011 10:10:03 08/02/2011 15:57:16 07/19/2011 13:58:52 07/19/2011 16:50:55 07/26/2011 15:11:24 06/21/2011 12:36:47 08/16/2011…
ELM
  • 529
  • 2
  • 7
  • 19
8
votes
2 answers

SQL Server Performance: derived table vs. common table expression (CTE)

Is there any performance gain using a CTE over a derived table?
D.S.
  • 1,413
  • 2
  • 16
  • 26
7
votes
1 answer

Laravel 5, Derived table in join clause?

I have this query: SELECT * FROM blog LEFT JOIN ( SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings GROUP BY (blog_id) ) T ON T.blog_id = blog.id; I do not know how to write this with Eloquent. For…
clzola
  • 1,925
  • 3
  • 30
  • 49
6
votes
3 answers

How do I tell the MySQL Optimizer to use the index on a derived table?

Suppose you have a query like this... SELECT T.TaskID, T.TaskName, TAU.AssignedUsers FROM `tasks` T LEFT OUTER JOIN ( SELECT TaskID, GROUP_CONCAT(U.FirstName, ' ', U.LastName SEPARATOR ', ') AS AssignedUsers FROM…
BMiner
  • 16,669
  • 12
  • 53
  • 53
6
votes
1 answer

SQL View vs Derived Table

I've always thought of a View as a stored query. Recently I needed to use Derived Tables in a project. That got me thinking about Views. Isn't a View the same thing as a Derived Table except that it has been saved as a logical entity where a Derived…
Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
5
votes
3 answers

When is it not appropriate to use Derived tables?

This SO post details some benefits in performance regarding Derived vs. Temporary tables. Other than performance, what situations exist for which a Derived table would not be appropriate. One answer per post with an example would be helpful.
Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82
4
votes
1 answer

Do indexes persist on derived tables?

Say I have a large table containing genomic positions from various files as follows: CREATE TABLE chromosomal_positions ( file_id INT, chromosome_id INT, position INT ) I want to compare the contents of 1 file to the contents of…
wobbily_col
  • 11,390
  • 12
  • 62
  • 86
3
votes
1 answer

Why is MySQL using 4 bytes for each character when selecting something from a derived table when it has a concat() using a utf8mb4 charset?

Starting from this table with a utf8mb4_unicode_ci collation: DROP TABLE IF EXISTS preferences; CREATE TABLE IF NOT EXISTS preferences ( id int auto_increment primary key, user_id int not null, title varchar(150) not…
Sneakyvv
  • 136
  • 5
1
2 3
…
12 13