Despite its origins in the 1970s, SQL continues to be a vital skill for data science professionals, with demand for it projected to grow even more in the job market. Its enduring relevance can be attributed to its robust capabilities in handling big data, making it particularly effective for organizing and analyzing information.
Although SQL may not be as user-friendly as some modern tools, it excels as a versatile solution throughout the entire data lifecycle. From storing collected data in databases to cleaning, organizing, visualizing and modeling data.
SQL remains an indispensable tool in the field.
In this article we will explore 10 advanced features in SQL using MariaDB.
Maria DB Sample Database
The sample data can be found here. The nation database has 6 tables with information on countries, regions, languages, and statistics.
Database Client Tool
We will use database-client extension in VS Code.
1. Access data from multiple tables – Joins
Using joins to fetch associated records in a single query is one of the ways to solve the N+1 problem. This reduces the number of queries from N+1 to just 1, improving performance by minimizing database load and query execution time. There are many types of join, but our example will use an inner join to list all languages spoken in Bangladesh.
SELECT l.language as Languages
FROM countries c
INNER JOIN country_languages cl
ON c.country_id = cl.country_id
INNER JOIN languages l
ON cl.language_id = l.language_id
WHERE c.name = "Bangladesh" LIMIT 5
+-----------+
| Languages |
+-----------+
| Bengali |
| Chakma |
| Garo |
| Khasi |
| Marma |
+-----------+
5 rows in set (0.001 sec)2. Combines results of multiple queries – Unions
UNION is used to combine the results from multiple SELECT statements into a single result set. In this example, we list countries that speak French and countries that speak Italian in one result set.
SELECT c.name as country_name, l.language as country_language FROM (
SELECT cl.country_id AS cid, cl.language_id AS lid FROM country_languages AS cl
WHERE cl.language_id IN (SELECT l.language_id FROM languages AS l WHERE l.language = "Italian" )
UNION ALL
SELECT cl.country_id AS cid, cl.language_id AS lid FROM country_languages AS cl
WHERE cl.language_id IN (SELECT l.language_id FROM languages AS l WHERE l.language = "French" )
) a
LEFT JOIN countries c
ON c.country_id = a.cid
LEFT JOIN languages l
ON l.language_id = a.lid
ORDER BY c.name ASC LIMIT 5
+--------------+------------------+
| country_name | country_language |
+--------------+------------------+
| Andorra | French |
| Argentina | Italian |
| Australia | Italian |
| Belgium | French |
| Belgium | Italian |
+--------------+------------------+
5 rows in set (0.001 sec)
3. Perform multi-step operations – Subqueries
We may also rewrite the previous query using a subquery in the WHERE clause to fetch the country_id for “Bangladesh” before joining.
SELECT l.language AS Languages
FROM languages AS l
INNER JOIN country_languages AS cl
ON l.language_id = cl.language_id
WHERE cl.country_id = (
SELECT c.country_id
FROM countries AS c
WHERE c.name = 'Bangladesh'
) LIMIT 5
+-----------+
| Languages |
+-----------+
| Bengali |
| Chakma |
| Garo |
| Khasi |
| Marma |
+-----------+
5 rows in set (0.001 sec)4. Aggregation Functions
Aggregate functions are functions that are computed over a sequence of rows and return one result for the sequence of rows e.g. count,sum,max,min. You can also creating a custom aggregate function, see User defined functions section below.
Example showing countries that speak both French and Italian, using the count/having approach.
SELECT c.name as country_name, count(cl.language_id) AS langs
FROM country_languages AS cl
LEFT JOIN languages AS l
ON l.language_id = cl.language_id
LEFT JOIN countries AS c
ON c.country_id = cl.country_id
WHERE l.language in ("French","Italian")
GROUP BY cl.country_id ASC
HAVING langs > 1 LIMIT 5
+--------------+-------+
| country_name | langs |
+--------------+-------+
| Belgium | 2 |
| Canada | 2 |
| Switzerland | 2 |
| France | 2 |
| Italy | 2 |
+--------------+-------+
5 rows in set (0.002 sec)5. Common Table Expressions (CTEs)
Common Table Expressions (CTEs) are essentially temporary named result sets. They can also be recursive, see Recursive Queries section below.
In this example we list countries whose population is above the average population of all the countries in the country_stats table. i.e.30,041,784.
WITH country_metrics AS (
SELECT c.name as country_name, cs.population FROM country_stats cs
LEFT JOIN countries c
ON c.country_id = cs.country_id
)
SELECT cs1.country_name, cs1.population FROM country_metrics cs1
WHERE cs1.population > (
SELECT AVG(cs2.population) FROM country_metrics cs2
)
ORDER BY cs1.population ASC LIMIT 5
+--------------+------------+
| country_name | population |
+--------------+------------+
| Venezuela | 30045134 |
| Saudi Arabia | 30052518 |
| Iran | 30075297 |
| Peru | 30090359 |
| Morocco | 30115214 |
+--------------+------------+
5 rows in set (0.007 sec)
6. Recursive Queries
CTEs permit a query to reference itself. A recursive CTE will repeatedly execute subsets of the data until it obtains the complete result set. This makes it particularly useful for handing hierarchical or tree-structured data.
In the example below, we gather all countries in a specific continent and aggregate their GDP and population by language.
WITH RECURSIVE continent_countries AS (
-- Anchor member: Get all countries in a specific continent
SELECT
ct.name as continent_name,
c.name AS country_name,
l.language AS language_name,
cs.population,
cs.gdp,
r.continent_id,
c.country_id
FROM
countries c
JOIN
regions r ON c.region_id = r.region_id
JOIN
continents ct ON r.continent_id = ct.continent_id
JOIN
country_languages cl ON c.country_id = cl.country_id
JOIN
languages l ON cl.language_id = l.language_id
JOIN
country_stats cs ON c.country_id = cs.country_id
WHERE
ct.name = "Africa"
AND cl.official = 1 -- only use official languages
AND cs.year = 2017 -- focus on 2017 only
UNION ALL
-- Recursive member: Continue adding countries by continent
SELECT
cc.continent_name,
cc.country_name,
cc.language_name,
cc.population,
cc.gdp,
cc.continent_id,
cc.country_id
FROM
continent_countries cc
JOIN
continents c ON cc.continent_id = c.continent_id
)
-- Final query: Aggregate by language
SELECT
country_name,
language_name,
FORMAT(avg(gdp), 0) AS average_gdp,
FORMAT(avg(population), 0) AS average_population
FROM
continent_countries
GROUP BY language_name
ORDER BY population DESC
LIMIT 5
+--------------+---------------+-----------------+--------------------+
| country_name | language_name | average_gdp | average_population |
+--------------+---------------+-----------------+--------------------+
| South Africa | Afrikaans | 349,268,000,000 | 57,000,451 |
| South Africa | Xhosa | 349,268,000,000 | 57,000,451 |
| South Africa | Zulu | 349,268,000,000 | 57,000,451 |
| Tanzania | Swahili | 53,320,625,959 | 54,663,906 |
| Madagascar | Malagasy | 11,465,850,504 | 25,570,540 |
+--------------+---------------+-----------------+--------------------+
5 rows in set, 1 warning (0.153 sec)
7. Conditional Logic – Case
This works like an IF-THEN-ELSE statement. In this example, we categorize countries into three categories based on their GDP: “Low GDP“, “Medium GDP“, and “High GDP“.
SELECT
c.name AS country_name,
cs.gdp,
CASE
WHEN cs.gdp < 10000000000 THEN 'Low GDP'
WHEN cs.gdp BETWEEN 10000000000 AND 100000000000 THEN 'Medium GDP'
ELSE 'High GDP'
END AS gdp_category
FROM
countries c
JOIN
country_stats cs ON c.country_id = cs.country_id
WHERE
cs.year = 2017
LIMIT 5
+--------------+--------------+--------------+
| country_name | gdp | gdp_category |
+--------------+--------------+--------------+
| Aruba | 2700558659 | Low GDP |
| Afghanistan | 20191764940 | Medium GDP |
| Angola | 122124000000 | High GDP |
| Albania | 13025062196 | Medium GDP |
| Andorra | 3013387424 | Low GDP |
+--------------+--------------+--------------+
5 rows in set (0.001 sec)8. Reusable modules – User defined functions
We can convert the CASE statement above into a reusable function called categorize_gdp
DELIMITER //
CREATE FUNCTION categorize_gdp(gdp_value DECIMAL(15,0))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE gdp_category VARCHAR(20);
SET gdp_category = CASE
WHEN gdp_value < 10000000000 THEN 'Low GDP'
WHEN gdp_value BETWEEN 10000000000 AND 100000000000 THEN 'Medium GDP'
ELSE 'High GDP'
END;
RETURN gdp_category;
END //
DELIMITER ;We can the use the function categorize_gdp it in our query as follows.
SELECT
c.name AS country_name,
cs.gdp,
categorize_gdp(cs.gdp) AS gdp_category
FROM
countries c
JOIN
country_stats cs ON c.country_id = cs.country_id
WHERE
cs.year = 2017
LIMIT 5
+--------------+--------------+--------------+
| country_name | gdp | gdp_category |
+--------------+--------------+--------------+
| Aruba | 2700558659 | Low GDP |
| Afghanistan | 20191764940 | Medium GDP |
| Angola | 122124000000 | High GDP |
| Albania | 13025062196 | Medium GDP |
| Andorra | 3013387424 | Low GDP |
+--------------+--------------+--------------+
5 rows in set (0.001 sec)9. Temporary tables
Temporary tables can be an effective way to enhance the performance of complex or resource-heavy queries. By dividing a complex query into several steps and utilizing temporary tables, you can boost efficiency by minimizing the data processed at each step or by calculating intermediate/transient results in advance.
This example will analyze the GDP and population data for countries within a specific continent, Africa in this case, grouped by region and further analyzed by the official languages spoken in those regions.
-- Step 1: Temporary table to store country-level data
-- for a specific continent, Africa in this case
CREATE TEMPORARY TABLE temp_country_data AS
SELECT
c.country_id,
c.name AS country_name,
r.name AS region_name,
cs.gdp,
cs.population,
cl.language_id
FROM
countries c
JOIN
regions r ON c.region_id = r.region_id
JOIN
continents ct ON r.continent_id = ct.continent_id
JOIN
country_stats cs ON c.country_id = cs.country_id
JOIN
country_languages cl ON c.country_id = cl.country_id
WHERE
ct.name = 'Africa'
AND cs.year = 2017
AND cl.official = 1;
--
-- Step 2: Temporary table to aggregate data by region
CREATE TEMPORARY TABLE temp_region_data AS
SELECT
region_name,
AVG(gdp) AS total_gdp,
AVG(population) AS total_population
FROM
temp_country_data
GROUP BY
region_name;Lastly, join the 2 tables for the final result.
-- Step 3: Join both tables with the final results
SELECT
r.region_name,
l.language,
FORMAT(r.total_gdp, 0) AS tot_gdp,
FORMAT(r.total_population, 0) AS tot_pop,
COUNT(tcd.country_id) AS num_countries
FROM
temp_region_data r
JOIN
temp_country_data tcd ON r.region_name = tcd.region_name
JOIN
languages l ON tcd.language_id = l.language_id
GROUP BY
r.region_name
ORDER BY
r.total_gdp DESC
LIMIT 5
+-----------------+------------+-----------------+------------+---------------+
| region_name | language | tot_gdp | tot_pop | num_countries |
+-----------------+------------+-----------------+------------+---------------+
| Southern Africa | English | 200,951,742,154 | 33,329,912 | 7 |
| Northern Africa | Arabic | 118,959,012,907 | 38,706,775 | 6 |
| Central Africa | Arabic | 9,975,692,095 | 15,016,773 | 1 |
| Eastern Africa | French | 9,661,433,980 | 14,297,055 | 15 |
| Western Africa | Portuguese | 6,746,296,337 | 6,636,395 | 5 |
+-----------------+------------+-----------------+------------+---------------+
5 rows in set (0.001 sec)
10. Transactions
An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data.
Consider a scenario when updating the GDP and population for a country, and at the same time, inserting a new language for that country. If either operation fails, the database should roll back to its previous state to prevent data inconsistencies.
DELIMITER //
CREATE PROCEDURE update_country_stats_and_languages()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- On error, roll back the transaction
ROLLBACK;
END;
-- Start the transaction
START TRANSACTION;
-- Update operation
UPDATE country_stats
SET gdp = 200000000002,
population = 50000000
WHERE country_id = 1 AND year = 2017;
-- Insert operation
INSERT INTO country_languages (country_id, language_id, official)
VALUES (53, 9, 1);
-- Commit the transaction
COMMIT;
END //
DELIMITER ;Since MariaDB
-- With errors, no change made to the DB
> CALL update_country_stats_and_languages();
Query OK, 0 rows affected (0.009 sec)
-- If no errors occur, the transaction is committed, rows updated.
> CALL update_country_stats_and_languages();
Query OK, 2 rows affected (0.003 sec)11. Query Optimization Techniques
Covered in a separate post MariaDB Query Optimization Strategies.
Conclusion
The techniques we’ve explored here are just the tip of the iceberg when it comes to the power and versatility of SQL. Each concept offers a pathway to more efficient and sophisticated data handling, but there is always more to uncover with real-world data scenarios.