Performance differences between IN and EXISTS

Very often I hear people wondering if it’s better the EXISTS or the IN operator. Let’s start by saying that the two operators behave differently when NULL values are involved (see this post). If you are sure that the field used to join the tables does not include NULL values then the IN operator “generally” performs better than EXISTS. I say “generally” because this is not always the case. For example consider the following query:

NOT IN vs. NOT EXISTS when NULL values are involved

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables: CD_FIELD DS_FIELD 1 ALPHA 2 BETA TABLE_A CD_FIELD DS_FIELD 1 GAMMA NULL DELTA TABLE_B Now try the following query: SELECT*FROMtable_aaWHEREa.cd_fieldNOTIN(SELECTb.cd_fieldFROMtable_bb); Does the previous query return any row? The answer is NO it doesn’t, even if there is a value (2) in table_a.

How to get the number of columns in a ResultSet in Java

In Java it is possible to retrieve the number of columns of a ResultSet dinamically, thanks to the ResultSetMetaData class. Here’s an example: // Here you get the conn object. E.g.: // Connection conn = DriverManager.getConnection(...); Statementst=conn.createStatement();ResultSetrs=st.executeQuery("SELECT * FROM your_table");ResultSetMetaDatarsmd=rs.getMetaData();intnumCols=rsmd.getColumnCount();System.out.println("Number of columns in your_table: "+numCols); The previous code retrieves and displays the number of columns of your_table.