Learn about table expression and its implementations

In this article we will learn about table expression and its implementations.Table Expression is likely virtual table.A table that is derived by table expression is a temporary table object.In this lesson we will learn temporary table, table variable, Common table expression and derived tables.These table expressions are result set from a sql query.

To study and learn about table expression and its implementations in detail follow the points given below:

Derived Tables:

Derived table is defined in the from clause of select query

These tables do not contain record in physical structure, it is only a temporary or virtual structure.

It is sub query used with from clause

All fields in the derived table must be unique and have unique alias.

In most of the cases derived table is used to get all columns in a row for particular group. As you know a group by returns only that column that involve in group by clause or column that doing some aggregate function.

Example: In this case I am going to make derived table that show full detail of department table to show how many worker work in each department by making a aggregate sub query .

I am showing all columns of table tbldepartment by making tbldepartment.* with join of aggregate query of salary table

By executing the query in from clause (selected part in above sub query) it only displays dept id and its number of employee with total disbursed salary, now to get additional information we will assign sub query to table alias called salaryexpression.

This will be used as derived table, as if deptId, empcount and disbursedsalary is the data source being sent to the outer query. And then we will join outer query to salaryexpression to get additional information of department.

Now run full query:

select  tbldeparment.*, Disbursedsalary ,empcount from tbldeparment join
	 (
	 select dept_id as deptid, count(*) as empcount, sum(monthlysalary) as Disbursedsalary from salary group by dept_id
	 )
	 as
  salaryexpression
on tbldeparment.dept_id  =salaryexpression.deptid

Output:

Common Table Expression:

CTE is also similar to derived table.

Common table expression also known as CTE or SQL With.

The scope of these expressions is only in the outer query. The CTE table goes out of scope when outer query is finished.

Column must have unique name

Let’s execute the same example that we have used in derived table.

;WITH Sales_CTE (deptid,Disbursedsalary ,empcount )
AS
--  CTE query data source for outer query.
(
    select dept_id as deptid, count(*) as empcount, sum(monthlysalary) as Disbursedsalary from salary group by dept_id
)
-- outer query referring the CTE.
SELECT tbldeparment.*,empcount,Disbursedsalary
FROM Sales_CTE inner join tbldeparment  on tbldeparment.dept_id  =Sales_CTE.deptid

Output:

Temporary Table:

Temporary table as name suggest are not a real table.

Temporary tables are used as per logic concern in a code for business activity.

We can use CURD process in temporary table easily that you can generally use with real tables.

The scope of temporary table remains only in current query.

Temporary table gets closed by default when you close sql connection.

In most cases temp table is used to store that record in temp table which need’s manipulation before inserting the final data to real table.

After data gets stored in temp table you can easily do manipulation of data in temp table.

It is useful when need manipulation over large data

Temp table needed to drop, when the execution of the query is finished.

Syntax:

create table #temp_table_name
( col1 datatype,
  col2 datatype,
  ...
);

In this example, I am adding record to temporary table of real table salary to make salary of employee whose department is IT (Information Technology).

create table #temp_table_salary
( empid int,
  empname varchar(200),
  department varchar(250),
  Allowances bigint,
  Deduction bigint,
  NetSalary bigint
);

Till now I have created temporary table, let’s insert record from real table that is salary for making net salary of department employee.

Now to make net salary we simply make update query in temporary table.

update #temp_table_salary set netsalary =Allowances-deduction

select * from #temp_table_salary
drop table #temp_table_salary

Output:

Table variable:

Table variable is alternative approach of temporary table

It is not useful when there is large data

No need to drop table when work is finished.

The concept is similar to temporary table.

Need @ signature along with table name

Syntax:

Declare @TempTableVariable TABLE(
col1 datatype,
col2 datatype, 
...,

)

You can exercise same example that I used in temporary table in table variable.

Thus, we learnt about table expression and its implementations along with the queries.It also described about temporary table, table variable, Common table expression and derived tables explaining all the facts and figures about it.