Probably the most important data analysis lesson you will ever learn.
In this article, we’ll be looking at the most fundamental of fundamentals in the world of SQL:
The SELECT statement.
You don’t have to have be a college English major to have a wild stab in the dark at what the SELECT statement does for you.
As this is the Non-Boring Beginner’s Guide To SQL though, I’ll spell it out for you:
SELECT lets you grab whatever data you want from the database.
If you’re the greedy sort (read: lazy and couldn’t be bothered making a choice) then you can use SELECT to bring back all of the fields. Like this:
The * essentially means give me everything.
Every little bit. All of it. The lot. The whole lot. I don’t care if I can carry it or how long it takes to arrive, just give it to me.
Hint: don’t do this.
You can run into performance problems with this if you are hitting a big table or, worse, joining large tables together. We’re running before we walk here so I’ll advise being a bit more specific in your request.
Knowledge (of the fields) is Power
You should know the different fields that sit in your database table so it pays to be a bit more strategic when picking which ones you actually need to pull back.
Let’s say we are building a contact list and have all of our customer details in a table cunningly called CUSTOMERS.
If we want to get only the customer’s first name, date of birth and email address rather their full address and personal details we might do something like this:
SELECT firstname, dob, email;
Little bit more typing but less data being brought back and you actually get what you want so it’s well worth it.
We haven’t said where we want to get the data from however which brings us to the FROM part of the SELECT statement.
This tells the database which table you want to get the information out of.
SELECT firstname, dob, email FROM customers;
Now we know which fields we want to get back and where to get them from. This is now a valid SQL SELECT statement, well done. Stick it on your CV and away you go but wait just a second, we can improve it even more with just one more element.
The WHERE clause allows us to filter the results by a set of criteria.
Maybe you only want customers aged 18 years or older for your contact campaign so you decide to filter on date of birth. We can use the WHERE clause to do just that.
SELECT firstname, dob, email FROM customers WHERE dob < '01/01/2000';
What we are asking (or querying) the database for then is:
- First Name
- Date of Birth
- Email Address
- For ALL customers with a Date of Birth before 1st January 2000.
We’ve told it what we want, where we want it from and been even more specific about which sub-group of the wider group we want to see. We will cover the WHERE clause in a lot more depth in a future article in this series.
And that, my friends, is the SELECT statement.
It will be THE single most useful and most used statement you will experience in your SQL database querying careers.
I call it the “Million Dollar Keyword” because learning how to use the SELECT statement can EASILY add a million dollars to your overall career earnings over the course of your lifetime. If that doesn’t make you take extra care around soaking it into your brain then I don’t know what will.
Remember me each time you write it. (Hopefully in a positive fashion.) It just might save your life.
Check out the next lesson from my Learn SQL For Data Analysis series.