DEV Community

Advanced SQL: Mastering Query Optimization and Complex Joins

bilel salem on May 30, 2024

Hello everyone, السلام عليكم و رحمة الله و بركاته SQL (Structured Query Language) is an essential tool for managing and manipulating relational d...

Read full post

Andrew Atkinson • Jun 2 • Edited

Hi Bilel. Nice post! I’ve recently written a PostgreSQL book that covers advanced SQL, query planning, index design, and uses a Ruby on Rails app for examples and exercises.

After reading through your examples, I’ve realized we didn’t cover recursive CTEs (non recursive CTEs are covered) or lateral joins. I’ll save your post and link to as you introduce those topics nicely.

You did a great job of boiling down all of these topics into short descriptions with examples.

As you mentioned in Postgres we’ve got native materialized views that can be refreshed concurrently and indexed. In the extension ecosystem, there are even extensions to incrementally update materialized views.

Thanks for writing the post!

Andrew, author of High Performance PostgreSQL for Rails

bilel salem • Jun 3

You are welcome, Thank you .

Alvaro Figueiredo • Jun 12

Great article, I just learned a bunch of new techniques! I only missed a mention to the use case of ANALYZE statement to update the statistics about the dataset, allowing optimization of WHERE clauses.

craig mcinne • May 31 • Edited

Did you state a product anywhere or are you picking pure ANSI standard SQL ?
Looks like SQL Server to me.

bilel salem • Jun 1 • Edited

standard SQL, i put some notes for mysql about materialized views

iheb-sebai • May 31

Extremely helpful

Firas Latrach • May 30

Good job 👏

solixzsys • Jun 1

Thanks very much.
But are this sql construct portable across all major relational databases because you only took notes of MySQL?

bilel salem • Jun 1

i took note for mysql because it doesn't support directly materialized views but postgres for example supports materialized views . It's more general than specific db .