Pull rows out, then filter them

Close an app and what you wrote is still there. It feels ordinary, but it means somebody is keeping your data somewhere. That somewhere is a database, and the language for talking to it is SQL.

There is one big way SQL differs from the programming languages you have met so far. You write what you want, not how to get it. In JavaScript you wrote the steps: walk the array from the front, and when something matches the condition, push it into another array. In SQL you only write "I want the rows that match this". Working out how to fetch them is the database's problem.

Data sits in a table. It is shaped like squared paper: the columns running down are columns, the rows running across are records. For book data the columns would be title, author and page count, and a record would be one book. The strength of a table is that every record carries the same columns.

There are three moves to learn in this chapter, roughly speaking.

• choose — which columns of the table do you want to see
• filter — which records match what you are after
• order — what order should they come in

Combine those three and you can already answer questions like "show me the titles of the books by so-and-so, longest first". The writing sits close to an English sentence, and once you are used to it you will read it straight off.

Beyond equals, greater and smaller, a filter can also do a vague "contains this", or combine conditions with "A and B" and "A or B". This works the way branching did in JavaScript, so it should go down easily.

One thing to watch is how a value that is not there behaves. SQL marks "nothing in here yet" with a special value, and it is neither zero nor an empty string. An ordinary comparison will never catch it, so there is a form of words made just for it. This is the one everybody trips over at least once.

This chapter is the foundation for all of SQL. The aggregating and the joining that come later are just sitting on top of choose, filter and order. Get it solid here.

Pick up the habits of writing while you are at it. SQL does not care about upper and lower case, but the convention is to write the command words in capitals. That way your eye can tell the commands from the table and column names. The longer the query, the more it helps, so get into the habit from the start.

Do not forget the ; at the end of the line either. It is the signal for "that is the end of one instruction". Leave it off and the database sits waiting, wondering whether more is coming.

There is one more thing about SQL: the order of the result is not promised. If you do not ask for a sort, nothing says which order the rows come back in. It may happen to look right, but that is all it is — happening to. If the order matters, always write the sort.

If you came from JavaScript, it is worth holding on to the idea that what comes back is also a table. Take out a single record and what you get is a one-row table. With that feeling in place, the later chapters about filtering and joining results will slot in easily.

By the way, the SQL in this course runs inside your own phone. Nothing you write gets sent to any server, so try anything you like. Make plenty of errors, too. When you write a query wrong, the database tells you where it went wrong — and getting used to reading that is another thing worth doing here.