MySQL – Derived Tables


MySQL – Derived Tables


Table of content


”;


Previous

Next

MySQL Derived Tables

The Derived tables are pretty much what their name describes: they are the tables that are derived from another MySQL database table (main table). In other words, the derived table is a virtual result-set obtained from a SELECT statement given as a subquery to another SELECT statement of the main table.

This table is similar to a temporary table. But unlike temporary tables, you need not create a derived table separately; the records in it are retrieved from the main table using a subquery. Therefore, similar to the actual database table, a derived table can also be displayed as a result-set of computations, aggregate functions, etc.

Syntax

Following is the basic syntax to display a derived table in MySQL −


SELECT column_name(s) FROM (subquery) AS derived_table_name;

Example

Let us see a simple example demonstrating how derived table is displayed in MySQL. In the following query, we are creating a new table CUSTOMERS −


CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query inserts 7 records into the above created table −


INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

To retrieve the records of the CUSTOMERS table, execute the following query −


SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −










ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are retrieving a derived table from this CUSTOMERS table using the following query −


SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

The derived table DERIVED_CUSTOMERS is hence obtained with ID, NAME and SALARY as its attributes.










ID NAME SALARY
1 Ramesh 2000.00
2 Khilan 1500.00
3 Kaushik 2000.00
4 Chaitali 6500.00
5 Hardik 8500.00
6 Komal 4500.00
7 Muffy 10000.00

Using WHERE Clause

We can also use the WHERE clause to filter records (or rows) from the derived table. Following is the syntax for it −


SELECT column_name(s) FROM (subquery) AS derived_table_name WHERE [condition];

Example

In the following query, we are retrieving a derived table from the CUSTOMERS table created initially. We are doing this by filtering rows from it using the WHERE clause −


SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS 
WHERE DERIVED_CUSTOMERS.SALARY > 5000.00;

Executing the query above will produce the following output −






ID NAME SALARY
4 Chaitali 6500.00
5 Hardik 8500.00
7 Muffy 10000.00

Aliasing a Column in Derived Table

In derived tables, not only the table name, but we can also alias a column name while displaying the contents. Following is the syntax −


SELECT column_name(s) AS alias_name(s) FROM (subquery) AS derived_table_name;

Example

In the example below, we are displaying the derived table from the CUSTOMERS table with the aliased columns using the following query −


SELECT ID AS DERIVED_ID, NAME AS DERIVED_NAME, SALARY AS DERIVED_SALARY 
FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −










DERIVED_ID DERIVED_NAME DERIVED_SALARY
1 Ramesh 2000.00
2 Khilan 1500.00
3 Kaushik 2000.00
4 Chaitali 6500.00
5 Hardik 8500.00
6 Komal 4500.00
7 Muffy 10000.00

Displaying Aggregate Functions as Derived Tables

We can also show the result of an aggregate function or calculations performed on the main table”s records as a derived table.

Following is the syntax to display aggregate functions as a derived table −


SELECT function_name() FROM (subquery) AS derived_table_name;

Example

In the following query, we are using the aggregate SUM() function to calculate the total salary from the CUSTOMERS table −


SELECT SUM(SALARY) FROM (SELECT SALARY FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −




SUM(SALARY)
35000.00

Example

In the following query, we use the aggregate AVG() function to calculate the average salary of customers from the CUSTOMERS table.


SELECT AVG(DERIVED_SUM) AS AVERAGE_SALARY 
FROM (SELECT SUM(SALARY) AS DERIVED_SUM FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −




AVERAGE_SALARY
35000.000000


Deriving Table Using a Client Program

Besides using MySQL queries to derive a table from another database table (main table), we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −


PHP
NodeJS
Java
Python

To derive a table from another database table through PHP program, we need to execute the SELECT statement using the mysqli function query() as follows −


$sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
$mysqli->query($sql);

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the query() function of the mysql2 library as follows −


sql ="SELECT column_name(s) FROM (subquery) AS derived_table_name";
con.query(sql);

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the JDBC function executeUpdate() as follows −


String sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
statement.executeQuery(sql);

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the execute() function of the MySQL Connector/Python as follows −


sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
cursorObj.execute(sql);

Example

Following are the programs −


PHP
NodeJS
Java
Python 


$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

//derived table (sub query)
$sql = "SELECT tutorial_title, tutorial_author FROM tutorials_table WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_table);";
if ($result = $mysqli->query("$sql")) {
    printf("Derived table query worked successfully!.
");
    while ($res = mysqli_fetch_array($result)) {
        print_r($res);
    }
}
if ($mysqli->errno) {
    printf("Derived table could not be worked!.
", $mysqli->error);
}

$mysqli->close();

Output

The output obtained is as follows −


Derived table query worked successfully!
Array
(
    [0] => MySQL
    [tutorial_title] => MySQL
    [1] => Aman kumar
    [tutorial_author] => Aman kumar
)
Array
(
    [0] => Python
    [tutorial_title] => Python
    [1] => Sarika Singh
    [tutorial_author] => Sarika Singh
)




var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SAMPLE(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO SAMPLE VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),(3, ''kaushik'', 23, ''Kota'', 2000.00 ),(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),(6, ''Komal'', 22, ''MP'', 4500.00 ),(7, ''Muffy'', 24, ''Indore'', 10000.00 );"
  con.query(sql);

  //retrieving a derived table
  sql = "SELECT ID, NAME, SALARY FROM (SELECT * FROM SAMPLE) AS DERIVED_SAMPLE;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Output

The output produced is as follows −


Connected!
--------------------------
[
  { ID: 1, NAME: ''Ramesh'', SALARY: ''2000.00'' },
  { ID: 2, NAME: ''Khilan'', SALARY: ''1500.00'' },
  { ID: 3, NAME: ''kaushik'', SALARY: ''2000.00'' },
  { ID: 4, NAME: ''Chaitali'', SALARY: ''6500.00'' },
  { ID: 5, NAME: ''Hardik'', SALARY: ''8500.00'' },
  { ID: 6, NAME: ''Komal'', SALARY: ''4500.00'' },
  { ID: 7, NAME: ''Muffy'', SALARY: ''10000.00'' }
]



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DerivedTable {public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String username = "root";
    String password = "password";
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        System.out.println("Connected successfully...!");

        //Lock table....
        String sql = "SELECT tutorial_title, tutorial_author FROM tutorials_tbl WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_tbl)";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Table derived successfully...!");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
            System.out.println();
        }

        connection.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}
}

Output

The output obtained is as shown below −


Connected successfully...!
Table derived successfully...!
Learn PHP John Paul
Learn MySQL Abdul S
JAVA Tutorial Sanjay
Python Tutorial Sasha Lee
Hadoop Tutorial Chris Welsh



import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
# Main query with a derived table (subquery)
main_query = """
SELECT d.tutorial_id, d.tutorial_title, d.tutorial_author, d.submission_date
FROM (
    SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
    FROM tutorials_tbl
    WHERE submission_date >= ''2023-01-01''
) AS d
WHERE d.tutorial_author LIKE ''%Paul%''
"""
cursorObj = connection.cursor()
cursorObj.execute(main_query)
result = cursorObj.fetchall()
print("Derived Table Result:")
for row in result:
    print(f"| {row[0]:<11} | {row[1]:<15} | {row[2]:<15} | {row[3]:<15} |")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −


Derived Table Result:
| 1           | Learn PHP       | John Paul       | <15 |



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *