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:
query FROM table_name;
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 ]
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 string,
client_origin string
);
INSERT INTO client
(client_id, client_name, client_origin)
VALUES
(181891,'Oxla','Poland'),
(181892,'Google','USA'),
(181893,'Samsung','South Korea');
SELECT * FROM client;
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.
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 string,
category string,
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);
SELECT * FROM product;
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 string,
budget int
);
INSERT INTO category
(categoryName, budget)
VALUES
('Shoes', 7000),
('Shirt', 9000),
('Bag', 8000),
('Skirt', 7500),
('Hat', 7000);
SELECT * FROM category;
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 |
+------------------------------------+-----------+----------+