SQL: Derived tables

 

A derived table is an expression that generates a table within the scope of a query FROM clause.

 

Example

If you use a sub query in a SELECT statement FROM clause, then derived table is created.

 

For example,

SELECT emp.emp_id, concat(emp.first_name, ',', emp.last_name) FROM

(

         SELECT * FROM employee

         WHERE

         emp_id % 2 != 0

) AS emp;

 

In the above example, sub query return all the columns of employee table. The subquery result is referenced by the containing query via its alias (in this case, alias is emp) . The data in ‘emp’ is held in memory for the duration of the query, and data is discarded after the query is done with execution.

 

Step 1: Create table employee.

CREATE TABLE employee (

    emp_id int,

    first_name varchar(255) NOT NULL,

    last_name varchar(255),

    email varchar(255) NOT NULL,

    PRIMARY KEY (emp_id)

);

 

Step 2: Insert some data into employee table.

INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (1, 'krishna', 'gurram', 'krishna@abcdef.com');

INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (2, 'ram', 'kota', 'kota@abcdef.com');

INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (3, 'gopi', 'battu', 'gopib@abcdef.com');

INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (4, 'harika', 'snem', 'harikas@abcdef.com');

mysql> CREATE TABLE employee (
    ->     emp_id int,
    ->     first_name varchar(255) NOT NULL,
    ->     last_name varchar(255),
    ->     email varchar(255) NOT NULL,
    ->     PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (1, 'krishna', 'gurram', 'krishna@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (2, 'ram', 'kota', 'kota@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (3, 'gopi', 'battu', 'gopib@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name, email) VALUES (4, 'harika', 'snem', 'harikas@abcdef.com');
Query OK, 1 row affected (0.00 sec)

 

Get all the records from employee table.

mysql> SELECT * FROM employee;
+--------+------------+-----------+--------------------+
| emp_id | first_name | last_name | email              |
+--------+------------+-----------+--------------------+
|      1 | krishna    | gurram    | krishna@abcdef.com |
|      2 | ram        | kota      | kota@abcdef.com    |
|      3 | gopi       | battu     | gopib@abcdef.com   |
|      4 | harika     | snem      | harikas@abcdef.com |
+--------+------------+-----------+--------------------+
4 rows in set (0.00 sec)

 

Step 3: Print employee id, and name where ‘id’ is odd number using derived table.

SELECT emp.emp_id, concat(emp.first_name, ',', emp.last_name) FROM

(

         SELECT * FROM employee

         WHERE

         emp_id % 2 != 0

) AS emp;

mysql> SELECT emp.emp_id, concat(emp.first_name, ',', emp.last_name) FROM 
    -> (
    -> SELECT * FROM employee
    -> WHERE
    -> emp_id % 2 != 0
    -> ) AS emp;
+--------+--------------------------------------------+
| emp_id | concat(emp.first_name, ',', emp.last_name) |
+--------+--------------------------------------------+
|      1 | krishna,gurram                             |
|      3 | gopi,battu                                 |
+--------+--------------------------------------------+
2 rows in set (0.00 sec)

 

 

 

 

 

 

 

Previous                                                    Next                                                    Home