CODEWITHSUNDEEP
mysql
Ali Imtiaz
Ali Imtiaz

Reputation: 15

How to use exists with a subquery?

Hi i'm trying to Display all drinks each manufacturer is producing except the carbonated one's using exists and a subquery but its giving me a empty set please help me out

mysql> 'select * from drink';
+----------------+------------+----------------+
| name           | type       | manf           |
+----------------+------------+----------------+
| Coca-cola zero | carbonated | Coca-Cola      |
| Mirinda        | carbonated | PepsiCo        |
| Big Apple      | carbonated | Murree Brewery |
| Big Lemon      | carbonated | Murree Brewery |
| Big Orange     | carbonated | Murree Brewery |
| Peach Malt     | malt       | Murree Brewery |
| Lemon Malt     | malt       | Murree Brewery |
| Apple Malt     | malt       | Murree Brewery |
| New Coke       | carbonated | Coca-Cola      |
| Diet Coke      | carbonated | Coca-Cola      |
| Pepsi          | carbonated | PepsiCo        |
| Fruita vitals  | Juice      | Nestle         |
| Twister        | Juice      | Shezan         |
| Nescafe        | cofee      | Nestle         |
| Cappuccino     | Cofee      | Gloria Jeans   |
| Caffe Latte    | Cofee      | Gloria Jeans   |
| Flat White     | Cofee      | Gloria Jeans   |
+----------------+------------+----------------+
17 rows in set (0.00 sec)

Query i tried but its not giving me a result

SELECT * FROM drink WHERE NOT EXISTS (SELECT * FROM drink WHERE drink.type = 'carbonated');

Upvotes: 0

Views: 70

Answers (4)

user2727841
user2727841

Reputation: 725

do not use subquery if the simple query is doing the same thing

SELECT * FROM drink WHERE `type` <> 'carbonated'

subquery will slow your system and also there is no need of subquery for doing this. These techniques enhance your application speed. Think about future if you have fifteen thousand records subquery will slow down your application.

Upvotes: 0

asantaballa
asantaballa

Reputation: 4048

Not sure of your goal.

If it's to select all the drinks form all manufacturers except manufacturers which have carbonated drinks do this.

Select
   * 
From Drink d1
Where Not Exists
    (   Select * 
        From Drink d2 
        Where d2.Manf = d1.Manf
          And d2.Type = 'carbonated');
    )

If it's just selecting all the drinks which are not carbonated it's simpler.

Select
   * 
From Drink 
Where Type <>'carbonated'

Upvotes: 0

gbtimmon
gbtimmon

Reputation: 4332

What Eugen said is correct, using a EXISTS for this is insanely ineffiecent, and would generaly be just wrong. But if you really want to make it work it might look like this :

SELECT * FROM drink A WHERE NOT EXISTS (
    SELECT * FROM drink B WHERE A.name = B.name AND A.manf = B.manf AND B.type = 'carbonated'
);

(I only give this answer because I remeber being asked for similar stupid queries in my graduate databases course when going over relational algebra, so I think I have idea why you're asking this. )

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65332

This is not a case for EXISTS - a simple 

SELECT * FROM drink WHERE `type`<>'carbonated'

will do.

If this is not what you are after, you need to update your question to be more specific.

Upvotes: 1

Related Questions