totn SQL

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:

Try It
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:

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';

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:

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';

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.