Trending September 2023 # How Postgresql Common Table Expression Works? # Suggested October 2023 # Top 9 Popular

You are reading the article How Postgresql Common Table Expression Works? updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How Postgresql Common Table Expression Works?

Definition of PostgreSQL CTE

PostgreSQL CTE is an abbreviation for common table expressions used to simplify complex queries. The common table expression result is temporary, which we can include in the SELECT, INSERT, UPDATE, and DELETE, etc. SQL statements. The Common Table Expressions result is temporary, which means that the common table expression exists while executing the SQL statement only. We can use the common table expressions to increase the readability of the complex JOINs and SQL statements; we can also organize the complex JOINs and SQL statements in a human-readable and organized manner.

Start Your Free Data Science Course

Syntax:

Consider the following syntax of the PostgreSQL CTE:

WITH CTE_name (column_name_list) AS ( CTE_SQL_statement_definition ) statement;

Explanation:

CTE_name: We define the name to be given to the common table expression. The names of the columns follow the common table expression. The column name list is optional.

CTE_SQL_statement_definition: We define the SQL statement, which returns the result set inside the body of the WITH clause. The CTE_SQL_statement_definition becomes the names of the column list of the common table expression if we have not defined the column names after the names of the common table expression.

statement: This can be used as a view or table in the statement, which can be a SELECT, INSERT, UPDATE, or deletes SQL statements.

How CTE Works in PostgreSQL?

To define a WITH clause, follow the syntax section by specifying the name of the common table expression and then list the column names.

The WITH clause contains a body; if we have not defined the column name, it takes all columns from the SQL statement defined in the body of the WITH clause.

The Common Table Expressions result is temporary, which means that the common table expression exists while executing the SQL statement only.

Examples:

We will create two tables of the name ‘student’ and ‘teacher’ by using the PostgreSQL CREATE TABLE statement as follows to understand the examples:

create table student ( stud_id serial PRIMARY KEY, stud_fname VARCHAR(80) NOT NULL, stud_lname VARCHAR(80) NOT NULL, stud_marks INT NOT NULL, teach_id INT NOT NULL ); create table teacher ( teach_id serial PRIMARY KEY, teach_fname VARCHAR(80) NOT NULL, teach_lname VARCHAR(80) NOT NULL );

We will insert some data into the ‘teacher’ table by using the PostgreSQL INSERT INTO statement as follows:

INSERT INTO teacher(teach_fname, teach_lname) VALUES ('William','Joe'), ('Oliver','John'), ('Jack','Richard'), ('Harry','Joseph'), ('George','Thomas'), ('Brown','Charles');

Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

select * from teacher;

INSERT INTO student(stud_fname, stud_lname, stud_marks, teach_id) VALUES ('Smith','Johnson',67,1), ('Williams','Jones',42,1), ('Harper','James',54,2), ('Jack','Liam',58,2), ('Harry','Mason',62,3), ('Jacob','Oscar',69,3), ('Michael','Charlie',75,4), ('William','Joe',44,4), ('Oliver','John',53,5);

Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

select * from student;

1. Consider the following basic example, which will be used to retrieve all the records from the student table by using common table expression:

With CTE_student AS ( select stud_id, stud_fname, stud_lname, stud_marks, teach_id FROM student ) select * from CTE_student;

Illustrate the result of the above SQL statement by using the following snapshot.

2. Consider another example; here, we will first define a cte_student as a common table expression by using the WITH clause as defined below:

WITH cte_student AS ( SELECT stud_id, stud_fname, stud_lname, (CASE WHEN stud_marks <= 40 THEN 'Fail' THEN 'Third' THEN 'Second' THEN 'First' THEN 'Distinction' END) Grade FROM student ) SELECT stud_id, stud_fname, stud_lname, Grade FROM cte_student WHERE Grade = 'Distinction' ORDER BY stud_fname;

3. Consider one more example; we can use the JOIN with the column table expression as follows:

WITH cte_student AS ( SELECT stud_id, stud_fname, stud_lname, teach_id, (CASE WHEN stud_marks <= 40 THEN 'Fail' THEN 'Third' THEN 'Second' THEN 'First' THEN 'Distinction' END) Grade FROM student ) SELECT t.teach_id, teach_fname, teach_lname, Grade FROM teacher t INNER JOIN cte_student USING (teach_id);

Illustrate the result of the above SQL statement by using the following snapshot.

Conclusion

From the above article, we hope you understand how to use the PostgreSQL common table expression and how the PostgreSQL common table expression works. Also, we have added several examples of PostgreSQL common table expression to understand them in detail.