The set of all distinct rows from two or more result sets.

learn more… | top users | synonyms

7
votes
2answers
95 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
6
votes
1answer
6k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
6
votes
3answers
2k views

Optimize UNION query in MySQL

I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more ...
5
votes
2answers
7k views

Why won't SQL Server optimize the UNIONs?

Consider these queries (SQL Fiddle): Query 1: SELECT * INTO #TMP1 FROM Foo UNION SELECT * FROM Boo UNION SELECT * FROM Koo; Query 2: SELECT * INTO #TMP2 FROM Foo UNION SELECT * FROM Boo UNION ALL ...
5
votes
2answers
2k views

Perform UNION ALL regardless of column order in the two joined tables

SELECT * INTO TABLE1 FROM Table2 UNION ALL SELECT * FROM Table3; GO I am using this query to stack two tables together into one table. These tables should have the columns in the exact same order ...
4
votes
2answers
4k views

Query two SQL Server databases in same function but on two different servers

Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases. Is it possible to query both ...
4
votes
1answer
11k views

MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout. There is one table for each data source. For the log viewer, I want to UNION all the log tables, filter them ...
3
votes
4answers
657 views

Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I ...
3
votes
1answer
799 views

Unable to run union in parallel on SQL Server

I am unable to get this fairly simple query to parallelize the union operation: select va.ObjectId, 0 as IsFlag from Oav.ValueArray va where va.PropertyId = @pPropertyId ...
3
votes
1answer
179 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
3
votes
2answers
13k views

How to combine UNION and INNER JOIN?

I'm fetching the grandparent of all nodes in a tree as follows: SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P INNER JOIN product_groups GP ON P.parent = GP.id There is ...
3
votes
2answers
2k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
3
votes
1answer
367 views

How can I conditionally summarize multiple rows in Microsoft Access?

I have a table of data, in Microsoft Access 2003, listing multiple rows for each person. I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = ...
2
votes
2answers
2k views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
2
votes
2answers
2k views

Is there any way to speed up a query with 3x UNION on the same large table?

Assuming that the following query is legitimate and executes successfully, is there any way to speed it up? SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes UNION SELECT bar ...
2
votes
2answers
1k views

How can I do a differential query (delta plus/minus) telling me what rows are in view A that are not in view B and vice versa?

I have two views which use radically different means to accomplish what I hope is the same end view. One uses complex query expressions and walks entire giant tables and is highly expensive, and the ...
2
votes
2answers
215 views

MySQL : UNION script help

I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small. We run the followign ...
2
votes
2answers
2k views

Union of Columns

I have some SQL queries that retrun the following. Total Pre Orders Web Total Pre Orders Phone Total Orders Web Total Orders phone Each one of those queries retrurns a the query name and the count ...
2
votes
1answer
1k views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, ...
2
votes
1answer
1k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
2
votes
1answer
3k views

Dealing with NULL values and EMPTY strings in UNION of two tables

I want return records from two database tables when a record differs in at least one of the fields. I have written a query for it using the UNION operator. It is working absolutely fine. However, I ...
2
votes
1answer
71 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
2
votes
1answer
57 views

Selecting max(date) from union query

I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL. I am trying to get the max last login date from a union select on three ...
2
votes
2answers
61 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
2
votes
1answer
155 views

How to use COLLATE with UNION

How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal). I get error: sg 468, Level 16, State 9, Line 1 Cannot resolve ...
2
votes
1answer
2k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
1
vote
2answers
122 views

WITH Clause UNION Issue

WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice ...
1
vote
2answers
659 views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
2answers
4k views

MySQL - INSERT INTO … SELECT … UNION

I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a ...
1
vote
2answers
2k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
1
vote
1answer
3k views

MySQL - SELECT INTO OUTFILE, with a UNION

Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile? What I am trying to do, but not working: (SELECT email FROM table WHERE createdate ...
1
vote
2answers
92 views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more ...
1
vote
1answer
69 views

How to perform almost the same query N times without rewriting it N times?

Basically I have a list of numbers like: 1,2,3,4,5 And I want to perform a query like this for each of the previous numbers: SELECT COUNT(*), field_name FROM my_table WHERE field_name = NUMBER ...
1
vote
1answer
774 views

Fast query, in theory using index, still in the slow-query log

I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows. ...
1
vote
2answers
143 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
1
vote
3answers
879 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
128 views

Refactoring sql for tables with similar columns but different table names

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except ...
1
vote
1answer
83 views

Merge multiple “is exists” queries into one statement

I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement. Given the following tables: table table_one ======== id 'a' 'b' 'c' table table_two ======== ...
1
vote
1answer
3k views

How do I perform a UNION query on a dynamic list of tables?

My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>. Each of these tables has the same data structure. I also have another ...
1
vote
1answer
223 views

What is a good way to query data stored in multiple identical tables split by date/time

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
1
vote
1answer
25 views

To find out the sum total of GRNQty and GRNValue of particular item from column 1

Query 1 # SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue FROM suppdeldetail AS supdd ...
1
vote
0answers
44 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
1
vote
1answer
63 views

MySQL Summary with Union

The following query does what I want, but I need to extend it, but i'm just not enough of an SQL expert to figure out what I'm doing wrong. SELECT sd.StockNumber AS StkNm, sd.Description As Descr, ...
1
vote
0answers
18 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
2answers
77 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
52 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
0
votes
1answer
452 views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = ...
0
votes
1answer
41 views

Does PostgreSQL benefit from more cores or faster cores for READ heavy applications? [closed]

I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster ...
0
votes
2answers
50 views

How many passes of a table are done with UNION ALL? [closed]

I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be ...
0
votes
1answer
106 views

Optimizing key lookup in subquery with UNION ALL

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot ...