CODEWITHSUNDEEP
sqlsql-server
Luke101
Luke101

Reputation: 65308

How to efficiently query a one to many relationship for a value

Please consider this data:

+----+-----------+----------+--+
| id | name      | model    |  |
+----+-----------+----------+--+
| 1  | Toyota    | Camry    |  |
+----+-----------+----------+--+
| 2  | Chevrolet | Cavalier |  |
+----+-----------+----------+--+
| 3  | Chevrolet | Astro    |  |
+----+-----------+----------+--+
| 4  | Ford      | Pinto    |  |
+----+-----------+----------+--+
| 5  | Nissan    | XTerra   |  |
+----+-----------+----------+--+

For every row above there are many rows in the sub table below.

+-------+--------+
| carid | color  |
+-------+--------+
| 1     | Blue   |
+-------+--------+
| 1     | Black  |
+-------+--------+
| 1     | Yellow |
+-------+--------+
| 2     | Green  |
+-------+--------+
| 2     | Blue   |
+-------+--------+

I need to find all the cars that have a color blue available. My sample output will be.

+----+-----------+----------+--+
| id | name      | model    |  |
+----+-----------+----------+--+
| 1  | Toyota    | Camry    |  |
+----+-----------+----------+--+
| 2  | Chevrolet | Cavalier |  |
+----+-----------+----------+--+

As you can see both the Camry and Cavalier have blue color available. My table has thousands of rows in the sub table for each row in the main table. It takes a long time to even query 100 rows. Is there a more efficient way to do this?

Upvotes: 0

Views: 91

Answers (3)

Prob1em
Prob1em

Reputation: 94

There are many different ways you can get your output. I prefer to use IN vs EXISTS but they work similar to one another. If the query is simple, but is taking a long time to produce a dataset then you may want to consider using proper indexes.

Please check out the estimated execution plan to see how the indexes effect the query. We are looking for an index seek/scan vs table scan

Below is my query using local (#) temp tables

     create table #car
     (
      id int
     , name varchar(20)
     , model varchar(20)
     )
     insert into #car values
     ( 1 ,'Toyota', 'Camry')
    , (2  , 'Chevrolet' , 'Cavalier')
    , (3  , 'Chevrolet' , 'Astro ')
    , (4  ,'Ford'      , 'Pinto')
    , (5  , 'Nissan'    , 'XTerra')


    create table #description
    (
    carid int
    , color varchar(20)
    )
    insert into #description values 
    (1     , 'Blue')
    , (1     , 'Black')
    , (1     , 'Yellow')
    , (2     , 'Green')
    , (2     , 'Blue')


    create nonclustered index idx_tmp_a on #description (color) include (carid)
    create nonclustered index idx_tmp_a on #car (id) include (name, model)


    select * from #car  
    where id IN
    (select carid from #description where color IN ('Blue')) --added IN Clause in case you needed more of a selection of colors

Upvotes: 0

Hadi Ardebili
Hadi Ardebili

Reputation: 109

maybe this is the simplest way to query and in large tables faster than a join :

Select * 
from cars 
where id in (select carid 
             from colors  
             where color = 'blue')

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

There are so many ways present to do this, I prefer to use EXISTS operator 

SELECT *
FROM   car c
WHERE  EXISTS (SELECT 1
               FROM   colortable ct
               WHERE  c.id = ct.carid
                      AND ct.color = 'blue')

or using INNER JOIN, considering carid and color combination is unique in color table

SELECT c.*
FROM   car c
       INNER JOIN colortable ct
               ON c.id = ct.carid
WHERE  ct.color = 'blue'

Upvotes: 4

Related Questions