SQL for newbies (as a web developer)

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 *
from my_table

Select all data from table my_table

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

select t.id, t.name
from my_table t

Select the columns id and name from my_table using t as an alias for my table

It's common for us to filter data

select t.id, t.name
from my_table t
where t.category = 'my_category'

Filter on the category

Renaming columns

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

select
  t.id as "idTable",
  t.name as "nameTable"
from my_table t

Rename the fields id and name as idTable and nameTable

Join the tables

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

• When you join 2 tables and not want lose rows if some match doesn't exist on the second table, use a left join
• When you join 2 tables and want to filter rows doesn't have a match with second table, use inner join

Tip : When you build your query, start by left join and refine with inner join if necessary to avoid lose data

Credit

Left join

select
  t.id,
  t2.address
from my_table t
left join my_table_2 t2 on t.id = t2.table_id

We join the tables using the table_id column of my_table_2 that matches with the id column of my_table.

Using left join, no rows of my_table are lost but some fields of my_table_2 can be null.

Inner join

select
  t.id,
  t2.address
from my_table t
inner join my_table_2 t2 on t.id = t2.table_id

We join the tables using the table_id column of my_table_2 that matches with the id column of my_table

Using inner join, some rows of my_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 the as keyword to rename columns, it is very useful!

select
    json_build_object(
      'id', t.id,
      'name', t.name
    ) as my_json_object
from m_table t

On each row, we have the column my_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 with json_agg!

select
  json_agg(
    json_build_object(
      'id', t.id,
      'name', t.name
    ) as my_json_object
  ) as my_json_array
from m_table t

In the first (and only) row, in the column my_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.

select
    json_agg(
        json_build_object(
          'id', "source".id,
          'address', "source".address
        )
    )
from (
    select
        t.id,
        t2.address
    from my_table t
    left join my_table_2 t2 on t.id = t2.table_id
) as "source"
limit 5

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

Conclusion

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