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 a 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.

Real life JOIN using Alias

In real life, you may have some long column names or query specific ids, or do a multi join, so let’s see how it would look:

SELECT m.name, v.length, m.year, r.profit
FROM videos as v 
JOIN movies as m ON v.id = m.video_id
JOIN revenues as r ON r.id = m.id;

In this example, we want to see the movies their length, year of release and profit margins. To do so and make the SQL query more readable, we use:

  • A selection of the interesting fields in whichever order we want
  • The as keyword to rename the table as we join them
  • A double join to show information from three tables

As you can see, we don’t even need to query the field that we join on, and we can use the alias before it’s declared.

Other 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.