Basic about FROM Clause – using and example

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 the use of the FROM clause in a SQL statement, there need to be at least one table listed in the FROM clause. If there are two or extra tables listed in the SQL FROM clause, these tables are generally joined using INNER or OUTER joins.

DDL/DML for Examples

If you choose to comply with along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your personal database!

Get DDL/DML

Example – One Table Listed in the FROM Clause

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

In this example, we have a desk referred to as 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 data selected. These are the consequences that you have to 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 got used the FROM clause to list the table referred to as suppliers. There are no joins performed in this query considering we have solely listed one table.

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

Let’s seem 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 known as 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 documents selected. These are the outcomes that you must see:

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

This instance uses the FROM clause to be part of 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 seem to be at how to use the FROM clause when we be a part of two tables collectively the usage of an OUTER JOIN. In this case, we will appear at the LEFT OUTER JOIN.

Let’s use the same merchandise 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 archives selected. These are the results that you ought to see:

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

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

Now, the remaining record with the product_name of ‘Kleenex’ will show up in our end result set with a NULL value for the category_name. This file did no longer appear in our effects when we carried out an INNER JOIN.