Some context before getting into it… SQL is a domain specific language used to manage data held in relational database management system (RDBMS). It is excellent for structured data.

Not to confuse with “noSQL Database” which refers to non-relational database. However, some noSQL database can use SQL like queries or be used in coordination with SQL managed databases (ie: the SQL databases for short).

Most of the examples are in sylhare/sql

Here we’ll be reviewing some generic SQL concepts, and queries that were tested specifically with MySQL hence the dolphin 🐬.

Types of JOIN

Definitions

Because it has multiple flavours, but the two first one are the main ones:

  • (INNER) JOIN: which is the default include from both tables when the condition is met.
  • LEFT (OUTER) JOIN: Includes the rows where the conditions are met plus all the row of the left table where the conditions are not met.
  • RIGHT (OUTER) JOIN: Not always supported. Same thing as the LEFT OUTER JOIN but includes the rows not met from the right table.
  • FULL (OUTER) JOIN: Not always supported. Takes here the condition is met for plus all remaining rows not met from left and right.

By default, when the condition is not match on the other table, its corresponding value will be NULL

Example

In the case where you have two tables, 🅰 and 🅱, and you want to link them:

SELECT * 
FROM A JOIN B ON A.id = B.id;

We have 🅰 to the left side of the JOIN word and 🅱 to the right side of it. In an INNER JOIN, the position does not matter since we take from both tables when condition is met only.

However, for the LEFT JOIN, that means we’ll take all the rows on the left side (ex: 🅰) of the join operator, and add the columns on the right side (ex: 🅱) when it matches the ON. Since all may not be matched, the values on the row that did not match will have NULL in the added column from the right side due to the join.

Useful Queries

Let’s have some quick references for useful queries.

Have a conditional column

When you want to return a value based on other columns or database. You can use the CASE keyword to conditionally create a column in your query output.

SELECT albums.name,
       CASE WHEN albums.release_year < 2021 THEN 'OLD' ELSE "NEW" END AS age,
       artists.name,
FROM albums
         JOIN artists ON albums.artist = artists.name;

Here you return from two tables artists and albums joined by the name and print the albums age which is going to be OLD or NEW depending on the value in albums.release_year

You can also do it with IF:

SELECT albums.name,
       IF(albums.release_year < 2021, 'OLD', "NEW") AS age,
       artists.name,
FROM albums
         JOIN artists ON albums.artist = artists.name;

Count the amount in SQL

Usually your client will return the number of rows in your query. In the case were you want to COUNT things in your query you can use it like:

SELECT COUNT(artists), NOW()
FROM albums
WHERE genre IN (
                'ROCK',
                'POP',
                'CLASSIC',
    );

Two things, use the IN to filter only the desired genre of music, then you can return the amount of artists with a timestamp given by NOW().

Select only unique values

When you want to avoid duplicates:

SELECT DISTINCT artist FROM albums;

This way you will only get the list of artist in the albums table. Or you could use GROUP BY to know the amount of album per artists:

SELECT COUNT(id), artist
FROM albums
GROUP BY artist
ORDER BY DESC;

This way you can see the most prolific to the least in terms of album created.