is union all bad or good for performance. Is there any other way to write the query

Question

0.00/5 (No votes)

See more:

--I have the below scenario in my project...But i am giving a simple example. Kindly help.

table1

fields :
1) id
2) distance
3) address

table2

fields:
1) id
2) colony
3) subcolony

--question..
now a query has to be written in such a way that :
-----> it should fetch all the id from table 2 that is not in table1
-----> it should fetch all the id from table 2 that is in table1 but the distance is zero

--The idea which came to my mind at first glance is :

SQL

select id from table2 
where id not in(select id from table1)
union all 
select id from table2
where id in(select id from table1 where distance=0)

--So is there any other way to tackle. Is there any other way to write this query ?

Kindly need your help.

Posted 13-Sep-13 4:24am

anurag19289

Updated 13-Sep-13 7:04am

Maciej Los

v5

Add a Solution

Comments

phil.o 13-Sep-13 11:20am

Your query answers exactly to the requirements you gave. So what's wrong with it ? You do not even need a 'UNION ALL' statement, as both queries definitely cannot return any common element. A simple 'UNION' does the trick.

2 solutions

Solution 1

This can do what you need, except you made as small mistake in the end of the query:

SQL

SELECT id FROM table2 
WHERE id NOT IN(SELECT id FROM table1)
UNION 
SELECT id FROM table2
WHERE id IN(SELECT id FROM table1 WHERE distance=0)

I replaced 'and' with 'where'.

Posted 13-Sep-13 4:45am

phil.o

Comments

anurag19289 13-Sep-13 11:18am

ok but union is going to give me duplicate values i guess..

and i need a query other than union or union all...

phil.o 13-Sep-13 11:21am

No, both queries cannot return common elements since one is 'NOT IN' and other is 'IN' -> they are mutually exclusive.

anurag19289 13-Sep-13 11:30am

Ok Ok...Is there any other way to write this query ?

phil.o 13-Sep-13 15:12pm

None that would be shorter, simpler or more efficient.
A CTE would do it, but it would be more complicated. I'll post it as a second, alternative solution.

anurag19289 13-Sep-13 15:13pm

ok bro..

anurag19289 13-Sep-13 15:13pm

kindly post that too

phil.o 13-Sep-13 15:26pm

Ok. And remember, if that helped you, and you had your answer, thanks to:
- post a vote
- mark your question as answered

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2013-09-13T09:21:00

Solution 2

Second solution ~~with CTE~~:

SQL

SELECT id FROM table2
WHERE (id NOT IN (SELECT id FROM table1))
OR (id IN (SELECT id FROM table1 WHERE distance=0))

I thought about a CTE at first glance, but it would not have removed the union.
This solution should work without UNION.

Posted 13-Sep-13 9:21am

phil.o

Comments

anurag19289 13-Sep-13 17:17pm

cool its working... :)