Whats is the difference between join query within loop and join query using IN clause it.?

Question

enter image description here

This is my database tables representation.

I am doing inner join to get the data from these tables the query is as follows.

Query-1

 select * from Trips
        Inner join TripPoints
               On Trips.tripkey=Trippoints.tripkey 
        inner join Cars
               On Trips.carid=Cars.cid 
                and Cars.cid IN ('1','2','3','4','5','6')
                where (lat>='4.0' and lat<='5.0')
                  and 
                (long>='52' and long <='54')

Query-2

    for(int carId=1;carId<=6;carId++)
        {
        select * from Trips
        Inner join TripPoints
           On         
          Trips.tripkey=TripPoints.tripkey 
          inner join Cars
          on 
          Trips.carid=Cars.cid and CARS.cid = carId
        where 
         (lat>='4.0' and lat<='5.0') 
           and 
         (long>='52' and long <='54')
        }

Both queries executes successfully.

But Query-1 gives lesser records than that of Query-2 ?

What difference in the query makes result difference?

What database are you using that has for loop structures?

Oded
– Oded

2011-12-21 09:34:37 +00:00
Commented Dec 21, 2011 at 9:34 — Oded
– Oded, Commented Dec 21, 2011 at 9:34
I am using C# to build sql query and Database is mySQL 5.0.

pramod choudhari
– pramod choudhari

2011-12-21 09:37:49 +00:00
Commented Dec 21, 2011 at 9:37 — pramod choudhari
– pramod choudhari, Commented Dec 21, 2011 at 9:37

onedaywhen · Accepted Answer · 2011-12-21 11:55:51Z

1

In the second query, this:

Trips.carid=Cars.cid and CARS.cid = carId

...will be treated as this:

Trips.carid = Cars.cid AND Cars.cid = Trips.carid

...which of course is the same as this:

Trips.carid = Cars.cid

In other words, the appearance of carId in the SQL is being treated as the column carid in table Trips and not your C# variable carId as you may be expecting. Therefore, the only semantic difference between the two queries is that the first has this:

and Cars.cid IN ('1','2','3','4','5','6')

...which the second does not, hence the first query returns fewer rows because there must exist Cars.cid values other than the values '1' to '6' inclusive.

answered Dec 21, 2011 at 11:55

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aF. · Accepted Answer · 2011-12-21 09:35:27Z

0

The first query returns only one result with the info that you need (6 rows).

The second query returns 6 results because you make 6 different selects (1 row per each).

Each select gives a result!

answered Dec 21, 2011 at 9:35

aF.

67k45 gold badges141 silver badges202 bronze badges

Collectives™ on Stack Overflow

Whats is the difference between join query within loop and join query using IN clause it.?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related