MySQL: FROM Clause

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

Description

The MySQL FROM clause is used to list the tables and any joins required for the query in MySQL.

Syntax

The syntax for the FROM Clause in MySQL is:

FROM table1

[ { INNER JOIN

  | LEFT [OUTER] JOIN

  | RIGHT [OUTER] JOIN } table2

ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

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

Note

• When using the FROM clause in a MySQL statement, there must be at least one table listed in the FROM clause.
• If there are two or more tables listed in the MySQL FROM clause, these tables are generally joined using INNER or OUTER joins, as opposed to the older syntax in the WHERE clause.

Example - With one table

It is difficult to explain the syntax for the MySQL FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *

FROM order_details

WHERE quantity >= 10

ORDER BY quantity DESC;

In this MySQL FROM clause example, we've used the FROM clause to list the table called order_details. There are no joins performed since we are only using one table.

Example - Two tables with INNER JOIN

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

For example:

SELECT order_details.order_id, customers.customer_name

FROM customers

INNER JOIN order_details

ON customers.customer_id = order_details.customer_id

ORDER BY order_id;

This MySQL FROM clause example uses the FROM clause to list two tables - customers and order_details. And we are using the FROM clause to specify an INNER JOIN between the customers and order_details tables based on the customer_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.

For example:

SELECT products.product_id, inventory.qty_on_hand

FROM products

LEFT OUTER JOIN inventory

ON products.product_id = inventory.product_id

WHERE products.product_name = 'Database Software';

This MySQL FROM clause example uses the FROM clause to list two tables - products and inventory. And we are using the FROM clause to specify a LEFT OUTER JOIN between the products and inventory tables based on the product_id columns in both tables.