Distinct

The DISTINCT keyword is used to remove duplicate values from a result of a SQL or SPARQL query.

Union

Use this tag only for questions about UNION, a keyword of the SQL language for combining the results of multiple SQL queries



Better performance performance-wise

Example

"Union is better than union since we are sure that duplicates are removed"

from question  

How to write a MySQL select query for obtaining duplicates in select columns but unique in another

"So union is much better than union all with distinct in performance-wise"

from question  

UNION versus SELECT DISTINCT and UNION ALL Performance

"The union all with a distinct gives better performance though i believe.."

from question  

Using SQL JOIN and UNION together

Slower faster distinct

Example

"Union all is generally faster than using distinct or grouping"

from question  

SQL Server : shortest syntax using distinct?

"Union is a shorter way to write union distinct"

from question  

How to select one attribute from 2 tables?

"This is why execution of a union all performs faster as it doens t have to do the duplicate removal contained within the execution of a union"

from question  

Union Causing The ntext data type cannot be selected as DISTINCT because it is not comparable

"However the tables are huge and the union all i read its faster than distinct takes forever to execute even with just two tables let alone 6"

from question  

MySQL: Optimizing SELECT from 6 huge identical tables with different data split up by timestamp

"A side note since you want all rows using union all is faster since it does not need to perform a distinct to eliminate duplicates"

from question  

SQL select from table and get table name

"Union distinct is slower than union all but you may need it for de-dupping."

from question  

How to optimize select sum() on mysql?

"Keep in mind that union will return a distinct list - duplicates will be removed but it will perform slower than using union all which will not remove duplicates"

from question  

Checking if a string is found in one of multiple columns in mySQL

"I changed union to union all because union implies a distinct;i don t think that s what you want or even need union all is also faster because it doesn t have to check if there are duplicate rows"

from question  

GROUP BY this OR that

"If you used a union then that may well be slower as a union has to apply a distinct but union all doesn t have to do that so it should be no different"

from question  

UNION ALL Performance IN SQL Server 2005

"In most cases union all is slightly faster than union distinct"

from question  

How to know what table a result came from when using UNION in MySQL

In columns cell.i.e

Example

"Union all is faster than union;in case of union if you have 10 columns and 100 rows it will compare each cell.i.e 10 100 to get distinct values while in union all this is not the case"

from question  

SQL Server: multiple queries or UNION

"I am currently using union all and a distinct in the outer query as this proved much faster than using union s for the uniqueness of data"

from question  

Should I use a T-SQL function, view, or stored proc?

Efficient id all

Example

"Union all is more efficient as union will take unnecessary distinct"

from question  

SQL VIEW to the multiple same tables

"Ecause you are selecting distinct id s you do not need union all;union all is more efficient because it does not add the step of removing duplicates"

from question  

Oracle SQL UNION ALL where T1.ID <> T2.ID

"Seems that union is slightly less efficient than distinct . an array constructor is faster than array_agg"

from question  

Ensuring unique elements in array created from merging 2 bigint arrays

Others

Example

--a union approach seems more straight forward if you can guarantee that only status 2 records get mapped the union could be a union all to gain a bit of performance by avoiding the distinct

from question  

Mysql fetch from multiple tables

Depending on the query ef will generate either union all with distinct or just union so your explicit distinct is not necessary;the linq equivalent of union all is concat

from question  

EF: Is there way to force Linq using UNION instead of UNION ALL

I am guessing that count distinct is a better approach than union in the subquery but you could test that

from question  

Count distinct on TWO columns on SQL

As you didn t wanted duplicates theres no point using union all and use of distinct is simply unnecessary as union gives distinct data can create a view would be best choice as view is a virtual representation of the table

from question  

Select distinct values from multiple columns in same table

Just use lead with union select t.id t.dte as startdate lead t.dte over partition by t.id order by t.dte as enddate from select distinct t.id v.dte from t cross apply values startdate enddate v dte t

from question  

How to find the continous range of dates in Sql Server?

It sounds to me like you are asking for the union of the filtering session values from the two tables and as you want the distinct set you do want union rather than union all union all is actually a much more common requirement than union

from question  

How to select rows from two tables using sql

I would go for these two changes to optimize this query first i would split the query to two parts combined with a union preferably a union all if you don t care about removing duplicates but also possible with union distinct

from question  

SQL Server : is there a better way to write this logic using a where clause to improve performance

If you want to ensure two collections have the same distinct set of members where duplicates in either are ignored you can use;using the set operations intersect union except is more efficient than using methods like contains

from question  

Compare two List<T> objects for equality, ignoring order

Union all is better than union because it avoids a sort you know you ll get a distinct set because you re joining on two different tables

from question  

How to remove duplicate columns from join in SQL

Yes the union operator is really more like a union all statement in sql;excel s union operator does not return the distinct set of cells

from question  

Excel vba Union method duplicates cells that overlap

You do want a union all but i think this is the version of the query you want in other words sum distinct is no longer appropriate when you use union all

from question  

Sql sum distinct with same values on cells

Try something like this this statement will first create a long list of union all select with group by better than distinct as dynamically created sql and executes this with exec

from question  

Select all unique values from all columns in a table

Back to Home
Data comes from Stack Exchange with CC-BY-SA-4.0