In this guide, we would look at various ways you can join and subquery data in mariadb. If you are new to mariadb, you can read the following guide to follow the progression right from the start.
- Introduction To MariaDB
- [MariaDB] Guide To Creating Databases and Tables
- Altering MariaDB Tables
- Indexes In MariaDB
- Guide To Understanding INSERT Statement (MariaDB)
- Guide To Selecting Data In MariaDB
- Guide To Updating and Deleting Data In MariaDB
Let's start with...
Union
UNION is used to combine the result from multiple SELECT statements into a single result set. The column names from the first SELECT statement are used as the column names for the whole results returned.
Before I show you an example, please note that UNION is used only with SELECT statement, and selected columns listed in corresponding positions of each SELECT statement should have the same data type, for example, the first column selected by the first statement should have the same type as the first column selected by the other statements
Consider I have the following table...
Table 1 - cmusic
SELECT * FROM cmusic;
+----------+-----------------+-------------------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+-----------------+-------------------------+----------+-------------+
| 1 | Avicii | Silhouette | 2 | 1 |
| 2 | Beautiful Nubia | Higher Steps | 3 | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 | 3 |
| 4 | Fela | Observation Is No Crime | 4 | 4 |
| 5 | Avicii | Levels | 2 | 1 |
| 6 | Fela | Colonial Mentality | 4 | 4 |
| 7 | Asa | Jailer | 1 | 2 |
+----------+-----------------+-------------------------+----------+-------------+
Table 2 - cmusic_genre
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
To count the number of times Avicii appeared in the cmusic table, the number of times Afrobeat appeared in the cmusic_genre table, and then unite them into one results set, you do:
SELECT
cmusic.artiste AS 'Artiste Name',
COUNT(*) AS 'Number of Appearance'
FROM cmusic
WHERE artiste = 'Avicii'
UNION
SELECT
cmusic_genre.genre_name,
COUNT(*)
FROM cmusic_genre
WHERE genre_name = 'Afrobeat';+--------------+----------------------+
| Artiste Name | Number of Appearance |
+--------------+----------------------+
| Avicii | 2 |
| Afrobeat | 1 |
+--------------+----------------------+
As stated before the column names from the first SELECT statement are used as the column names for the whole results returned.
Joining Tables
The JOIN clause can be used to link two tables together in a SELECT, UPDATE, or even DELETE statement. Before linking a table, you'll want to make sure the tables have something in common for purposes of selecting, updating, or deleting data.
The relationship of the tables might be an id, and from there, you can link them together to present a meaningful result altogether. You might have wondered why we have relationships in a table, well, if you don't have a relationship, you'll have to keep all the data in one giant table.
Before joining a table, suppose I have the following table:
Table 1 - cmusic
SELECT * FROM cmusic;
+----------+-----------------+-------------------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+-----------------+-------------------------+----------+-------------+
| 1 | Avicii | Silhouette | 2 | 1 |
| 2 | Beautiful Nubia | Higher Steps | 3 | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 | 3 |
| 4 | Fela | Observation Is No Crime | 4 | 4 |
| 5 | Avicii | Levels | 2 | 1 |
| 6 | Fela | Colonial Mentality | 4 | 4 |
| 7 | Asa | Jailer | 1 | 2 |
+----------+-----------------+-------------------------+----------+-------------+
Table 2 - cmusic_genre
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
If you look at the two table carefully, you can see that their relationship is defined by the genre_id in the both table. We can get the joined data from both tables by running the following query:
SELECT cmusic.genre_id, cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
JOIN cmusic_genre
WHERE cmusic.genre_id = cmusic_genre.genre_id;
+----------+-------------------+-----------------+-------------------------+
| genre_id | genre_name | artiste | title |
+----------+-------------------+-----------------+-------------------------+
| 1 | Indie pop | Asa | Jailer |
| 2 | Progressive House | Avicii | Silhouette |
| 2 | Progressive House | Avicii | Levels |
| 3 | Folk and Root | Beautiful Nubia | Higher Steps |
| 3 | Folk and Root | Beautiful Nubia | Tables Turn |
| 4 | Afrobeat | Fela | Observation Is No Crime |
| 4 | Afrobeat | Fela | Colonial Mentality |
+----------+-------------------+-----------------+-------------------------+
You don't even have to list the genre_id, we've already specified we are linking them with their genre_id relationship in the WHERE clause, so, you can remove it:
SELECT cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
JOIN cmusic_genre
WHERE cmusic.genre_id = cmusic_genre.genre_id;
+-------------------+-----------------+-------------------------+
| genre_name | artiste | title |
+-------------------+-----------------+-------------------------+
| Indie pop | Asa | Jailer |
| Progressive House | Avicii | Silhouette |
| Progressive House | Avicii | Levels |
| Folk and Root | Beautiful Nubia | Higher Steps |
| Folk and Root | Beautiful Nubia | Tables Turn |
| Afrobeat | Fela | Observation Is No Crime |
| Afrobeat | Fela | Colonial Mentality |
+-------------------+-----------------+-------------------------+
Another of achieving the above result is using the ON clause, here is how it works:
SELECT cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
JOIN cmusic_genre
ON (cmusic.genre_id = cmusic_genre.genre_id);
+-------------------+-----------------+-------------------------+
| genre_name | artiste | title |
+-------------------+-----------------+-------------------------+
| Indie pop | Asa | Jailer |
| Progressive House | Avicii | Silhouette |
| Progressive House | Avicii | Levels |
| Folk and Root | Beautiful Nubia | Higher Steps |
| Folk and Root | Beautiful Nubia | Tables Turn |
| Afrobeat | Fela | Observation Is No Crime |
| Afrobeat | Fela | Colonial Mentality |
+-------------------+-----------------+-------------------------+
This is the same example as before, but without the WHERE clause, we are using ON instead to indicate the joint point. This syntax allows you to specify the column names for join keys in both tables.
You'll notice that the column we are specifying In ON and WHERE clause are both identical (genre_id), the keyword USING together with the JOIN clause can allow us to specify the column name only once, here is an example:
SELECT cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
JOIN cmusic_genre
USING (genre_id);
+-------------------+-----------------+-------------------------+
| genre_name | artiste | title |
+-------------------+-----------------+-------------------------+
| Indie pop | Asa | Jailer |
| Progressive House | Avicii | Silhouette |
| Progressive House | Avicii | Levels |
| Folk and Root | Beautiful Nubia | Higher Steps |
| Folk and Root | Beautiful Nubia | Tables Turn |
| Afrobeat | Fela | Observation Is No Crime |
| Afrobeat | Fela | Colonial Mentality |
+-------------------+-----------------+-------------------------+
Use the USING clause if the column have the same name in both tables, and use ON clause if they do not have the same name.
Let's improve our example, by introducing the third table...
Table 3 - cmusic_producers
SELECT producer_id, producer_name FROM cmusic_producers;
+-------------+---------------+
| producer_id | producer_name |
+-------------+---------------+
| 1 | Avicii |
| 2 | Cobhams |
| 3 | Eni Obanke |
| 4 | Felas' Band |
+-------------+---------------+
To combine the three tables together, we would do:
SELECT cmusic_genre.genre_name, cmusic.artiste, cmusic.title, cmusic_producers.producer_name
FROM cmusic
JOIN cmusic_genre USING (genre_id)
JOIN cmusic_producers USING (producer_id);
+-------------------+-----------------+-------------------------+---------------+
| genre_name | artiste | title | producer_name |
+-------------------+-----------------+-------------------------+---------------+
| Progressive House | Avicii | Silhouette | Avicii |
| Progressive House | Avicii | Levels | Avicii |
| Indie pop | Asa | Jailer | Cobhams |
| Folk and Root | Beautiful Nubia | Higher Steps | Eni Obanke |
| Folk and Root | Beautiful Nubia | Tables Turn | Eni Obanke |
| Afrobeat | Fela | Observation Is No Crime | Felas' Band |
| Afrobeat | Fela | Colonial Mentality | Felas' Band |
+-------------------+-----------------+-------------------------+---------------+
We gave two JOIN clauses in this SQL statement. It doesn’t usually matter which table is listed where. You can also rename the column name by using the AS keyword followed by the alias name you wanna use, e.g:
SELECT
cmusic_genre.genre_name AS 'Genre Name',
cmusic.artiste AS 'Artiste',
cmusic.title AS 'Song Name',
cmusic_producers.producer_name AS 'Musical Producer'
FROM cmusic
JOIN cmusic_genre USING (genre_id)
JOIN cmusic_producers USING (producer_id);
+-------------------+-----------------+-------------------------+------------------+
| Genre Name | Artiste | Song Name | Musical Producer |
+-------------------+-----------------+-------------------------+------------------+
| Progressive House | Avicii | Silhouette | Avicii |
| Progressive House | Avicii | Levels | Avicii |
| Indie pop | Asa | Jailer | Cobhams |
| Folk and Root | Beautiful Nubia | Higher Steps | Eni Obanke |
| Folk and Root | Beautiful Nubia | Tables Turn | Eni Obanke |
| Afrobeat | Fela | Observation Is No Crime | Felas' Band |
| Afrobeat | Fela | Colonial Mentality | Felas' Band |
+-------------------+-----------------+-------------------------+------------------+
JOIN Types
There are different ways you can use JOIN depending on the way you want to handle data that doesn’t match the join condition. Let's see some example:
- INNER JOIN - This is the default, returns only the rows where matches were found. INNER JOIN is same as JOIN or CROSS JOIN
- LEFT JOIN- This returns matches and all rows from the left listed table
- RIGHT JOIN - This returns matches and all rows from the right listed table
We've already covered INNER JOIN, let's see.
LEFT JOIN
Let's go back to the example we used in the INNER JOIN section:
SELECT cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
JOIN cmusic_genre
USING (genre_id);
+-------------------+-----------------+-------------------------+
| genre_name | artiste | title |
+-------------------+-----------------+-------------------------+
| Indie pop | Asa | Jailer |
| Progressive House | Avicii | Silhouette |
| Progressive House | Avicii | Levels |
| Folk and Root | Beautiful Nubia | Higher Steps |
| Folk and Root | Beautiful Nubia | Tables Turn |
| Afrobeat | Fela | Observation Is No Crime |
| Afrobeat | Fela | Colonial Mentality |
+-------------------+-----------------+-------------------------+
Let's take a look at what we have in the actual genre table:
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
Let's say I delete genre_id 4: DELETE FROM cmusic_genre WHERE genre_id = 4;
Now, if I rerun the INNER JOIN or JOIN statement:
SELECT cmusic.music_id, cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
INNER JOIN cmusic_genre
USING (genre_id);
I'll get:
+----------+-------------------+-----------------+--------------+
| music_id | genre_name | artiste | title |
+----------+-------------------+-----------------+--------------+
| 1 | Progressive House | Avicii | Silhouette |
| 2 | Folk and Root | Beautiful Nubia | Higher Steps |
| 3 | Folk and Root | Beautiful Nubia | Tables Turn |
| 5 | Progressive House | Avicii | Levels |
| 7 | Indie pop | Asa | Jailer |
+----------+-------------------+-----------------+--------------+
As you can see music_id 4 and 6 are missing and this is because genre_id that is linked to the id 4 and 6 is genre_id 4, but there is no genre_id 4 anymore in the cmusic_genre table, so, it doesn't match.
But, when you use LEFT JOIN:
SELECT cmusic.music_id, cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
LEFT JOIN cmusic_genre
USING (genre_id);
+----------+-------------------+-----------------+-------------------------+
| music_id | genre_name | artiste | title |
+----------+-------------------+-----------------+-------------------------+
| 1 | Progressive House | Avicii | Silhouette |
| 2 | Folk and Root | Beautiful Nubia | Higher Steps |
| 3 | Folk and Root | Beautiful Nubia | Tables Turn |
| 4 | NULL | Fela | Observation Is No Crime |
| 5 | Progressive House | Avicii | Levels |
| 6 | NULL | Fela | Colonial Mentality |
| 7 | Indie pop | Asa | Jailer |
+----------+-------------------+-----------------+-------------------------+
It selects rows in the table on the left (i.e., cmusic) regardless of whether there is a matching row in the table on the right (i.e., cmusic_genre).
The NULL means there is no match on the right (in the cmusic_genre table).
So, to summarize LEFT JOIN:
The LEFT JOIN retrieves data starting from the left table. It compares each row from the left table (i.e the cmusic table) with every row from the right table (i.e the cmusic_genre table).
If the both row matches or cause the JOIN condition (e.g
USING (genre_id)) to evaluate to true, the LEFT JOIN creates a new row whose columns include columns of both rows specified by the select list.However, If the LEFT JOIN doesn’t find any matching row in the right table, it still creates a new row whose columns include columns of the row from the left table and null values for columns of the row from the right table.
RIGHT JOIN
This is similar to LEFT JOIN, but works in reverse. I'll use the same table as an example, to save you time scrolling to the top, here are the tables again...for cmusic_genre:
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
For the main table; cmusic:
SELECT * FROM cmusic;
+----------+-----------------+-------------------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+-----------------+-------------------------+----------+-------------+
| 1 | Avicii | Silhouette | 2 | 1 |
| 2 | Beautiful Nubia | Higher Steps | 3 | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 | 3 |
| 4 | Fela | Observation Is No Crime | 4 | 4 |
| 5 | Avicii | Levels | 2 | 1 |
| 6 | Fela | Colonial Mentality | 4 | 4 |
| 7 | Asa | Jailer | 1 | 2 |
+----------+-----------------+-------------------------+----------+-------------+
Now, I'll update the genre_id 3 to NULL:
UPDATE cmusic SET genre_id = NULL WHERE genre_id = 3;
Now, if you swap the cmusic table to the right and the cmusic_genre table to the left, we can use the RIGHT JOIN like so:
SELECT cmusic.music_id, cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic_genre
RIGHT JOIN cmusic
USING (genre_id);+----------+-------------------+-----------------+-------------------------+
| music_id | genre_name | artiste | title |
+----------+-------------------+-----------------+-------------------------+
| 1 | Progressive House | Avicii | Silhouette |
| 2 | NULL | Beautiful Nubia | Higher Steps |
| 3 | NULL | Beautiful Nubia | Tables Turn |
| 4 | Afrobeat | Fela | Observation Is No Crime |
| 5 | Progressive House | Avicii | Levels |
| 6 | Afrobeat | Fela | Colonial Mentality |
| 7 | Indie pop | Asa | Jailer |
+----------+-------------------+-----------------+-------------------------+
Now, here is the thing, if you swap the cmusic table to the left and the cmusic_genre table to the right, we are going to get the same result as the RIGHT JOIN clause:
SELECT cmusic.music_id, cmusic_genre.genre_name, cmusic.artiste, cmusic.title
FROM cmusic
LEFT JOIN cmusic_genre
USING (genre_id);
+----------+-------------------+-----------------+-------------------------+
| music_id | genre_name | artiste | title |
+----------+-------------------+-----------------+-------------------------+
| 1 | Progressive House | Avicii | Silhouette |
| 2 | NULL | Beautiful Nubia | Higher Steps |
| 3 | NULL | Beautiful Nubia | Tables Turn |
| 4 | Afrobeat | Fela | Observation Is No Crime |
| 5 | Progressive House | Avicii | Levels |
| 6 | Afrobeat | Fela | Colonial Mentality |
| 7 | Indie pop | Asa | Jailer |
+----------+-------------------+-----------------+-------------------------+
So, they don't really make much difference, it only depends on the position you want to place the table.
Subqueries
A subquery or nested query is a query within another query, a SELECT statement within another SQL statement e.g INSERT, UPDATE or DELETE.
A subquery can return a single data, a row of data, or even several columns from several rows. You can make queries easier to create when using a subquery, we would see an example in a moment.
Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query.
Note: A subquery is called an inner or nested query while the query that contains the subquery is called an outer query
Before using sub-query, here are what you need to know:
- A subquery must appear within parentheses.
- A subquery cannot be a UNION. Only a single SELECT statement is allowed
- Subqueries cannot manipulate their results internally, therefore the ORDER BY clause cannot be added into a subquery. However, you can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.
Let's see a simple example, consider I have the following table:
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | NULL | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | NULL | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
| 7 | 1 | NULL | Asa | Jailer | 1 | 2 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
If I want to query the music that has an id 1, 2, 3, 4, I'll normally do:
SELECT * FROM cmusic
WHERE music_id = 1 OR music_id = 2 OR music_id = 3 OR music_id = 4;This looks long, there are couple of ways we can make it better, and one example is using subquery:
SELECT * FROM cmusic WHERE music_id IN (1,2,3,4);
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | NULL | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | NULL | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
With the IN, we created a sub query, we used the result of the outside query or the main query (in our case, SELECT) to filter whatever we want.
Here is where things get a bit interesting, consider I have the following table:
+----------+-------------------+ | genre_id | genre_name | +----------+-------------------+ | 1 | Indie pop | | 2 | Progressive House | | 3 | Folk and Root | | 4 | Afrobeat | +----------+-------------------+
You should have noticed the genre_id of this table is related to the genre_Id of the first table, now see the following SQL statement:
Note: In the below SQL statement, you can use equals (=) instead of "IN", they'll give you the same result, I just prefer in this case.
SELECT * FROM cmusic
WHERE genre_id IN
(SELECT genre_id
FROM cmusic_genre);
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | NULL | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | NULL | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
| 7 | 1 | NULL | Asa | Jailer | 1 | 2 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
What did you notice? It returned the whole rows of the first table, which can also be done by using SELECT * FROM cmusic, we are simply saying:
SELECT all from cmusic where genre_Id IN the cmusic table is related to the genre_id from the cmusic_genre table, which made it return everything
It doesn't really make much difference from SELECT * FROM cmusicbut consider you don't want to keep track of the id of the genre since you can easily remember the genre name, and you want to query a track by its genre name, you can do so this way:
SELECT * FROM cmusic
WHERE genre_id IN
(SELECT genre_id FROM cmusic_genre
WHERE genre_name = 'Progressive House');
+----------+--------+-------+---------+------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+---------+------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
+----------+--------+-------+---------+------------+----------+-------------+-------------+
You see what I am saying, you don't need to keep track of the genre_id again:
We returned all the data with the outer query (SELECT * FROM cmusic), we then map the genre_id of the cmusic table to the genre_id of the cmusic_genre table, we then use the WHERE clause in the inner query to create a condition that as long as the genre_name is "Progressive House" returned the result, easy peasy.
You can even have multiple subqueries:
SELECT * FROM cmusic
WHERE genre_id IN
(SELECT genre_id FROM cmusic_genre
WHERE genre_name
IN ('Progressive House', 'Afrobeat')
);
+----------+--------+-------+---------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+---------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
+----------+--------+-------+---------+-------------------------+----------+-------------+-------------+
We have a sub-query in a sub-query, don't forget to use a closing parenthesis for the first sub-query.
Subqueries can be super simple and easy to construct. Note that, when working on a database that is very large and has a huge
amount of activity, subqueries may not be a good choice, this is understandable as they can be lots of round trips depending on how many subqueries you are using.
You can achieve anything a subquery can return with a JOIN clause, and they are faster than subquery when working a large data set, so, depending on the scenario, you should make a better judgment on what might do the job best.