Forum: Building VoltDB Applications

Post: SQL - Self Join or "WHERE IN ()" supported?

SQL - Self Join or "WHERE IN ()" supported?
lemongrass
Dec 12, 2010
Hi. Is the "where x IN ( )" SQL syntax supported?
(or are Self Joins supported?)

I need to run a query like:

select user_id from friends where user_id in (select friend_id from friends where user_id=?)

What would be the optimal approach to doing that in VoltDB?
Thanks.
re: SQL - Self Join or "WHERE IN ()" supported?
tcallaghan
Dec 13, 2010
VoltDB does not currently support "IN" syntax, sub-queries, or self-joins. Of the three, IMHO, self-joins will be the easiest to implement. We are adding SQL functionality as the customer's needs dictate.

That said, you can implement this functionality either within your client application or within a stored procedure. Where you implement it will depend largely on how your data is partitioned. Here is some pseudo-code (written synchronously):

in the client application

VoltTable[] vtFriends = client.callProcedure("GetFriendsList",userId);
foreach (friendUserId in vtFriends[0]) {
  VoltTable[] vtSingleFriend = client.callProcedure("GetUserInfo",friendUserId);
}

(or)
in the stored procedure (note, you can only queue 1000 SQL statements in a stored procedure)
  SQLStatement sqlGetFriends = "select f.friend_id from friends where user_id = ?";
  SQLStatement sqlGetFriendInfo = "select f.friend_name, f.friend_state from friends where user_id = ?";
  ...
  VoltQueueSQL(sqlGetFriends,userId);
  VoltTable[] vtFriends = VoltExecuteSQL();
  foreach (friendUserId in vtFriends[0]) {
    VoltQueueSQL(sqlGetFriendInfo,friendUserId);
    VoltTable[] vtAllFriends = VoltExecuteSQL(true);
  }

Does that help?

-Tim