> I am not clear how joined rows produce cartesian product. > I guess you have to learn SQL then:
https://en.wikipedia.org/wiki/Join_(SQL) https://en.wikipedia.org/wiki/Cartesian_product > > Can you please correct the query ? thanks. > > Just use NOT IN, <> will not use index anyways. Sergi > On 4 January 2017 at 17:06, Sergi Vladykin <[email protected]> > wrote: > >> Your query is wrong: >> >> Joined rows will produce a cartesian product of all the row pairs, thus >> you will have a row Person[100, 'Name100'] that correctly passed the first >> join and paired with n['Name200'] in the second join. Since 'Name100' <> >> 'Name200', it will be correctly returned in the result set. >> >> Sergi >> >> 2017-01-04 14:06 GMT+03:00 Anil <[email protected]>: >> >>> HI, >>> >>> As per ignite documentation, IN operator does not use index and has to >>> be use as *join table.* >>> >>> Hoping this holds good for* NOT IN *operator as well. >>> >>> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId >>> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) = >>> ?) n on n.joinId <> p.name"; >>> PreparedStatement statement = conn.prepareStatement(sql); >>> statement.setFetchSize(100); >>> statement.setObject(1, new String[]{"100", "200", "300"}); >>> statement.setObject(2, new String[]{"Name100", "Name200"}); >>> >>> >>> Expected results are Name300 >>> Actual output : >>> Name100 >>> Name200 >>> Name300 >>> Name300 >>> >>> Did you see any issue with the query ? please help. >>> >>> thanks. >>> >> >> >
