How Multiple Named Subqueries Work Behined The Scenes?

The first question
How multiple named subqueries using WITH clause work in sql especially when those subqueries build on each other? Does sql store each table(subquery) or something?

The second question
How can i test each subquery while they build on each other?

Screen Link:

For Example :

    usa AS
        SELECT * FROM customer
        WHERE country = "USA"
    last_name_g AS
         SELECT * FROM usa
         WHERE last_name LIKE "G%"
    state_ca AS
        SELECT * FROM last_name_g
        WHERE state = "CA"

FROM state_ca

Thanks in advance.

Hi @gehadeltabakh

  1. No. the WITH clause is a type of common table expression (CTE). In simple terms, its a temporary structure only created when the main (parent/external) query is run. So it doesn’t create a data structure in database.
    Think of it like clipboard, you can copy-paste the contents but they are not stored anywhere except for RAM.

Tables form actual row-column structures with indexes in a database and Views also have their properties/ definitions stored in a database. Their underlying query is execute when you use a view in a simple select query.

  1. I don’t fully understand your question. So if this is not what you are looking for please elaborate.
    When you provide the main query, just simply select from the nested query. that should give you results of the nested query.
    For example:
    WITH sub_query_name1 AS ( ..... ), sub_query_name2 AS ( ..... ), ....... SELECT * FROM sub_query_name1;

Edit: Sorry for the query format, I have no idea why it’s coming like that.

1 Like

So the subqueries within the WITH clause have no action, i can’t run them as with the subquery. And if i want to run any subquery within the WITH clause i should do this through the main query.
Am i right?.

Hi @gehadeltabakh
apologies for delayed response. I am not sure I understand this right…

See WITH clause is like how we can better organize a complex query with lot of sub-queries involved. At times we may need to repeat our subqueries. So, ofcourse these sub-queries have action and fetch records.

Say, you want to connect 5 tables - [A, B, C, D and E]. you are not gonna start directly with a WITH clause are you? You will first try to identify the best connections available within these 5 tables. those are your sub-queries. While trying to get the final desired results, you realize that you have to utilize same sub-query connecting C table and E table multiple times.

So instead of repeating the same sub-query multiple times, you define them under the WITH clause. Once you have internal connections between these 5 tables, think -

  • step1: Join A to B
  • step2: Join C to D
  • step3: Join AB combo with E
  • step next: and so on…
    some StepN: Join C to E and and Join A to D are often repeated for whatever reasons.

This is where WITH clause comes handy. you can bag these sub-queries under one roof and then make your main query utilize and give you the desired results.

They do have action, it’s just that they don’t store any fetched results like how a table or view would normally do. They fetch the results and it’s like temporarily stored.

Some resources you can checkout:

Edit: please let me know if this still hasn’t helped you much.

Thank you @Rucha for the detailed answer and also the resources.
Now everything became clear.

1 Like