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:
FROM table1_name
[ { JOIN
| LEFT JOIN
| RIGHT JOIN
| OUTER JOIN } table2_name
ON table1_name.column1 = table2_name.column1 ]
The examples below are executed in the
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.
CREATE TABLE client (
client_id int,
client_name text,
client_origin text
);
INSERT INTO client
(client_id, client_name, client_origin)
VALUES
(181891,'Oxla','Poland'),
(181892,'Google','USA'),
(181893,'Samsung','South Korea');
It will create a table as shown below:
+------------+--------------+------------------+
| client_id | client_name | client_origin |
+------------+--------------+------------------+
| 181891 | Oxla | Poland |
| 181892 | Google | USA |
| 181893 | Samsung | South Korea |
+------------+--------------+------------------+
- Run the following query:
SELECT client_name, client_origin FROM client;
- You will get a list of the client’s data for a successful result:
+--------------+------------------+
| client_name | client_origin |
+--------------+------------------+
| Oxla | Poland |
| Google | USA |
| Samsung | South Korea |
+--------------+------------------+
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.
More than one table can be defined by separating it with a comma (,).
Syntax
Here is an example of the sub-query syntax that uses a FROM clause:
SELECT X.column1, X.column2, X.column3
FROM table_2 as X, table_1 as Y
WHERE conditions (X.column, Y.column);
-
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
CREATE TABLE product (
id int,
product text,
category text,
price int
);
INSERT INTO product
(id, product, category, price)
VALUES
(445747,'Court vision women’s shoes nike','Shoes', 8000),
(445641,'Disney kids h&m','Shirt', 6500),
(477278,'Defacto adidas','Hat', 8500),
(481427,'Sophie shopping bag','Bag', 6500),
(411547,'Candy skirt zara','Skirt', 6500),
(488198,'Slim cut skirt hush puppies','Skirt', 7600);
It will create a table as shown below:
+---------+----------------------------------+-----------+--------+
| id | product | category | price |
+---------+----------------------------------+-----------+--------+
| 445747 | Court vision women’s shoes nike | Shoes | 8000 |
| 445641 | Disney kids h&m | Shirt | 6500 |
| 477278 | Defacto adidas | Hat | 8500 |
| 481427 | Sophie shopping bag | Bag | 6500 |
| 411547 | Candy skirt zara | Skirt | 6500 |
| 488198 | Slim cut skirt hush puppies | Skirt | 7600 |
+---------+----------------------------------+-----------+--------+
category table
CREATE TABLE category (
categoryName text,
budget int
);
INSERT INTO category
(categoryName, budget)
VALUES
('Shoes', 7000),
('Shirt', 9000),
('Bag', 8000),
('Skirt', 7500),
('Hat', 7000);
It will create a table as shown below:
+---------------+----------+
| categoryName | budget |
+---------------+----------+
| Shoes | 7000 |
| Shirt | 9000 |
| Bag | 8000 |
| Skirt | 7500 |
| Hat | 7000 |
+---------------+----------+
- Run the following query to know and ensure the average value of all category’s budgets:
select avg(budget) as avgBudget from category;
- The average budget of all categories from the category table is 7700.
+--------------------+
| avgbudget |
+--------------------+
| 7700.000000000000 |
+--------------------+
- 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.
select P.product, P.category, P.price from
(select avg(budget) as avgBudget from category) as C, product as P
where P.price > C.avgBudget;
➡️ The output will display “court vision women’s shoes nike” and “Defacto adidas” as the products with a price of more than 7700.
+------------------------------------+-----------+----------+
| product | category | price |
+------------------------------------+-----------+----------+
| court vision women`s shoes nike | shoes | 8000 |
| Defacto adidas | hat | 8500 |
+------------------------------------+-----------+----------+