A Guide to SQL Queries
SQL wasn’t always a part of my life. There were days when I never even thought about accessing or manipulating data from a database. I like to call those times, that Dark Ages.
Now that I’ve spent some time working in Rails, SQL has become a dear friend of mine. Even though it often hides behind the abstraction of ActiveRecord, I know it’s there, I know SQL is the one putting in the real work. Here is a brief guide to some common SQL keywords and what they mean.
Select form the backbone to pretty much any SQL query. It usually takes form of…
SELECT * FROM table
SELECT tells SQL the columns that you would like to have returned form your table. You can explicitly pass SELECT the name of the column, or use the * (wildcard) operator to tell SELECT you would to have all columns returned. SELECT’s power really shines when joined with other more advanced methods.
FROM tells SQL what table you want to look in. If I had the statement
SELECT * FROM animals
SQL is going to look for a table name animals to pull my data from. FROM really starts to take flight when combined with its friend JOIN. But we aren’t there quite yet.
WHERE is the first time we begin to see the usefulness of SQL. Using just FROM and SELECT we aren’t able to do much more than view basic columns in a table. WHERE allows us to apply a bit of logic to our query that saves us a lot of time when compared to just searching for the data ourselves, especially on a large dataset. A typical WHERE statement will look something like this…
SELECT * FROM animals WHERE animals.type = 'Bear'
This statement looks through the animals table and returns all the rows for all animals with the type of ‘Bear’. You can see the WHERE statement is broken down into a couple of different parts.
First, you need to tell it what column you want to look in. In the statement above, I’m looking in the ‘type’ column of the animals table. Next you need to give WHERE some condition. There are many different conditions that SQL allows you to use, but equality is the most basic ones. The condition in my SQL query is that column ‘type’ is equal to bear.
This will look through each row, reading the value of the column ‘type’, and seeing if it is equal to bear. For any row that is true, all of its columns will be returned.
Just the Basics
Now these are just some of the most basic commands in SQL. Consider this a living document that I’ll come back and update as time allows. SQL can be a hairy beast, and I imagine another guide out in the world can only help.