Stack Overflow
Stack Overflow

Questions tagged [union-all]

"UNION ALL" is a keyword in SQL that is used for combining the results of multiple SELECTs. The related tag is "UNION". "UNION ALL" combines the results without checking for Uniqueness. "UNION" combines the results eliminating duplicates.

UNION ALL is a keyword in SQL that is used for combining the results of multiple SELECTs. The related tag is UNION (). UNION ALL combines the results without checking for uniqueness. UNION combines the results eliminating duplicates. The type and order of the fields in the two SELECTs should be the same.

775 questions
2
votes
1 answer

Using "Union All" with Queries Between Databases

I have a interesting situation that I want to see if there is a solution to...I have 4 tables in 4 databases on the same server, all with the same structure: dbo.IL_Membership in Pnl_IL_2012 database dbo.NM_Membership in Pnl_NM_2012 database I want…
digitalcb
  • 47
  • 2
  • 8
2
votes
3 answers

MySQL Join Return Null

I have two tables Table_1 id|subject_code|subject_id 1|Test1 |1 2|Test2 |2 3|Test3 |3 Table2 id|subject_id|grade|status 1|1 |5.00 |Fail 2|3 |2.25 |Pass Now, I want to create a query that will return the…
Ruben_PH
  • 1,692
  • 7
  • 25
  • 42
2
votes
1 answer

Select two distinct sets of rows for one column in one statement?

I have a table with 2 columns: name, percentage I have a 100 rows in this table, and want to make a query that selects the 5 rows with the smallest percentage value and the 5 rows with the largest percentage value. Normally I would do this with…
Lay
  • 249
  • 1
  • 3
  • 15
2
votes
1 answer

Why is UNION ALL with and without parenthesis behaving different?

I have this query implemented in two ways: SELECT race1, mode1 FROM organization WHERE condition = 1 LIMIT 1 UNION ALL (SELECT race2, mode2 FROM organization WHERE condition = 1 LIMIT 1) UNION ALL (SELECT race, mode FROM …
nawfal
  • 70,104
  • 56
  • 326
  • 368
1
vote
1 answer

Multiple Inserts with XML vs INSERT...VALUES...SELECT...UNION ALL

For SQL Server 2005, if I want to insert about 20 or 30 rows each with three columns, which approach is faster and efficient? The calling application is a C# 4.0 .Net console application. a) Passing the values as XML to a stored proc and parsing it…
FMFF
  • 1,652
  • 4
  • 32
  • 62
1
vote
3 answers

How do I effectively select the average sum of several sums being calculated based on different timestamps in SQL?

I have a database table looking like the following: id | macaddr | load | timestamp ========================================= 1 | 0011111 | 17 | 2012-02-07 10:00:00 1 | 0011111 | 6 | 2012-02-07 12:00:00 2 | 0022222 | 3 | 2012-02-07…
mikey
  • 161
  • 9
1
vote
4 answers

SQL - Is it possible to join a table to a resultset created by several select/union-alls?

I'm trying to find a workaround (hack) to some limitations preventing me from using a temporary table or a table variable in my SQL query. I have a real table (technically it's a derived table that results from an UNPIVOT of a poorly designed table)…
STW
  • 44,917
  • 17
  • 105
  • 161
1
vote
3 answers

Emulating full join in MYSQL with large dataset

I have got three tables whose data I need to join based on a common field. sample pseudo table defs: barometer_log(device ,pressure float,sampleTime timestamp) temperature_log(device int,temperature float,sampleTime timestamp) magnitude_log(device…
anzaan
  • 245
  • 1
  • 5
1
vote
2 answers

Can a query that uses the same filtered table for both unpivoting and joining be written without repetition?

Consider the following query: create function unpivoter(@TableID int) returns table as return ( select a, b from ( select foo1, foo2, foo3, foo4 from table1 where table1.id = @TableID ) tab_in unpivot …
J. Mini
  • 1,868
  • 1
  • 9
  • 38
1
vote
1 answer

Achieving Tabular Pivot Type report in SQL

I have the below code which I am trying to get the info of Population of City, County Total for those cities and State Total for all counties in the state. I was able to crack most of it except the State sorting or in genaral State sorting. ORDER…
Ameer Memon
  • 25
  • 4
1
vote
0 answers

SQL UNION changes values of a table

I'm using Teradata. Trying to understand a very strange thing. SELECT ID, TYPE, '' AS SUB_TYPE FROM Table_A UNION ALL SELECT ID, TYPE, SUB_TYPE FROM TABLE_B Somehow, valid values of Table B become blank in the…
Billie_H
  • 69
  • 7
1
vote
1 answer

SQL Server, T-SQL: weird behavior on "multiple value INSERT INTO statement" + sql_variant type column

Writing a script to experiment with sql_variant data type and SQL_VARIANT_PROPERTY function (to recover data information from a sql_variant column) on SQL Server I discover, accidentally, what I consider an unexpected behavior on a multiple value…
Mauricio Ortega
  • 301
  • 2
  • 12
1
vote
1 answer

How to get a optimized paginated list from a query that has a UNION ALL?

I have a query formed by an UNION ALL from two tables. The results have to be ordered and paginated (like the typical list of a web application). The original query (simplified) is: SELECT name, id FROM _test1 -- conditions WHERE UNION ALL SELECT…
Dr. No
  • 1,306
  • 5
  • 28
  • 57
1
vote
1 answer

SQL sort by, order by case when, item to specific position

How to put item in a specific position? I'd like to put item on a 5th position: `id`='randomId243' So far my sorting looks like that: ORDER BY CASE WHEN `id` = 'randomId123' THEN 0 WHEN `id` = 'randomId098' THEN 1 ELSE…
kkkkk
  • 572
  • 1
  • 10
  • 21
1
vote
3 answers

How to use WITH clause with UNION ALL in SQL Server

I'm trying but I don't know how to combine two SQL statements including the WITH clause with the UNION ALL. In each of the WITH SQL statements the difference is the WHERE clause. WITH cte AS ( SELECT CMCONTRACTS.CMSERIALNUMBER,…
Sternahirundo
  • 25
  • 4
1 2 3
…
51 52