Use the Tables tab to select the tables, derived tables, and views you want to include in your query. You can create derived tables using the Create a Derived Table button. You cannot create views in the Query Editor, but you can create them in Interactive SQL and reference them in the Query Editor.
Include tables that you want to get data from, and tables that you want to include in joins. If you include more than one table or view in your query, you should use the Joins tab to specify how the tables are to be joined.
WARNING! If you are including more than one table in your query, and the tables are large, you should define a join strategy on the Joins tab after adding each table. The processing can become very slow if you do not change the join strategy because the Query Editor sometimes has to default to a cross product and the Query Editor processes your query as you work.
Table pattern – To restrict the tables in the Matching Tables box, type the name or partial name of tables. You can use wildcard characters in the pattern. For example, to get only tables starting with SYS, type SYS%.
Owner pattern – To restrict the tables in the Matching Tables box, type the name or partial name of an owner. You can use wildcard characters in the pattern. For example, to get only tables starting with SYS, type SYS%.
Table type – To restrict the tables in the Matching Tables box, choose a type of table from the dropdown list. For example, you can choose to view only system tables.
Matching tables – This lists all the tables in the database that match the criteria above. The default is all tables.
Selected tables – To add a table to your query, select a table in the Matching Tables box and click the right arrow. The table appears in the Selected Tables box. To create a derived table for your query, click the middle icon (between the two arrows). If you add more than one table, you should use the Joins tab to specify a join strategy. When you add tables to your query by specifying them in the Selected Tables box, they are joined by default as cross products.
Results – Click Results at the bottom of the dialog to see the results of your query, or an error message if the query contains errors.
SQL – Click SQL at the bottom of the dialog to see the SQL code for your query. You will notice that this SQL looks a little different from the SQL you normally write. This SQL is fully formed, meaning that all tables are prefixed with owner name, all strings are enclosed in quotation marks, and so on. This extra formatting is not normally necessary, but it ensures that the SQL will work in all situations.
You can add views and derived tables to your query, as well as tables.
A view is a SELECT statement that is stored in the database as an object. You cannot create views in the Query Editor, but you can create them in Interactive SQL and enter them as tables in the Query Editor.
You can create derived tables in the Query Editor. Derived tables allow you to nest queries within a FROM clause. With derived tables, you can perform grouping of groups, or you can construct a join with a group, without having to create a view.
For more information, see Introducing the Query Editor.