Paul White (11/3/2013)

---

The above query does not produce a join. It produces a distinct sort then concatenation of values from one table and the other.

Well it might do, or it might do something else. Without table definitions and data, how could we know?

CREATE TABLE Contacts

(

City nvarchar(25) NOT NULL

)

CREATE TABLE Contacts2

(

City nvarchar(25) NOT NULL

)

CREATE NONCLUSTERED INDEX i ON dbo.Contacts (City);

CREATE NONCLUSTERED INDEX i2 ON dbo.Contacts (City);

UPDATE STATISTICS dbo.Contacts WITH ROWCOUNT = 10000, PAGECOUNT = 1000;

UPDATE STATISTICS dbo.Contacts2 WITH ROWCOUNT = 10000, PAGECOUNT = 1000;

SELECT city

FROM contacts

UNION

SELECT city

FROM contacts2

GROUP BY city

ORDER BY 1;

DROP TABLE dbo.Contacts, dbo.Contacts2;

That produces the following plan featuring a Merge Join (my answer!) running in Union mode: