FROM Clause in SQL

The from clause defines the tables used by a query, along with the means of linking the tables together. This definition is composed of two separate but related concepts, which we explore in the following sections.

Tables –

When confronted with the term table, most people think of a set of related rows stored in a database. While this does describe one type of table, I would like to use the word in a more general way by removing any notion of how the data might be stored and concentrating on just the set of related rows. Four different types of tables meet this relaxed definition:

Permanent tables (i.e., created using the create table statement)

Derived tables (i.e., rows returned by a subquery and held in memory)

Temporary tables (i.e., volatile data held in memory)

Virtual tables (i.e., created using the create view statement)

Each of these table types may be included in a query’s from clause.

Let’s see the general syntax of the FROM clause and then we will dive deeper into it.

Syntax –

SELECT column1, column2
FROM table_name

Let’s say we want to select all the data from the language table. For that we can write.

SELECT *
FROM language;

Or we can specify the name of the columns like this

SELECT 
    language_id,
    name,
    last_update
FROM language;

Now, we know how to use the from clause. Let’s see what other kinds of tables can be used in the from clause.

Derived (subquery-generated) tables –

A subquery is a query contained within another query. Subqueries are surrounded by parentheses and can be found in various parts of a select statement; within the from clause, however, a subquery serves the role of generating a derived table that is visible from all other query clauses and can interact with other tables named in the from clause. Here’s a simple example:

SELECT concat(cust.last_name, ', ', cust.first_name) AS full_name
FROM (
    SELECT first_name, last_name, email
    FROM customer
    WHERE first_name = 'JESSIE'
    ) cust;

In this example, a subquery against the customer table returns three columns, and the containing query references two of the three available columns. The subquery is referenced by the containing query via its alias, which, in this case, is cust. The data in cust is held in memory for the duration of the query and is then discarded. This is a simplistic and not particularly useful example of a subquery in a from clause; you will find detailed coverage of subqueries in our later posts.

Temporary Table –

Although the implementations differ, every relational database allows the ability to define volatile, or temporary, tables. These tables look just like permanent tables, but any data inserted into a temporary table will disappear at some point (generally at the end of a transaction or when your database session is closed). Here’s a simple example showing how actors whose last names start with J can be stored temporarily:

CREATE TEMPORARY TABLE actors_j
(actor_id   smallint(5),
first_name  varchar(45),
last_name   varchar(45)
);

INSERT INTO actors_j
SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'J%';

SELECT * FROM actors_j;

These rows are held in memory temporarily and will disappear after your session is closed.

Views –

A view is a query that is stored in the data dictionary. It looks and acts like a table, but there is no data associated with a view (this is why I call it a virtual table). When you issue a query against a view, your query is merged with the view definition to create a final query to be executed. To demonstrate, here’s a view definition that queries the employee table and includes four of the available columns:

CREATE VIEW cust_vw AS 
SELECT customer_id, first_name, last_name, active
FROM customer;

When the view is created, no additional data is generated or stored: the server simply tucks away the select statement for future use. Now that the view exists, you can issue queries against it, as in:

SELECT first_name, last_name
FROM cust_vw
WHERE active = 0;

Views are created for various reasons, including to hide columns from users and to simplify complex database designs.

Table Links –

The second deviation from the simple from clause definition is the mandate that if more than one table appears in the from clause, the conditions used to link the tables must be included as well. This is not a requirement of MySQL or any other database server, but it is the ANSI-approved method of joining multiple tables, and it is the most portable across the various database servers. We will discuss more about joining tables in our later posts but here is one example of it.

SELECT customer.first_name, 
       customer.last_name,
       time(rental.rental_date) rental_time
FROM customer
INNER JOIN rental
ON customer.customer_id = rental.customer_id
WHERE date(rental.rental_date) = '2005-06-14';

The previous query displays data from both the customer table (first_name, last_name) and the rental table (rental_date), so both tables are included in the from clause. The mechanism for linking the two tables (referred to as a join) is the customer ID stored in both the customer and rental tables. Thus, the database server is instructed to use the value of the customer_id column in the customer table to find all of the customer’s rentals in the rental table. Join conditions for the two tables are found in the on subclause of the from clause; in this case, the join condition is ON customer.customer_id = rental.customer_id. The where clause is not part of the join and is only included to keep the result set fairly small, since there are more than 16,000 rows in the rental table.

Defining Table Aliases –

When multiple tables are joined in a single query, you need a way to identify which table you are referring to when you reference columns in the select, where, group by, having, and order by clauses. You have two choices when referencing a table outside the from clause:

Use the entire table name, such as employee.emp_id.

Assign each table an alias and use the alias throughout the query.

In the previous query, I chose to use the entire table name in the select and on clauses. Here’s what the same query looks like using table aliases:

SELECT c.first_name, 
       c.last_name,
       time(r.rental_date) rental_time
FROM customer c 
INNER JOIN rental r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

If you look closely at the from clause, you will see that the customer table is assigned the alias c, and the rental table is assigned the alias r. These aliases are then used in the on clause when defining the join condition as well as in the select clause when specifying the columns to include in the result set. I hope you will agree that using aliases makes for a more compact statement without causing confusion (as long as your choices for alias names are reasonable). Additionally, you may use the as keyword with your table aliases.

SELECT c.first_name, 
	c.last_name,
	time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';