FROM
Overview
The FROM
clause is used to specify which table or joins are required for the query/statement (e.g., SELECT
statement) to return or obtain data.
Syntax
There must be at least one table listed in the FROM
clause. See the following syntax:
If two or more tables are listed in the FROM
clause, these tables are joined using JOIN, RIGHT JOIN, LEFT JOIN, or OUTER JOIN, depending on the operations to be queried as seen in the syntax below:
public
schema, the default schema in Oxla. You can also create, insert, and display a table from other schemas - click here for more info.Example
We’ll start by looking at how to use the FROM
clause with only a single table.
There is a client table, and we want to know the client’s name and the city where the company is based.
It will create a table as shown below:
- Run the following query:
- You will get a list of the client’s data for a successful result:
FROM - Sub Queries
FROM clause is also used to specify a sub-query expression. The relation created from the sub-query is then used as a new relation on the other query.
Syntax
Here is an example of the sub-query syntax that uses a FROM clause:
-
The sub-query in the first
FROM
clause will select the columns from the specific table using a new temporary relation (SELECT X.column1, X.column2, X.column3 FROM
). -
Set the tables into a new temporary relation (
table_2 as X, table_1 as Y
). -
Next, the query is evaluated, selecting only those rows from the temporary relation that fulfill the conditions stated in the
WHERE
clause.
Example
We want to find a product whose price exceeds all categories’ average budget.
product table
It will create a table as shown below:
category table
It will create a table as shown below:
- Run the following query to know and ensure the average value of all category’s budgets:
- The average budget of all categories from the category table is 7700.
- Now, run the following query:
-
We specify the product table as P and the budget’s average value from the category table as C.
-
We will display the product’s name, category, and price.
-
We set the conditions where the product’s price exceeds the budget’s average value.
➡️ The output will display “court vision women’s shoes nike” and “Defacto adidas” as the products with a price of more than 7700.