SQL Operation Order – Vlad Mihalcea

Introduction

On this article, we’re going to see what’s the customary SQL operation order.

When you perceive the order by which SQL operations are executed, then it will likely be clear why the Oracle legacy pagination question required a Derived Desk in an effort to guarantee that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause.

SQL Operations

The SQL customary defines many operations, reminiscent of:

• SELECT
• FROM
• JOIN
• GROUP BY and HAVING
• Window Capabilities (e.g., DENSE_RANK)
• UNION and UNION ALL
• ORDER BY
• FETCH FIRST/NEXT ROWS ONLY

However since SQL is a declarative language, the order of operations just isn’t the one we outlined, however the one which follows a logical circulate that’s much like processing Streams of knowledge utilizing a practical programming strategy.

SQL Operations Order

In an effort to see the order of operations in a given SQL question, you simply must examine its related Execution Plan.

For example, let’s take into account the next question that features the overwhelming majority of normal SQL Operations:

SELECT
    p.id AS post_id,
    p.title AS post_title,
    COUNT(laptop.*) AS comment_count,
    row_number() over () idx
FROM submit p
LEFT JOIN post_comment laptop ON p.id = laptop.post_id
WHERE p.title LIKE 'SQL%'
GROUP BY p.id, p.title
HAVING COUNT(laptop.*) > 1
UNION ALL
SELECT
    p.id AS post_id,
    p.title AS post_title,
    COUNT(laptop.*) AS comment_count,
    row_number() over () idx
FROM submit p
LEFT JOIN post_comment laptop ON p.id = laptop.post_id
WHERE p.title LIKE 'JPA%'
GROUP BY p.id, p.title
ORDER BY idx
OFFSET 1 ROWS
FETCH NEXT 5 ROWS ONLY

When extracting the Execution Plan with EXPLAIN ANALYZE, we get the next listing of operations that obtained executed:

Restrict  
(value=47.21..47.21 rows=1 width=536) 
(precise time=0.139..0.141 rows=5 loops=1)
  ->  Kind  
      (value=47.21..47.21 rows=2 width=536) 
      (precise time=0.138..0.139 rows=6 loops=1)
        Kind Key: (row_number() OVER (?))
        Kind Technique: quicksort  Reminiscence: 25kB
        ->  Append  
           (value=23.55..47.20 rows=2 width=536) 
           (precise time=0.075..0.131 rows=7 loops=1)
              ->  WindowAgg  
                  (value=23.55..23.59 rows=1 width=536) 
                  (precise time=0.075..0.079 rows=2 loops=1)
                    ->  GroupAggregate  
                        (value=23.55..23.57 rows=1 width=528) 
                        (precise time=0.069..0.072 rows=2 loops=1)
                          Group Key: p.id
                          Filter: (depend(laptop.*) > 1)
                          Rows Eliminated by Filter: 1
                          ->  Kind  
                              (value=23.55..23.56 rows=1 width=1068) 
                              (precise time=0.065..0.066 rows=6 loops=1)
                                Kind Key: p.id
                                Kind Technique: quicksort  Reminiscence: 25kB
                                ->  Hash Proper Be part of  
                                    (value=11.76..23.54 rows=1 width=1068) 
                                    (precise time=0.052..0.059 rows=6 loops=1)
                                      Hash Cond: (laptop.post_id = p.id)
                                      ->  Seq Scan on post_comment laptop  
                                          (value=0.00..11.40 rows=140 width=552) 
                                          (precise time=0.013..0.014 rows=6 loops=1)
                                      ->  Hash  
                                          (value=11.75..11.75 rows=1 width=520) 
                                          (precise time=0.027..0.027 rows=3 loops=1)
                                            Buckets: 1024  Batches: 1  Reminiscence Utilization: 9kB
                                            ->  Seq Scan on submit p  
                                                (value=0.00..11.75 rows=1 width=520) 
                                                (precise time=0.018..0.019 rows=3 loops=1)
                                                  Filter: ((title)::textual content ~~ 'SQL%'::textual content)
                                                  Rows Eliminated by Filter: 5
              ->  WindowAgg  
                  (value=23.55..23.58 rows=1 width=536) 
                  (precise time=0.046..0.051 rows=5 loops=1)
                    ->  GroupAggregate  
                        (value=23.55..23.57 rows=1 width=528) 
                        (precise time=0.045..0.047 rows=5 loops=1)
                          Group Key: p_1.id
                          ->  Kind  
                              (value=23.55..23.56 rows=1 width=1068) 
                              (precise time=0.043..0.044 rows=5 loops=1)
                                Kind Key: p_1.id
                                Kind Technique: quicksort  Reminiscence: 25kB
                                ->  Hash Proper Be part of  
                                    (value=11.76..23.54 rows=1 width=1068) 
                                    (precise time=0.036..0.038 rows=5 loops=1)
                                      Hash Cond: (pc_1.post_id = p_1.id)
                                      ->  Seq Scan on post_comment pc_1  
                                          (value=0.00..11.40 rows=140 width=552) 
                                          (precise time=0.010..0.011 rows=6 loops=1)
                                      ->  Hash  
                                          (value=11.75..11.75 rows=1 width=520) 
                                          (precise time=0.015..0.015 rows=5 loops=1)
                                            Buckets: 1024  Batches: 1  Reminiscence Utilization: 9kB
                                            ->  Seq Scan on submit p_1  
                                                (value=0.00..11.75 rows=1 width=520) 
                                                (precise time=0.013..0.014 rows=5 loops=1)
                                                  Filter: ((title)::textual content ~~ 'JPA%'::textual content)
                                                  Rows Eliminated by Filter: 3

To higher visualize the circulate of operations, we are able to use this on-line device, and we’ll get the next SQL Operation diagram:

So, based mostly on this diagram, we are able to see that the SQL Operation Order:

• FROM and JOIN
• WHERE
• GROUP BY
• ROLLUP, CUBE, GROUPING SETS
• HAVING
• OVER (e.g., Window Capabilities)
• SELECT
• DISTINCT
• UNION, INTERSECT, EXCEPT
• ORDER BY
• OFFSET
• FETCH FIRST/NEXT ROWS ONLY, LIMIT, TOP

That’s it!

If you happen to loved this text, I guess you’ll love my E-book and Video Programs as effectively.

And there’s extra!

You’ll be able to earn a major passive earnings stream from selling all these wonderful merchandise that I’ve been creating.

If you happen to’re eager about supplementing your earnings, then be a part of my associates program.

Conclusion

Understanding the SQL operation order not solely makes it simpler so that you can write SQL queries, however additionally, you will be higher ready when having to learn the related Execution Plan.

Associated