FROM Clause in MySQL

Syntax of the FROM Clause

The basic syntax for the FROM clause in MySQL is the same as in standard SQL:

In this syntax, SELECT specifies the columns to retrieve, FROM specifies the table or tables to retrieve data from, and WHERE specifies any conditions to filter the data. For example, the following MySQL query selects all columns from the employee's table:

Use of the FROM Clause with SELECT Statements

The FROM clause is an essential part of the SELECT statement in SQL. It is used to specify the table or tables from which the data is to be retrieved. The syntax for using the FROM clause in a SELECT statement is as follows:

Here, column1, column2, etc., represent the columns of the table that you want to select, and table1 represents the name of the table from which you want to retrieve the data.

let's consider an example where we want to retrieve a subset of rows from the student's table based on a certain condition. Suppose we want to retrieve the name and age columns for students who are in the 12th class. The SELECT statement would look like this:

This will retrieve only the name and age columns for students who are in the 12th class. The WHERE clause is used to filter the rows based on the condition class = '12th'.

Joining Tables with the FROM Clause

Joining tables means taking the combination of rows of multiple tables at once which satisfy the specific condition as given in the query. To join multiple tables at once we have to use various JOINs incorporated with the FROM clause in MySQL.

Explanation of How to Use the FROM Clause to Join Multiple Tables

As SQL works on relational databases, the data is presented in various tables linked together. To extract information from interlinked tables we perform join operation. The FROM clause with JOIN is used to tell which tables are joined together that are sufficient to perform the required query.

MySQL provides various join types, such as INNER JOIN, LEFT JOIN, and RIGHT JOIN. These types allow combining data from multiple tables. To illustrate the use of the FROM clause with join, the following example can provide a clearer understanding.

In this example, table1 and table2 are the names of the tables being joined, and the column name is the column on which the join is being performed. The ON keyword provides the join condition. All rows from one table and any matching rows from the other table are returned by JOIN.

Different Types of Joins, Including INNER JOIN, LEFT JOIN, and RIGHT JOIN

Example of Inner JOIN:

This example involves the joining of two tables, namely table1 and table2. The INNER JOIN operation retrieves only the rows from table1 and table2 that meet the specified condition.

Example of LEFT JOIN:

In this example, two tables table1 and table2 are taken to perform the joining operation. In the LEFT JOIN operation all the rows of table1 would be returned along with rows of the table2 for which the join condition is satisfied.

Example of RIGHT JOIN:

In this example, two tables table1 and table2 are taken to perform the joining operation. In the RIGHT JOIN operation all the rows of table2 would be returned along with rows of the table1 for which the join condition is being satisfied.

Using Subqueries with the FROM Clause

MySQL also allows the user to write nested queries to perform complex operations easily. The FROM clause is used to write the nested query in which the subquery is like a virtual table whose data can be used to extract meaningful information.

Explanation of Subqueries and How they can be used with the FROM Clause

A subquery is a query nested within another query, where the result of the inner query is used as input to the outer query. The subquery is enclosed in parentheses and can appear in various clauses of a SQL statement, including the SELECT, WHERE, HAVING, and FROM clauses.

When used with the FROM clause, a subquery is treated as a virtual table that can be joined with other tables in the query. The subquery is executed first, and its result set is then used as a temporary table that can be referenced in the outer query.

Examples of How to Use Subqueries with the FROM Clause to Filter Data

Suppose we want to retrieve information from an orders table subquery that determines the total amount spent by each customer. Then, only those rows are chosen where a customer's cumulative spending exceeds 1000. The SQL query would look like this:

Query:

Explanation:

In this example, the inner query uses the GROUP BY clause and SUM function to determine the total amount spent by each client in the orders table. A result set containing the columns customer_id and total_spent is returned. Only rows with a total_spent column value of more than 1000 are returned by the outer query, which chooses all columns (*) from the inner query's result set. The outer query uses the inner query's alias customer_totals to refer to the result set. The inner query is enclosed in parentheses and given this name.

1. Using a subquery to join tables Query:

   Explanation:

   In this example, the subquery is used to select only the id and name columns from table1. The outer query then joins this result set with table2 on the id column.

2. Using a subquery to calculate aggregate values:

   Query:

   Explanation:

   In this example, the subquery is used to calculate the total value for each category in mytable. The outer query then selects only the rows where the total value is greater than 1000.

See Also

You can also refer to below mentioned topics to have more understanding regarding topics related to FROM clause in MySQL:

1. HAVING clause
2. GROUP BY clause
3. ORDER BY clause
4. USING clause