5. The From Clause

In the last chapter, we explored an extremely basic select statement that resulted in tables with only one row comprised of literal text that we specified. Now, it's time to start selecting things from the database! This is where the from clause comes in.

From Clause Basics

The from clause is optional in most databases (required in Oracle used by ROI CRM) but you can't do much in SQL without it. from tells the database which table(s) it should use to find the data in your select statement. Let's look at one simple example using our data.

select first_name
from accounts

This query tells the database, "look in the accounts table and return a list of every first_name value you find there." Try it out in the SQL console below.

Exercise 1: Select more columns

Our accounts table contains three columns: account_id, first_name, and last_name. In the SQL console below, write a query that selects them all.

Notice that in these examples, we did not use any column aliases as we did in chapter 4. When no column alias is used, the database will give your columns the same names they have in the database itself.

Select Star

Sometimes, we want to select all the columns from a single table, but we don't want to type out all the column names (because there are a a lot, or we are lazy 🙂). Fortunately, SQL has us covered with the * operator (called "star"). You can select all columns from a single table like this:

select * from accounts

This query tells the database, "Go into the Accounts table and pull out every single column for me." Try it out in the SQL console above.

With great power comes great responsibility

Just because we can select everything using * doesn't mean we should. It is best practice to select only the exact columns you need for a given purpose in your select statements. This is for a variety of reasons:

• Lessens the load on the database and network
• Ensures your query will return consistent results even if columns are later added to the table
• Produces a smaller result set that's easier to work with
• Potentially saves money if your database is cloud hosted

Exercise 2: Select from Addresses

In the SQL console below, write a query using the * operator that selects all columns from the addresses table.

Dot notation

Later in the course, we will be using more complex queries in which there are multiple tables per query. Some of these tables will have columns that have the same name. For example, both our accounts table and our transactions table contain the field account_id. In cases like this, we need some way to tell the database which column we're referring to. In SQL, we use dot notation for this task, which looks like this:

select accounts.account_id
from accounts

This tells the database, "Go into the accounts table and get me every single account_id. In case it wasn't clear which table account_id should come from, it should come from the accounts table."

We call this "dot notation" because we put a dot (a period, .) between our table name and our column name to form one unambiguous reference. Notice that in this query, we still only have one table but we're still using dot notation. This is completely fine and some would argue preferred because it makes your query easier to work with in the future. If you need to add tables to it later, you won't run into any errors due to ambiguous column references. In this course, we will use dot notation only where necessary or where it adds significant instructional value/clarity to save space on the page.

Exercise 3: Dot notation

In the SQL console below, write a query using dot notation to select the account_id column from the addresses table.

Table Aliases

In the previous chapter, we saw that we could alias column names in our query using as. We can do the same thing for tables, also using as, like so:

This query aliases the accounts table so that you can refer to it elsewhere in your query as simply a. Notice that in our select statement, we have used dot notation to specify that we want the account_id value to come from our "a" table, which in this case is the alias for accounts – dot notation works for table aliases just like it does for table names. Table aliases, like column aliases, can be (almost) as long as you want, but it's usually best practice to keep them short and meaningful.

In simple queries like this, table aliasing is not necessary. However, later on we will encounter situations where it's extremely useful (or even required!) Finally, note that if you alias a table name, you need to use that alias in all dot notation in your query where you're referring to the table. If you alias accounts as a, you cannot somewhere in your query refer to accounts.account_id. For the purposes of this entire query, the database is calling that table a, so it won't know what table accounts refers to.

Exercise 4: Table aliases

Use dot notation and table aliases to select the street_address field from the addresses table.

A final note about aliases

Technically, the as keyword is optional when defining aliases. For example, these two queries are identical:

select * from accounts as a

select * From accounts a

However, using as can make your queries easier to read, particularly in modern text editors that highlight certain keywords (notice as is green in the first query). In extremely complex queries with hundreds of lines, using as is also helpful if you need to locate a certain table or column alias to determine which database entity it is the alias for. For example, if we forget which table a refers to, we can search our query for the text as a and locate its source, whereas searching for a alone may return hundreds of spurious results.

In this course, we will always use as for aliases and I recommend you do, too (please don't ask my colleagues whether I follow my own advice 🙂).