Hot answers tagged

23

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to: SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as ...


12

It is unclear to me what is a "more elegant way". Oracle you can use the following statement to make columns to rows select all_name from foo unpivot (all_name for col_name in ( his_name, her_name, other_name)); This is the syntax diagram of the select statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [...


11

USING UNION Others have submitted answers trying aggregation to collect data without using UNION May 12, 2014 : Query improvements without UNION May 05, 2015 : Calculating values from three related tables, without using join or union Feb 20, 2012 : SQL Data aggregation In this instance, what makes UNION an absolute must is the merging of three columns ...


10

is this a bug in SQL Server? Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White here. I executed your final query in dbfiddle (SQL Server 2019) and pasted the ...


9

According to Craig Freedman the order of execution for the concatenation operator is guaranteed. From his blog post Viewing Query Plans on MSDN Blogs: Note that when an operator has more than one child, the order of the children matters. The topmost child is the first child while the bottommost child is the second. The concatenation operator ...


9

Looks like a bug to me and I can confirm this puzzling behaviour in: 10.2.14-MariaDB If possible you can cast the integer value to a double: SELECT cast(20 as double) UNION SELECT null UNION SELECT 2.2; or make sure you have the double value first: SELECT 2.2 UNION SELECT null UNION SELECT 22; Further observations after reading the comments in @Evan ...


7

Memory related settings You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file. Actually, there's a litany of good info in this DBA....


7

I cannot recall seeing a hash match (union) operator in the wild so I can't speak authoritatively as to when they are better than the alternatives. It's possible to force one using the { CONCAT | HASH | MERGE } UNION query hint but let's try to create a real example. Quote from the documentation referenced in the question: For the union operator, use the ...


7

An example query would be great. Using a numbers table (integers 1...n, where n needs to be at least 1000 for this example): SELECT N.n % 10, SPACE(100) FROM dbo.Numbers AS N WHERE N.n BETWEEN 1 AND 1000 UNION SELECT 999, SPACE(100); Results: 999 6 4 8 3 1 0 7 5 9 2 When is it used and when is it better than the alternatives? Hash union is not very ...


7

Something like this?: CREATE VIEW MyUnion AS SELECT a, b, c, d, CAST(NULL AS varchar(32)) AS e FROM table_1 UNION ALL SELECT a, b, c, CAST(NULL AS bigint) AS d, e FROM table_2 ;


6

The best solution depends on where and in what form the values come from. unnest() Use the set returning function unnest() with an array in the FROM clause. That's shortest for long lists, but it's a Postgres specific solution. SELECT avg(n) FROM unnest('{2,3,4}'::int[]) n; Which is short syntax for: SELECT avg(n) FROM unnest('{2,3,4}'::int[]) AS n(n); ...


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


6

Sum after you did the union (hope DB2 does it this way): SELECT BRAND, BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME FROM (SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME FROM "tema".MMT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL UNION ALL SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME ...


6

The primary solution is to add parentheses: ( -- parentheses required! SELECT dt1.name AS name1, dt1.time AS time1 , dt2.name AS name2, dt2.time AS time2 FROM departure_times dt1 JOIN departure_times dt2 USING (tram_id) WHERE dt1.name = '$name' AND dt2.name = 'CitySquare' -- no ORDER BY? LIMIT 5 ) UNION ALL -- see below ( SELECT dt1.name, ...


6

In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM). A full answer would inevitable involve a discussion about how the SQL language has strayed from the relational model specifically (e.g. nulls) and good language design generally (e.g. data types). In essence, before 1992 ...


5

Based on the collation names I assume that you are using Microsoft SQL Server. COLLATE can be used at the database level or the column level. Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query. Here is a sample bit of code to help you: use testdb GO CREATE TABLE dbo.Serbian (Name VARCHAR(...


5

Just a word about nested CTEs, they get evaluated each time they are referenced. To put it another way, they do not behave like temp tables, the data is not cached in some special way. So for a CTE referenced multiple times as yours are ( ie once in the UNION, once in the RunningTotal subquery ), there can be a performance penalty. I set up a simple rig ...


5

Edit regarding fields having different types, not just decimal. You can try to use sql_variant type. I never used it personally, but it may be a good solution for your case. To try it just replace all [decimal](38, 10) with sql_variant in the SQL script. The query itself remains exactly as it is, no explicit conversion is needed for performing the ...


5

Here is another approach: SELECT di.name, di.date, x.field, x.oldValue, x.newValue FROM @diffInput AS di LEFT JOIN dbo.myTable AS mt ON mt.version = @version AND mt.name = di.name AND mt.date = di.date CROSS APPLY ( SELECT 'fieldA', mt.fieldA, di.fieldA WHERE NOT EXISTS (SELECT mt.fieldA ...


5

In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (This is unlike e.g. ORDER BY, which you would be allowed to specify only once and it would apply to the combined set.) So, if you want products by the same maker ...


5

The simple answer is.... To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause. The Oracle documentation spells it out clearly: Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows ...


5

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause. For the problem described in your question, you actually do not need a UNION at all. Use only the LIKE condition to cover both full and partial matches: WHERE name LIKE '%roma%' Then use a ...


5

The UNION operator combines all the rows from one query with all the rows from another, eliminating duplicates, and forming a single list. The hash operator builds a hash table from the upper input and probes that table with the lower input. I can see how this would work to implement a UNION, given the row-at-a-time pull model the execution engine uses. I ...


5

No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans. In many cases, the difference between query plans may not matter for you. But in some (likely rare) cases it could matter significantly. For example, if SQL Server has a very poor cardinality estimate for one branch of your ...


5

Bug MDEV-15999 Bug MDEV-15999 filed by dbdemon reported this. It's since been fixed in 10.3.1. Weird MySQL/MariaDB nature From the docs, The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For ...


5

You can use the ROW_NUMBER() analytical function to generate row numbers for each partition of (points, type): SELECT *, ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum FROM questions And then you can compare rownum to a certain value that depends on points and type: WHERE rownum <= some_expression Of course, you will ...


5

This is a bug in the way SQL Server decides whether a rebind is required with certain plan shapes. It has been in the product since SQL Server 2005. Minimal repro This uses a trace flag to force a spool: CREATE TABLE #T (v integer NOT NULL); INSERT #T VALUES (1), (2), (3), (4); SELECT #T.v, A.* FROM #T CROSS APPLY ( SELECT #T.v UNION ALL ...


5

Venn diagrams are not ideal to visualize join operations like a_horse commented: Venn diagrams do not visualize joins, but set operations like union, intersect or except. And his link to illustrate: https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/ The twist, I think, is this: the Venn diagram is the right tool to visualize ...


4

There's a lot of good material in the comments, but I overlooked something obvious early on that actually makes the answer to the question fairly straightforward. What I missed was the repeated use of OR in the queries, which I originally mis-read as AND... and this makes a great deal of difference. You're asking for this: WHERE relDst='24794' OR relSrc='...


4

UNION ALL I would go with a simple UNION ALL query here: ( SELECT * FROM tbl WHERE timein >= '2013-04-26 0:0' AND timein < '2013-04-27 0:0' ORDER BY timein DESC LIMIT 1 ) UNION ALL ( SELECT * FROM tbl WHERE timein >= '2013-04-27 0:0' AND timein < '2013-04-30 0:0' ORDER BY timein ); This is a single query to Postgres. Parentheses ...


Only top voted, non community-wiki answers of a minimum length are eligible