CODEWITHSUNDEEP
sql-server
Nishant Khanna
Nishant Khanna

Reputation: 59

Alternative of UNION in sql server

I have 2 tables which contains 5 unique cities each. I want all 10 cities but i don't want to use UNION. Is there any alternative for UNION.

SELECT DISTINCT CITY FROM TABLE1
UNION
SELECT DISTINCT CITY FROM TABLE2

Upvotes: 3

Views: 14226

Answers (4)

Pierre C
Pierre C

Reputation: 3468

If the first table is sure to contains all the records of the second table, then one can check if the id could be found inside a subquery with an OR clause.

I'm using an ORM framework which doesn't support the UNION operator (Apache OJB) and, with the above assumption, this strategy has proven to be faster than with the use of FULL OUTER JOIN.

For instance if the table STUDENT contains all the students of a province/state with a field for their current main school and another table, STUDENT_SECONDARY_SCHOOL, contains information for those students attending a second school part time, I can get the union of all students attending a particular school either full time or part time this way :

SELECT STD_ID FROM STUDENT 
WHERE 
    STD_SCHOOL='the_school' 
    OR 
    STD_ID IN (SELECT STD_ID FROM STUDENT_SECONDARY_SCHOOL WHERE STD_SCHOOL='the_school')

Again, I want to emphasize that this is NOT the equivalent of a UNION but can be useful in some situations.

Upvotes: 0

Cato
Cato

Reputation: 3701

Here is an alternate way

SELECT DISTINCT CASE WHEN a.city is null then b.city else a.city end 
                         FROM Table1 FULL JOIN Table2b ON 1 = 0

it offers no advantage over UNION - but you might be interested in seeing FULL JOIN, which has its similarities to UNION

Upvotes: 3

DB101
DB101

Reputation: 633

You can insert the data that you want into a temporary table and retrieve it from there. That will avoid the need for a UNION.

SELECT DISTINCT CITY 
INTO #City 
FROM TABLE1

INSERT INTO #City
SELECT DISTINCT CITY 
FROM TABLE2

SELECT DISTINCT City 
FROM #City

Upvotes: 1

Mehul Bhalala
Mehul Bhalala

Reputation: 800

You can apply Full Outer join instead of Union

SELECT DISTINCT ISNULL(t.City,t1.City)
FROM dbo.TABLE1 t
FULL OUTER JOIN dbo.TABLE2 t1 ON t.City = t.City;

This query provides you the same result as union

Upvotes: 4

Related Questions