SQL: FROM Clause

This SQL tutorial explains how to use the SQL FROM clause with syntax and examples.

Description

The SQL FROM clause is used to list the tables and any joins required for the SQL statement.

Syntax

The syntax for the FROM Clause in SQL is:

FROM table1
[ { INNER JOIN
  | LEFT [OUTER] JOIN
  | RIGHT [OUTER] JOIN
  | FULL [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

These are the tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

Note

• When using the FROM clause in a SQL statement, there must be at least one table listed in the FROM clause.
• If there are two or more tables listed in the SQL FROM clause, these tables are generally joined using INNER or OUTER joins.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - One Table Listed in the FROM Clause

We'll start by looking at how to use the FROM clause that lists only a single table in the SQL statement.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_id < 400
ORDER BY city DESC;

There will be 3 records selected. These are the results that you should see:

supplier_id	supplier_name	city	state
300	Oracle	Redwood City	California
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California

In this example, we've used the FROM clause to list the table called suppliers. There are no joins performed in this query since we have only listed one table.

Example - Two Tables in the FROM Clause (INNER JOIN)

Let's look at how to use the FROM clause to INNER JOIN two tables together.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

And a table called categories with the following data:

category_id	category_name
25	Deli
50	Produce
75	Bakery
100	General Merchandise
125	Technology

Enter the following SQL statement:

SELECT products.product_name, categories.category_name
FROM products 
INNER JOIN categories
ON products.category_id = categories.category_id
WHERE product_name <> 'Pear';

There will be 5 records selected. These are the results that you should see:

product_name	category_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli

This example uses the FROM clause to join two tables - products and categories. In this case, we are using the FROM clause to specify an INNER JOIN between the products and categories tables based on the category_id column in both tables.

Example - Two Tables in the FROM Clause (OUTER JOIN)

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

Let's use the same products and categories tables from the INNER JOIN example above, but this time we will join the tables using a LEFT OUTER JOIN. Enter the following SQL statement:

SELECT products.product_name, categories.category_name
FROM products 
LEFT OUTER JOIN categories
ON products.category_id = categories.category_id
WHERE product_name <> 'Pear';

There will be 6 records selected. These are the results that you should see:

product_name	category_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli
Kleenex	NULL

This example uses the FROM clause to LEFT OUTER JOIN the products and categories tables based on the category_id in both tables.

Now, the last record with the product_name of 'Kleenex' will appear in our result set with a NULL value for the category_name. This record did not appear in our results when we performed an INNER JOIN.