CODEWITHSUNDEEP
sqlsql-server
Jerry West
Jerry West

Reputation: 151

How do I make a query listing certain elements of a column that are in a subquery table, SQL. EXISTS, IN?

I'll just show you guys an example: I got these tables:

Table: Car Mark

Name | Manufacturer
------------------- 
POLO   VOLKSWAGEN  
A6     AUDI
911    PORSCHE

Then I get this sub table, from about three tables, from a subquery:

Manufacturer |  Num of rented cars, by clients age > 50
-------------------------------------------------------
VOLKSWAGEN                      2 
AUDI                            3

My question is: how do I select the manufacturer from Car Mark that exist in this subquery?

I just want an output:

Manufacturer
---------------
VOLKSWAGEN
AUDI

Upvotes: 0

Views: 45

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You can do it different way, I rather the first one because the explain plain looks simpler and code is easy to read.

SQL FIDDLE DEMO

Check the explain plan of each query with or without index.

Last two have the exact same plan. My guess optimizer translate those to the same query.

SELECT C.[Manufacturer]
FROM  cars C
LEFT JOIN rented R
       ON C.[Manufacturer] = R.[Manufacturer]
WHERE R.[Manufacturer] IS NOT NULL;

SELECT C.[Manufacturer]
FROM  cars C
WHERE C.[Manufacturer] IN ( SELECT [Manufacturer]
                            FROM rented R );

SELECT C.[Manufacturer]
FROM  cars C
WHERE EXISTS ( SELECT [Manufacturer]
               FROM rented R 
               WHERE R.[Manufacturer] = C.[Manufacturer]);

Upvotes: 1

Praveen
Praveen

Reputation: 9355

Try

select Manufacturer
from Car_Mark
where Manufacturer in (
    select Manufacturer 
    from (
        --your subquery...
    ) x
)

Upvotes: 0

Related Questions