Prime Numbers as a SQL Query

This isn't a Sieve of Eratosthenes algorithm, but it's too good not to share it.

select r 
from (
  select rownum+1 r from dual connect by rownum < 10000
)
where
  r=2
  or 0 not in(
    select mod(r,rownum+1) from dual
    connect by rownum<sqrt(r)
  )

This is an Oracle query that will generate all of the primes up to 10,000. The query is not even close to fast, but I think it's kind of funny. It's just your typical everyday brute force method of calculating prime numbers.

I’m Done With Inline SQL


Photo courtesy of ViaMoi

When I began working "in the real world" almost 4 years ago, I came into an environment with SQL embedded directly into the code as strings.  At the time, it seemed simple. We only had 4 apps: data importer, data exporter, ui for employees to search/view/edit/create, and a web site.  There wasn't much that could go wrong.  We basically had one table of maybe 200,000 rows that our entire company was built off of.  The only thing that really could go wrong was forgetting to truncate a long string or escaping a tick mark.  Even if this did come up, the impact was small and not a big deal.

I managed to move away from the concatenated SQL statements to parameterized SQL relatively quickly if for nothing else just readability.  That's basically where I stopped.  I kept hearing and reading the debates for and against stored procs, and was never swayed from the ways I had learned already.  I started hearing about ORM software and gave a few a try.  Nothing really swayed me while we had our single table.

A few years back we did a massive database conversion to normalize our defunct single table.  The company was growing and our data needs were expanding.  All software was retooled using existing methods and everything painfully got switched over.  Now, here I am 4-years hardened into the real world, and I'm tired of buggy SQL strings.  Our business has expanded so much, and our simple single table SQL statements have turned into crazy multi-table joined monsters.  It just didn't scale and now the code is uglier for it.  Now we have maintenance and testing issues that we're working through.

I've just recently had a few experiences with ORM software and it's so much easier to write and debug code.  I want all of the SQL interactions to be isolated so that they can be tested by someone else (preferably, the ORM vendor).  I just want a platform to use and not worry about it.  If I need to do something crazy, I can create a view or stored proc and have the db check my syntax for me.  I'm sure there will be cases where it makes sense to write some inline SQL , and that's okay.  I'll isolate it and test the snot out of it.  I'm just done having code littered with SQL strings.

This is all just a part of learning and growing.  I'm sure that if I had come into an environment with an existing ORM in place, then I wouldn't have learned half the SQL I know now.  My SQL-Fu seems to have grown by leaps and bounds in the last year or two.  Knowing that, I don't regret the path I took to get to this point.  I do know that I'm sure as hell not looking forward to re-writing those legacy apps as they start giving me issues.

Oracle 9i NULL Behavior

This trips me up from time to time, so I wanted to document this for my own benefit and share my findings. Oracle treats NULL different from any other value. Any time you make a direct comparison to a NULL value, the end result is NULL.

This topic is best shown by example.

SELECT * 
FROM records 
WHERE NULL=NULL;

Returns nothing.

SELECT * 
FROM records 
WHERE ''='';

Returns nothing. Oracle treats an empty string (‘’) as NULL.

SELECT * 
FROM records 
WHERE NULL IS NULL;

Returns everything. In order to do a comparison with NULL, you must use “is”.

SELECT * 
FROM records 
WHERE '' IS NULL;

Also returns everything. This demonstrates Oracle’s behavior of the empty string (‘’) being treated as NULL.

Now it's time for the odd stuff. Whenever you are making list comparisons, you should also be careful of NULL. List comparisons are IN/NOT IN and EXISTS/NOT EXISTS.

SELECT * 
FROM records 
WHERE unique_id NOT IN (
   SELECT unique_id 
   FROM some_other_table 
   WHERE 1=0
);

Returns nothing even though you would expect it to. I’m using 1=0 because it is never true. The set will be empty(NULL). The inner query must return something in order for the query to proceed.

SELECT * 
FROM records r 
WHERE NOT EXISTS (
   SELECT 1 
   FROM some_other_table 
   WHERE unique_id=r.unique_id 
   AND 1=0
);

Returns everything. Exists will evaluate to true if something is returned and will evaluate false if nothing(NULL) is returned. Also, please note that in the inner query I’m referencing a value from the outer query(r.unique_id).

SELECT * 
FROM records 
WHERE unique_id NOT IN (
   SELECT NULL AS c 
   FROM dual
);

Returns nothing. The inner query doesn’t return an empty set, instead it returns a single value which is null. This query will take a while to run. It will scan every record and try to perform the following comparison “unique_id <> NULL”, which will fail for all records for reasons stated in the top set of examples.

SELECT * 
FROM records 
WHERE unique_id IN (
   SELECT NULL FROM dual 
   UNION ALL 
   SELECT 11697981 FROM dual --Let us assume this value actually exists in the records table.
);

Returns 1 record. The inner query returns 2 values (NULL, 11697981). Just because the list has a value in it that is null doesn’t mean the entire comparison will fail for that record.

That's all I've got on the NULL situation. Generally I get tripped up on a NOT IN clause or a <> comparison. Hopefully not anymore.

SQL Formatting

I'm tired of seeing poorly formatted SQL statements. I used to be a poor offender of this, but I have seen the light. I'm over the whole "stuff it all into one line" mentality. Less lines is not always better. I've recently turned my attention to my SQL formatting.

Have you ever seen some SQL that looked like this?

select * from my_table where my_primary_key=72;

Of course you have, now how about something like this?

select column_1,column_2,column_3 from my_table, my_other_table where my_primary_key=72 
AND my_table.my_primary_key=my_other_table.my_primary_key;

It's pretty damn unreadable right? Does this look better to you?

SELECT
	column_1,
	column_2,
	column_3 
FROM
	my_table,
	my_other_table 
WHERE
	my_primary_key=72 
	AND
	my_table.my_primary_key=my_other_table.my_primary_key;

SQL is just like any programming language. Readability helps maintainability. From this day forward I've decided to make sure that I do a good job of formatting and *gasp* commenting my SQL.