SQL features

This entry is part 1 of 2 in the series SQL tips and tricks

I’m currently going through ‘SQL Cookbook’ by Anthony Molinaro to fresh up my SQL knowledge. I’ve read about 1/6th of the book so far and love it. It’s not meant for newbies, but I’d say for everybody else. It contains tonnes of examples and in detail explanations. I really like that it explains solutions for the most common databases (SQL Server, MySQL, Oracle, PostgreSQL, DB2).

Before I start going into details of what all I’ve learned so far, I quickly explain how I even got to buy the book. I’m using MySQL and MSSQL databases for about 2 years now and all the databases I’ve worked so far were fairly straight forward, small, and the queries simple. I’ve had database courses at university and thought I remembered still most of what I’ve learned (about 10 years ago – what was I thinking?).

One of my current projects is atypical with respect to my previous databases: It still doesn’t consist of too many tables, but the required SQL queries are a whole lot more complicated. I hit a road block. I tried to solve way too many issues on the application side (which resulted in performance costs) and finally decided to step back and do it right in the first place (hmm, that line sounds familiar. Guess I’ve watched too many Mike Holmes episodes years ago).

Pattern matching in search conditions

Let’s assume you want to show all rows that start with the characters ‘Star’ in a certain column.
Furthermore, we have the following table named movie:

title
---------
Superstar
Star Wars
Star Trek
Stargate
Stardust
Despicable Me

The sql statement would be:

SELECT title FROM movie WHERE title LIKE 'Star%';

Real easy, isn’t it? Yeah, as long as you remember what wildcard stands for ‘any sequence of characters’.

List of wildcards that can be used:

%      any sequence of characters
_      any single character
[]     any single character within range/set (e.g. [s-z]/[stuvwxyz]
[^]    any single character not within range/set (e.g. [^s-z]/[^stuvwxyz]

UNION ALL versus UNION

To combine the result of several SELECT queries you use UNION. Nothing knew here, but there is a slight difference between UNION and UNION ALL. UNION ALL will result in duplicates if they exist. UNION, on the other hand side, will filter duplicates.

NOT IN statement in the WHERE clause

Ok, that one was completely new to me, but important to know. Let’s look at the following query:

SELECT title FROM move WHERE title NOT IN ('Despicable Me','Startdust');

As you guessed, the result will contain all columns but ‘Despicable Me’ and ‘Startdust’. Let’s quickly create a second table named ‘wishlist’ with following entries:

id    | title
--------------
1     | Despicable Me
2     | Startdust

Now change the SELECT query a bit and you get the same result:

SELECT title FROM movie WHERE title NOT IN (SELECT title FROM wishlist);

But what if the wishlist contained one entry with id 3 and title NULL? Ok, in this example, a title with NULL doesn’t quite make sense, but let’s just go with it. You still expect the same 4 movie titles as result with the above statement or? If you do, you’re mistaken. The result will be empty. What I didn’t know is that IN and NOT IN are essentially OR operations. So above select statement would be equivalent to following:

SELECT title FROM movie WHERE title NOT IN ('Despicable Me','Startdust',NULL);

So let’s look at the comparison for ‘Star Wars’:

'Star Wars'='Despicable Me' || 'Star Wars'='Startdust' || 'Star Wars'=NULL
==> (FALSE || FALSE || NULL)
==> NULL

The part to remember is that (FALSE or NULL) results in NULL and (TRUE or NULL) results in TRUE. That means if there is a chance that the column will contain a NULL value, use the following statement instead:

SELECT title FROM movie m WHERE NOT EXISTS (SELECT 1 FROM wishlist w WHERE m.title=w.title);

My first blog post (kinda)

Two years ago, I planned to start a blog, but since I was aware of my everything but perfect English I test wrote a bunch of blogs offline just for my hubby to read.

Yeah, that didn’t go too well. He criticized about 95% of my writing which (ah duh) demotivated me from actually publishing any of them online. Especially since he didn’t even come up with improvements for half of what he criticised (not that I blame him, he is no editor).

So this time, I don’t worry about my mistakes and just publish the posts. After all, I can only improve by practicing or? Of course, I could hire an editor, but I don’t really feel like that makes sense here. First of all, how many people are actually ending up reading my first posts and second (and more important), I have no intention on ever making income with this blog (even if I had enough subscribers one day), so why should I spent income for it?

I already have a small list of what I’m going to write about. For example, I want to write about my experiences with Codejock’s Toolkit Pro, Trello, and TeamViewer. I love emacs and use it daily, so I think it makes sense to write about it and why I use it in the first place.

Furthermore, I currently feel like I’m stuck with my SQL knowledge. I’m working with MySQL and MSSQL for quite a while now and I always found solutions to my problems (mostly via StackExchange – don’t we developers love it?), but my latest project is quite complex (and also the database) and my program seems fairly slow due to the amount of database requests. I feel like I should be able to put more logic into stored procedures and less in my application code. Therefore, I have started reading ‘SQL Cookbook’ and I’m planning to post what I’m learning.

Last but not least, I’m going to post about important experiences/events in my life.