What is a SELECT statement?
So, what does SELECT do? Very simply, it tells the DBMS that you want to get some data out of the database.
So, how does it work? SELECT can range from very simple to painfully complex. With SELECT there are a whole host of different options available to you that will allow you specify, calculate and arrange your data in an infinite number of ways.
Using a SELECT statement obviously begins with the SELECT command. Next, you will need to specify what column(s) of data you will be retrieving. For example, using our test table that we created say we want to get someone’s email address and first name. You would let the DBMS know what you are looking for by starting your SELECT statement like this:
SELECT first_name, email
This tells the DBMS that we are getting information only from the “first_name” and “email” columns of our table.
So, what if I wanted to select all the columns in the table? Would I have to spell out each and every column in a table? Wouldn’t that be a pain with a table that has a bunch of columns?
Yes, it would be a pain and no you don’t have to specify each column. If you want to get all of the columns out of any table you only need to use just one character, “*”, like this:
SELECT *
The "*" tells the DBMS to send every column in the table. Making use of the asterisk is very common but shouldn't be overused. Some developers have a tendency to use the asterisk almost exclusively even when they don't intend to use most of the information they are retrieving. While this may cut down on the amount of code you have to type in, it can be an incredible waste of system resources if it is abused.
Alright, what if I want to get stuff out of a few different tables? Do I have to make a SELECT statement for each table?
No, you can actually combine data from several different tables into one SELECT statement. However, this particular topic we are going to save for a bit later in the series.
The next part of the SELECT statement is the FROM clause.
What is a FROM clause?
As you might have guessed, the FROM clause tells the DBMS what table to look in. Using our table we created in Part 2, our SELECT statement should look something like this now:
SELECT first_name, email
FROM contacts
Can I use more than one table in the FROM clause?
Yes, you can. This can be a more complex issue which I will explain later in the series along with selecting multiple column names from different tables.
Now, if you wanted to you could stop right here. Just add a semicolon to the end of the SELECT statement above and you would get every first name and email in the "contacts" table.
But what if you wanted to get data for people with the first name of "Paul"? Well, that's when the WHERE clause comes in to play.
What is a WHERE clause?
A WHERE clause allows you to filter out any unwanted data so that the data you do get is exactly what you are looking for. Let's look for all of the people named "Paul" in our table.
SELECT first_name, email
FROM contacts
WHERE first_name = 'Paul';
Using the WHERE clause tells the DBMS you are only interested in data that has "Paul" in the "first_name" column. In our case we will get only one result because there is only one person in our "contacts" table with the name of "Paul".
So, what if I want to filter my data using a number like in the "contact_id" column? Can I do that?
Absolutely. To search for the person that has the "contact_id" of 3 your SELECT statement would look like this:
SELECT first_name, email
FROM contacts
WHERE contact_id = 3;
Notice there are no single quotes around the number 3 this time. That's because we are filtering by a column that is numeric, not text.
Can I filter data in other ways?
You bet. There are a whole host of operators that you can use to make comparisons and filter data. Keep in mind, though, each DBMS can be different and the operators they choose to use can change from DBMS to DBMS.
Here are some of the most common operators that DBMSs use.
Operator Description = Compares the data to your criteria to see if it is equal <> Compares the data to your criteria to see if it is not equal < Compares the data to your criteria to see if the data is less than your criteria <= Compares the data to your criteria to see if the data is less than or equal to your criteria > Compares the data to your criteria to see if the data is greater than your criteria >= Compares the data to your criteria to see if the data is greater than or equal to your criteria IS NULL Checks the data to make sure that there is no data in the column
What's Next?
Now you have enough basic information to create a database, create tables, enter data and retrieve data. Now is when it starts to get more interesting. We can start to get organized.
