Fun with SQL or, I know it should do this…

Occasionally, one comes across a problem where using a relational database is the correct tool. For me that is very occasionally. Some things I recently (re)learned that I thought I would share are:

• If you are trying to create a query that lets you know if a certain value is in another table, use IN to check for membership.  Doing an INNER JOIN only finds you the ones in common.  If you want a new column for multiple tables you are checking it gets ugly.  Do a membership check like X IN (SELECT y.X IN yyy AS y) and then you can cut-n-paste that section, changing the table you are drawing from.  The full query would look like SELECT X, X IN (SELECT y.X IN yyy AS y) AS Y, X IN (SELECT y.X IN zzz AS y) AS Z FROM XXX.
• If you are checking for membership in a group, i.e. X IN Y, make sure you have Y indexed.  That sped up a query I was doing about 100x.  That was the difference between a few seconds  and run time measured as a fraction of an hour.
• If you want to do a named or saved query (as I thought it should be called) it is actually called a database view.
• Have as much computation done on the server by using views.  When you start having multiple layers of queries and data going back and forth across the network, that can slow things down to a crawl.  Especially if it is over a slow broadband connection.
• Sometimes, you just need to RTFM.  In this case it was the O’Reilly book, Learning SQL, Second Edition.  That’s where I finally figured out what the correct name for a named or saved query was (a view).

I was using OpenOffice Base for my data front end and there’s a few gotcha’s there that need to be pointed out:

• It won’t let you do a lot of SQL that is valid.  To get around this sometimes you have to tell it to leave the SQL alone and execute it.  There’s a button to do that in the SQL view.
• If you need to use subqueries, you have to do it in SQL view.  There is no way to do it in the design view.
• If importing data into a table, remember to double check to make sure the number of records you got matches what you expected it to be.  You may have had bad data that threw out that record, or it stopped putting records in at the bad data.  The JDBC connector could have also crashed on you.
• If you start getting errors about your queries and having problems viewing tables, close out OO Base and reopen it.
• If you create a key as the first column in your table and try to set it to autogenerate, it will not save it that way.  This is a known bug.