FROM CLAUSE

This SQL tutorial explains how to use the SQL FROM clause with syntax and examples. Click the "Try It" button next to an example to test the FROM clause for yourself in our SQL Editor.

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

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
100	Microsoft	Redmond
200	RIM	Waterloo
300	Oracle	Redwood City
400	Google	Mountain View
500	Intel	Santa Clara
600	Samsung	Seoul

Enter the following SQL statement:

Try It

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

These are the results that you should see:

supplier_id	supplier_name	city
200	RIM	Waterloo
300	Oracle	Redwood City
100	Microsoft	Redmond

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 WITH 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

Enter the following SQL statement:

Try It

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

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 WITH 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:

Try It

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

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.