0

I have a Oracle table with userId and adId (and some other stuff like ad Description, Date Posted etc.). I want to determine if matching userId, adId row exists or not like SELECT * from Ad WHERE userId=xxx and adId=yyy for a list of userId and adId object, e.g. listOf<UserAd>() (using kotlin)

Can I do this in one SELECT statement, instead of looping over UserAd pair in the list and collecting data for which pair the row exists and for which pair it does not? I need to find out for which pair, row exists and for which pair row does not exist.

sidgate
  • 14,650
  • 11
  • 68
  • 119
Murik
  • 3
  • 3
  • Could you create a temp table with those values and simply outer join the two or use intersect/minus operators? If both are in tables this is a simple full outer join and not a where statement at all... `SELECT * FROM A FULL OUTER JOIN B on A.userID = B.UserID and A.ADID = B.ADID` when neither side is null you have a match, where one side is null you don't... Having the data from your listof() in a table gives you a great amount of flexablity – xQbert Feb 08 '22 at 22:41
  • You could also use an "In" with multiple columns `SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));` and "Build" your in via parameters https://stackoverflow.com/questions/13027708/sql-multiple-columns-in-in-clause but I think generating a temp table then doing the work is likely the easiest/most efficient. – xQbert Feb 08 '22 at 22:48
  • @xQbert, creating temp tables, inserting data into the temp tables and doing a joint vs. looping over the list data making select call multiple time. Which is better for performance? – Murik Feb 08 '22 at 23:30
  • 1
    Every language should have some flavor of prepared statement and batch execution. Use this combination, which should (by implementation) do this without extra roundtrips compared to row-by-row execution. Looks like Kotlin uses the same as Java: [`preparedStatement.addBatch`](https://developer.android.com/reference/kotlin/java/sql/PreparedStatement#addbatch) and `preparedStatement.executeQuery`. – astentx Feb 09 '22 at 03:36

0 Answers0