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
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
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.
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
albums joined by the name and print the albums age which is going to
be OLD or NEW depending on the value in
You can also do it with
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
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.