Context

Some time ago, as web developer, I had to use PostgreSQL and write some SQL queries to fetch some data.
I have a school level about SQL, so the first queries were difficult to write and I didn't find a memo about my use cases.

So this article acts as a "SQL memo" for web developer wondering to create basic SQL queries to fetch some data from SQL databases (especially PostgreSQL database, in my case).

Select data

The most basic query is the following:

select*frommy_table

Select all data from tablemy_table

We avoid fetching all data, we select the desired columns:

selectt.id,t.namefrommy_tablet

Select the columnsid andname frommy_table usingt as an alias for my table

It's common for us to filter data

selectt.id,t.namefrommy_tabletwheret.category='my_category'

Filter on thecategory

Renaming columns

To skip a mapping step during the fetch process, we can rename columns to match with our code base:

selectt.idas"idTable",t.nameas"nameTable"frommy_tablet

Rename the fieldsid andname asidTable andnameTable

Join the tables

Usually, we need to fetch data from multiple tables.
This section is not aSQL join course so keep in mind 2 things:

• When you join 2 tables andnot want lose rows if some match doesn't exist on the second table, use aleft join
• When you join 2 tables andwant to filter rows doesn't have a match with second table, useinner join

Tip : When you build your query, start byleft join and refine withinner join if necessary to avoid lose data

Credit

Left join

selectt.id,t2.addressfrommy_tabletleftjoinmy_table_2t2ont.id=t2.table_id

We join the tables using thetable_id column ofmy_table_2 that matches with theid column ofmy_table.

Usingleft join, no rows ofmy_table are lost but some fields ofmy_table_2 can benull.

Inner join

selectt.id,t2.addressfrommy_tabletinnerjoinmy_table_2t2ont.id=t2.table_id

We join the tables using thetable_id column ofmy_table_2 that matches with theid column ofmy_table

Usinginner join, some rows ofmy_table can be lost.

Build JSON object

As web developer, we like to use JSON objects. We can create the JSON using SQL to simplify the data manipulation.
Combined with theas keyword to rename columns, it is very useful!

selectjson_build_object('id',t.id,'name',t.name)asmy_json_objectfromm_tablet

On each row, we have the columnmy_json_object that contains JSON with the keys "id" and "name"

Create JSON array

Sometimes, we need to have arrays of JSON object. We can easily do it with SQL withjson_agg!

selectjson_agg(json_build_object('id',t.id,'name',t.name)asmy_json_object)asmy_json_arrayfromm_tablet

In the first (and only) row, in the columnmy_json_array, we have the wanted JSON array

Use "sub queries"

For complex queries, I can be useful to split big request into multiple one.
For example, a case of a query that aggregates multiple tables and another query based on the first one to do some logic to it.

selectjson_agg(json_build_object('id',"source".id,'address',"source".address))from(selectt.id,t2.addressfrommy_tabletleftjoinmy_table_2t2ont.id=t2.table_id)as"source"limit5

A "first select" fetch the data under "source" and the upper select format the data

Conclusion

This article is a smallmemo with only the most basic commands used in my use-cases in web development.
I hope it can help someone !