I notice this question gets quite a lot of views so I’ll first address a question you didn’t ask! Regarding the title. To achieve a “Sql Union All with “distinct”” then simply replace UNION ALL with UNION. This has the effect of removing duplicates. For your specific question, given the clarification “The first query should … Read more
SELECT COALESCE(t1.id, t2.id) as id, t1.value1, t2.value2 FROM table1 t1 FULL JOIN table2 t2 ON t1.id = t2.id;
UNION will eliminate duplicate rows, whether they come between the two result sets or, as in your case, within just one result set. Try using UNION ALL instead.
You should give alias to your table. So try this: SELECT A FROM ( SELECT A, B FROM TableA UNION SELECT A, B FROM TableB ) AS tbl WHERE B > ‘some value’
There is no default order. Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes. EDIT: Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there … Read more
When you use a UNION, the DBMS removes any duplicate rows, and in order to do so it needs to identify whether two rows are equal / identical. This in turn means looking at each column of the two rows it’s comparing, and deciding if they’re equal. The error message you’re seeing is where one … Read more
Concat is the LINQ equivalent of UNION ALL in SQL. I’ve set up a simple example in LINQPad to demonstrate how to use Union and Concat. If you don’t have LINQPad, get it. In order to be able to see different results for these set operations, the first and second sets of data must have … Read more
When you do set() you are creating an empty set. When you do set().intersection(…) you are intersecting this empty set with other stuff. The intersection of an empty set with any other collection of sets is empty. If you actually have a list of sets, you can get their intersection similar to how you did … Read more
Just checked that this will work: (SELECT * FROM catalog_productimage ORDER BY date_modified LIMIT 10) UNION SELECT * FROM catalog_productimage WHERE id=5;
select id, sum(amount) from ( select id,amount from table_1 union all select id,amount from table_2 union all select id,amount from table_3 ) x group by id