What is Subquery?
Subqueries are defined as the queries which are used inside a query. Subqueries are commonly used along with WHERE clause to filter the rows returned from the outer query. Subqueries are classified as a SELECT statement which can be nested with other statements.
Example for Subquery
Let us consider that we have an employee table with fields like emp_name, emp_id, emp_designation and emp_salary. A another table named department with fields like manager id and department name. Using these data the following SQL can be used as a subquery for the two tables.
select emp1name
from employee1
where emp1_salary >
— this is a subquery1:
(select avg(employee1_salary)
from employee1)
Using the above SQL, we can find the employee list who has salary above the average. The query used to find the average employee salary is the subquery because it filters the employee list with above salary average.
Subquery Example without Where Clause
INSERT INTO compscience_group(id, name1)
— this is a subquery1:
SELECT id, name1
FROM student WHERE subject= ‘comp’
In the SQL code used above, a subquery was used to select id, name of a student group from the table student. The selected id and name are inserted into the science1_group table.
What is Derived Table?
Derived table can be defined as a subquery used in the FROM statement. It is called as derived table because it has the characteristics of a table when used inside the query. Derived table cannot be used outside the query because its functions exist only in the query where it was created. Derived tables are not real tables and they are not part of the database.
Example for Derived Table
select max(age1)
from (
— this is the part of query which derived by:
select age1 from table1
) as Age1 — It must be derived in the table as an alias.
In the above example, derived table are declared inside the FROM statement. The result of the “select age from table” is called as the derived table of the SQL statement given above. In SQL, every table has its own alias so “as age” is used at end of the code. If “as age” is not used then the compiler will throw an error.
How to Create a Derived Table?
- Right click your mouse over the derived table schema and select the derived table from the menu. Type the name of derived table inside the dialog box.
- Type the SQL expression for your derived table inside the SQL expression box. You can also use the objects from tables and columns to build the SQL for the derived table.
- Click the SQL expression in order to validate it.
- Derived table will look similar to the tables used in the database. Columns of derived tables can be used to build objects in the derived table.
Check: What is Blind SQL Injection?
How Derived tables are implemented?
- First import the Universe; then click Insert option and choose derived tables.
- Write the SQL expression and name the derived table.
- Please enter valid alias to the columns which require calculations.
- Click on the “Check Syntax” button to detect the errors present in the SQL statement.
- The derived table can be seen in the universe canvas. The columns can be dragged into the canvas to create objects in the derived table.
- Using the suitable joins, link the derived table to the database.
Difference between Derived Tables and Subqueries
- Subquery is a SELECT statement which can be nested along with other statement. Derived table function are similar to the tables used in the database.
- Subqueries are used from the WHERE clause of a SQL statement while derived tables are used in the FROM clause of a SQL statement.
- Subqueries are used to select one table and insert it into another table.
- Subset query problem of derived table can be eliminated by creating an external table.