CODEWITHSUNDEEP
mysqlsqloracle-databasesybase
RV.
RV.

Reputation: 2998

How to replace UNION in sql query

I have a table: attb

------------------------------------------------
Id | Name | Code | Attribute | Amount 
------------------------------------------------
1  | AV   | 123  | Alpha     | 233
------------------------------------------------
2  | TV   | 122  | Beta      | 235
------------------------------------------------
3  | TV   | 123  | Gama      | 238
------------------------------------------------
4  | CD   | 122  | Beta      | 239
------------------------------------------------
5  | TP   | 122  | Beta      | 240
------------------------------------------------
6  | RX   | 123  | Alpha     | 241
------------------------------------------------

I'm querying it as:

select id,name, code, attribute, amount
from attb
where code = 123 and attribute='Alpha'
UNION
select id,name, code, attribute, amount
from attb
where code = 122;

It return the following

Id | Name | Code | Attribute | Amount 
------------------------------------------------
1  | AV   | 123  | Alpha     | 233
------------------------------------------------
2  | TV   | 122  | Beta      | 235
------------------------------------------------
4  | CD   | 122  | Beta      | 239
------------------------------------------------
5  | TP   | 122  | Beta      | 240
------------------------------------------------
6  | RX   | 123  | Alpha     | 241
------------------------------------------------

Is there a way I can combine two queries instead of using UNION operator? or Any better implementation?

Upvotes: 1

Views: 4162

Answers (4)

Ramya Rajan
Ramya Rajan

Reputation: 9

select id,name, code, attribute, amount
from attb
where (code IN(122, 123) and attribute='Alpha') ;

Upvotes: -1

Ashif Nataliya
Ashif Nataliya

Reputation: 922

Try this.

select id,name, code, attribute, amount
    from attb
    where ((code = 123 and attribute='Alpha') or (code = 122))

Upvotes: 1

Jordan Soltman
Jordan Soltman

Reputation: 3883

Pretty easily. Just use or.

select id,name, code, attribute, amount
from attb
where (code = 123 and attribute='Alpha') OR code = 122

Upvotes: 1

Nick
Nick

Reputation: 147156

Just put both where clauses into one query:

select id,name, code, attribute, amount
from attb
where (code = 123 and attribute='Alpha') 
   or code = 122;

Output:

id  name  code  attribute  amount
1   AV    123   Alpha      233 
2   TV    122   Beta       235 
4   CD    122   Beta       239 
5   TP    122   Beta       240 
6   RX    123   Alpha      241

SQLFiddle demo

Upvotes: 2

Related Questions