How to use hierarchical query in Infinispan?

Why do I need to use a subquery for this SQL query?

• Here are some assumptions: 2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below, SELECT id, first_name FROM student_details WHERE first_name IN ('Rahul', 'Stephen'); but, if you do not know their names, then to get their id's you need to write the query in this manner, SELECT id, first_name FROM student_details WHERE first_name IN (SELECT first_name FROM student_details WHERE subject= 'Science'); If I don't know there names, can I just use this query: SELECT id, first_name FROM student_details WHERE subject = 'Science';

• Answer:

  Just to be clear, the second query and the third query are NOT the same. Consider this data set (ID, FirstName, Last Name, Subject) (2, Rahul, Gandhi, Physics) (3, Stephen, Colbert, Comedy) (4, Stephen, Hawking, Science) The query: SELECT id, first_name FROM student_details WHERE first_name IN (SELECT first_name FROM student_details WHERE subject= 'Science'); will return 3, Stephen 4, Stephen While the fourth will return only 4, Stephen In cases where there are multiple records with the same first name, the second query and the third query will return different results.