You use the FROM clause to specify the tables from which data is to be selected.
In order to read rows in the specified tables, you must either own these tables or have SELECT permission.
SELECT ...
FROM table_specification,...
table_specification
Specification of a table from which data is to be read. You can only specify tables located in the same database.
Examples
The columns CUST_NUM from the CUSTOMERS table and ORDER_NUM from the ORDERS table are selected on the basis of the Cartesian product of the CUSTOMERS and ORDERS tables. The CUSTOMERS and ORDERS tables are renamed within the SELECT expression by assigning correlation names. Every column specification within the SELECT expression which references the CUSTOMERS or ORDERS table must then be qualified with the correlation name. Correlation names can be used to qualify columns uniquely, to abbreviate long table names or to specify the appropriate table name in SELECT expressions. The columns A.CUST_NUM and B.ORDER_NUM are selected from the Cartesian product of the CUSTOMERS and ORDERS tables.
SELECT A.cust_num, B.order_num FROM customers A, orders B
Derived table
cust_num | order_num |
100 | 200 |
100 | 210 |
100 | 211 |
etc. | etc. |
107 | 300 |
107 | 305 |
The table ORDSTAT is renamed as ORDERSTATUS and the columns ORD_STAT_NUM and ORD_STAT_TEXT are selected using the new names ORDERSTATUSNUMBER and ORDERSTATUSTEXT. If all columns are selected by specifying “*” in the SELECT list, it is possible to assign new column names using “(column, ...)” in table_specification. Unlike the AS clause in the SELECT list, it is not possible to rename individual columns. It is only possible to rename all columns. The new names must be used in place of the old names in the WHERE, GROUP BY and HAVING clauses in the SELECT list.
SELECT * FROM ordstat
AS orderstatus (orderstatusnumber, orderstatustext)
If a table is specified more than once in the FROM clause, as is the case when a table is joined to itself, correlation names must be defined to allow unique identification of columns. References in the SELECT list and in the WHERE, GROUP BY and HAVING clauses must use these correlation names instead of the original table names.
SELECT A.cust_num, B.cust_num FROM customers A, customers B