Coding truck

Select, from, and where sql clauses

One thing that helps beginners to learn sql fast is to understand the structure of a statement. A sql select statement is made up of clauses. Each clause has a purpose and the clauses have to be in a specific order. In this post we will take a closer look at the three basic clauses of a sql statement.

The select clause

The simplest select statements only need a select clause. This is because the select clause is used to tell the database what you would like to see. For instance if you want to ask Postgres for the number 1 you would select it.

Select 1

This is the purest select clause we can write. Asking for a single static value. The database understands the request and it returns it to us.

Now, let's ask Postgres for the current date. Postgres understands the current_date keyword as a request for the current date.

Select current_date

The database gives us the current date

date
2019-08-30

The from clause

One way to think about the sql query is like a food order. You might say, "Can I please get a hamburger, fries and a soda?". In sql you would write that as:

Select
  Hamburger
  ,Fries
  ,Soda

If you try to run this sql, the database will give you an error. You haven't told it where to find the burger, fries and drink. That's because there isn't a place to get them in the database right now. Let's make one!

Create table BurgerShack (
  Hamburger varchar(50)
  ,Fries varchar(50)
  ,Soda varchar(50)
  ,Location varchar(50)
);

Insert Into BurgerShack(Hamburger, Fries, Soda, Location)
Values ('A delicious burger', 'Hot fried potatoes', 'An icy cold beverage', 'Down the street');

Insert Into BurgerShack(Hamburger, Fries, Soda, Location)
Values ('A burger', 'Cold fries', 'Warm flat sugar water', 'Far far away');

We have created a burger shack table and filled it with two burger possibilities. Let's try our select statement again.

Select
  Hamburger
  ,Fries
  ,Soda

Still getting an error even though we have created the burger shack. There must be something that is still missing.

Tell the database where the data is from

Turns out that the select statement is missing a clause; the from clause. The from clause tells the database which tables contain the data. In our case, the table we created is called BurgerShack. So let's try adding a from clause.

Select
  Hamburger
  ,Fries
  ,Soda
From
  BurgerShack

Success!

Hamburger Fries Soda
A delicious burger Hot fried potatoes An icy cold beverage
A burger Cold fries Warm flat sugar water

Where the bad burgers go

So far we have created a burger shack table, inserted two records into it. Then we requested the records back in a sql query. That's really good!

There is just one more thing we need to do to wrap this up. Right now we are getting back a delicious burger meal and a meh burger meal. We want to filter the rows that we are getting back.

We use the where clause to filter the rows returned by a query. When we asked for a burger, we meant that we wanted it from "down the street". Looks like we are getting the burgers back from down the street and far far away.

Select
  Hamburger
  ,Location
From
  BurgerShack
Hamburger Location
A delicious burger Down the street
A burger Far far away

Let's use a where clause to specify the location

Select
  Hamburger
  ,Fries
  ,Soda
From
  BurgerShack
Where
  Location = 'Down the street'

Success! We got the tasty meal that we asked for!

Hamburger Fries Soda
A delicious burger Hot fried potatoes An icy cold beverage

Conclusion

In this post we reviewed the three main clauses of a sql select query.

  • The select clause is used to tell the database what you would like to see
  • The from clause tells the database which tables contain the data
  • The where clause is used to filter the rows returned by a query
Next
Prev