Creating Tables Using the WITH Clause in SQL

1. Overview

In SQL, the WITH clause simplifies complex queries and enhances performance. It transforms how we interact with relational data. Whether calculating aggregates, managing hierarchical data, or creating tables, the WITH clause enables concise, maintainable, and efficient queries.

In this tutorial, we’ll explore how to create tables using the WITH clause in SQL Server, MySQL, and PostgreSQL. In addition, we’ll demonstrate this process using the Baeldung University database schema.

2. Why Create Table Using WITH Clause

We can use the SQL WITH clause or Common Table Expressions (CTEs) to create a temporary dataset that exists only during the query’s execution.

For example, when handling multiple nested queries or creating tables using the same dataset, the WITH clause allows us to define the temporary dataset once and reuse it as needed. This avoids repeating the same subquery multiple times.

Moreover, by creating tables with the WITH clause, we can improve readability, simplify complex SQL operations, and enhance performance. Additionally, we can specify the WITH clause within a single DELETE, SELECT, UPDATE or INSERT statement.

3. How to Create Tables Using the WITH Clause

Creating a table using the WITH clause in SQL Server and PostgreSQL is straightforward and similar. However, MySQL supports a comparable feature using a different syntax, as it doesn’t implement the WITH clause in the same way.

3.1. SQL Server

To create a table in SQL Server, we first define a CTE using the WITH keyword followed, by the temporary table name. After specifying the CTE, we reference it in the main query.

Suppose we have a Student table in the Baeldung University database with columns id, name, gpa, and graduation_date. Let’s create a new table that includes only students with a GPA greater than 3.5:

WITH HighGPAStudents 
AS 
(
    SELECT 
        id, 
        name, 
        gpa, 
        graduation_date
    FROM Student
    WHERE gpa > 3.5
)
SELECT *
INTO StudentsWithHighGPA
FROM HighGPAStudents;

Here, we first define a CTE called HighGPAStudents that filters the student data to only include those with a gpa column value higher than 3.5. Then, we create a new permanent table called StudentsWithHighGPA and populate it with the data from the CTE.

Let’s retrieve the contents of the newly created table:

SELECT * FROM StudentsWithHighGPA;
+------+-----------------+-----+-----------------+
| id   | name            | gpa | graduation_date |
|------+-----------------+-----+-----------------+
| 1001 | John Liu        | 4   | 2024-06-15      |
| 1003 | Rita Ora        | 4.2 | 2024-06-15      |
| 1007 | Philip Lose     | 3.8 | 2024-06-15      |
| 1010 | Samantha Prabhu | 4.9 | 2024-06-15      |
...

We can also create and populate a table separately instead of creating and populating it in a single step. To do that, first, we need to define the table structure using the CREATE TABLE statement:

CREATE TABLE StudentsWithHighGPA
(
    id INT,
    name VARCHAR(255),
    gpa FLOAT,
    graduation_date DATE
);

Next, we need to define the CTE using the WITH clause:

WITH HighGPAStudents (id, name, gpa, graduation_date) 
AS 
(
    SELECT 
        id, 
        name, 
        gpa, 
        graduation_date
    FROM Student
    WHERE gpa > 3.5
)

Finally, we take the rows from the CTE and insert them into our new table using the INSERT INTO statement:

INSERT INTO StudentsWithHighGPA
SELECT *
FROM HighGPAStudents;

Let’s display all the rows and columns from the new table:

SELECT * FROM StudentsWithHighGPA;

Here, a new table contains all students from the Student table with a gpa column value higher than 3.5.

3.2. MySQL

MySQL started supporting the WITH clause (CTEs) from version 8.0 and later. However, unlike SQL Server, it doesn’t allow the creation of a permanent table directly from a CTE. Instead, we first need to create the new table using the CREATE TABLE statement, and then define the CTE using the WITH clause and use it as a subquery to populate the new table.

To get started, let’s create a new table and define the CTE:

CREATE TABLE gpa_new AS
WITH new_high_gpa 
AS 
(
    SELECT 
        id, 
        name, 
        enrollment_date, 
        gpa
    FROM student
    WHERE gpa > 3.5
)
SELECT *
FROM new_high_gpa;

We can display the newly created table using the SELECT statement:

SELECT * FROM gpa_new;
+------+-----------------+------------------+------+
| id   | name            | enruollment_date | gpa  |
|------+-----------------+------------------+------+
| 1001 | John Liu        | 2020-01-15       |  4   |
| 1003 | Rita Ora        | 2020-01-15       | 4.2  |
| 1007 | Philip Lose     | 2020-01-15       | 3.8  |
| 1010 | Samantha Prabhu | 2020-01-15       | 4.9  |
| 1101 | Jia Grey        | 2020-01-15       | 3.98 |
...

The gpa_new table contains records for all students who achieved a GPA greater than 3.5.

3.3. PostgreSQL

The PostgreSQL relational database management system also supports the WITH clause, and the process is similar to that of the SQL Server.

First, we define the CTE or the temporary result using the WITH clause:

WITH HighGPAStudents 
AS 
(
    SELECT 
        id, 
        name, 
        gpa, 
        graduation_date
    FROM Student
    WHERE gpa > 3.5
)

Next, we use the CTE in our main query:

SELECT *
INTO StudentsWithHighGPA
FROM HighGPAStudents;

Here, the StudentsWithHighGPA table is created automatically by the SELECT INTO statement, and we don’t need to define its structure beforehand.

Let’s retrieve all the rows and columns from the newly created table:

SELECT * FROM StudentsWithHighGPA;
+------+-----------------+-----+-----------------+
| id   | name            | gpa | graduation_date |
|------+-----------------+-----+-----------------+
| 1001 | John Liu        | 4   | 2024-06-15      |
| 1003 | Rita Ora        | 4.2 | 2024-06-15      |
| 1007 | Philip Lose     | 3.8 | 2024-06-15      |
| 1010 | Samantha Prabhu | 4.9 | 2024-06-15      |
| 1617 | Philip Mohan    | 5   | 2025-06-15      |
...

Our new table contains the data for students with a GPA greater than 3.5.

4. Conclusion

In this article, we explored how to use the WITH clause to create tables in SQL, MySQL, and PostgreSQL.

While SQL Server and PostgreSQL allow creating tables directly with CTEs, MySQL requires a different approach to populating new tables. In addition, we can use the WITH clause to manage complex queries, ensure better performance, and reduce redundancy in SQL scripts.