PostgreSQL 9.4: Using FILTER CLAUSE, multiple COUNT(*) in one SELECT Query for Different Groups

 PostgreSQL 9.4 has introduced one of the very good FILTER CLAUSE which is used to apply filters in aggregate functions.

Using FILTER, You can use different types of aggregate functions without applying any GROUP BY CLAUSE.

Now Imagine, that I have one Student table and I want total number of Students based different grades.
What happened without FILTER CLAUSE, We have to perform this calculation in the individual SELECT query.

But with the use of FILTER CLAUSE we can perform aggregation based on different FILTER values in a single SQL Query.

Below is a small demonstration of this:

Create one Student table with sample data:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE tbl_Students ( StudID INT PRIMARY KEY ,StudentName CHARACTER VARYING ,Marks INTEGER );   INSERT INTO tbl_Students VALUES (1,'Anvesh',88),(2,'Jenny',55),(3,'Tushar',85) ,(4,'Kavita',75),(5,'Manas',42),(6,'Martin',69) ,(7,'Roy',95),(8,'Benny',92),(9,'Neevan',82) ,(10,'Lee',43),(11,'Loother',65),(12,'Eric',58);

Apply FILTER clause to count number of Students based on Marks:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT COUNT(1) AS TotalStudents ,COUNT(1) FILTER (WHERE Marks BETWEEN 40 AND 60) AS TotalGrade_C ,COUNT(1) FILTER (WHERE Marks BETWEEN 60 AND 80) AS TotalGrade_B ,COUNT(1) FILTER (WHERE Marks BETWEEN 80 AND 100) AS TotalGrade_A FROM tbl_Students;   /* Result: -------------- --------------- --------------- --------------- TotalStudents |  TotalGrade_C |  TotalGrade_B |  TotalGrade_A --------------|---------------|---------------|---------------       12      |       4       |       3       |       5 -------------- --------------- --------------- --------------- */

SQL Server : Difference between Temp Table and Common Table Expression (CTE)

 

In this post, I am sharing the important difference between Temp Table and Common Table Expression (CTE) of the SQL Server.
I recently wrote an article on the difference between Temp Table and Table Variable of the SQL Server.




The theory and practice of Temp Table, Table Variable and CTE is one of my favourite topics of the SQL Server.

A CTE is used for a temporary result set that is defined within the execution scope of the query.
A Temp Table is also used for a temporary result set, but it can be defined for limited execution scope or can be used to define for global execution scope as a Global Temp Table.

A CTE is substituted for a view when the general use of a view is not required, and you do not have to store the definition of metadata.

CTE always uses memory and Temp Table stored in the TempDB.

The Scope of CTE is only for the query and can be referenced multiple times in other CTEs.
The child objects also access the Temp Table, and it persists until the end of the sessions.

We can not use NOLOCK and other isolation levels with CTE and with the Temp Table, we can apply it explicitly.

If we require to CTE for another batch query, it requires recreation of it.
The Temp Table can be used for all batches of a session.

A CTE doesn’t maintain any statistics and any metadata information, Temp table is maintaining a require statistics.

A CTE is a never part of the transaction and locking and its treated as system transaction.
The temp table operations are a part of user transactions.

If we have a large dataset, we should use Temp Table and with the small dataset, we can use CTE very effectively.

SQL Server: Difference between Temporary Table and Table Variable

 

Sharing one of the timeworn topics and we can also find an N number of good articles on this.

I also like this topic very much so I would like to write a small note on this.

First, Very important point:

Temp table is stored in TempDB and Table variable is stored in Memory.
The above statement is 100% wrong.
Table variable is also stored definition into TempDB and if the data volume is increased, sometimes it also stores data into TempDB.

For that reason, SQL Server 2014 introduced Hekaton storage engine for Memory optimized Table.
"SQL Server 2014: What is Hekaton?"
"SQL Server 2014: Create Memory Optimized File Group and Table"

If we require creating a unique index on data, we should use Temporary Table. We cannot apply Index on Table Variable.

If we are dealing with large number records for inserting and deleting, we should use a temporary table. If we have a small quantity of data, we should use Table Variable.

If the amount of data is frequently changing, query planner requires updated statistics to recompile object so for that we should use Temporary Table.
If the amount of data is not frequently changing, we should use Table Variable for better performance.

When we require rollback because of an outer user transaction, we should use Table Variable because it automatically discard all the data from the session.

If we would like to avoid locking issue, we should go with Table Variable because Temp Table is fully dependent on TempDB so sometimes it holds the transactions.

We can define explicit transaction on Temp Table where with the Table Variable we cannot define explicit transaction.

The maintenance and creation of metadata for Table Variable requires less time than Temp Table.

The scope of Table Variable is up to batch or stored procedure after completion of execution it drops automatically.
The scope of Temp Table is up to sessions, and once the session ends, it drops automatically, or we can also drop explicitly.

PostgreSQL: DELETE JOIN with an example

 

In this post, I am sharing a simple example of DELETE INNER JOIN statement in PostgreSQL.

Many of the database developers are exploring the PostgreSQL so DELETE a table from another table which is a very common requirement so I am sharing a simple example.

Create two sample tables with data:

1 2 3 4 5

CREATE TABLE ABC (ID INT, Name CHARACTER VARYING); CREATE TABLE XYZ (ID INT, Name CHARACTER VARYING);   INSERT INTO ABC VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'); INSERT INTO XYZ VALUES (1,'zzz'),(2,'yyy'),(3,'xxx'),(4,'www');

DELETE INNER JOIN:

1 2 3

DELETE FROM XYZ X USING ABC AS A WHERE A.ID = X.ID

Check the result:

1 2 3 4 5

SELECT *FROM XYZ;   id   |  name ---------------------- 4    |  www