How To Learn SQL?

Apr 13, 2023

In this issue, we talk about the following:

How to Learn SQL?
What is the Difference Between Inner, Left, Right, and Full Join?
SQL Queries Run Order
What is Query Optimizer?
Top 20 SQL Query Optimization Techniques
Tools & Resources

So, let’s dive in.

How To Learn SQL?

Structured Query Language (SQL) is a domain-specific language used in programming and designed to manage data held in relational database management systems for many years. It was developed at IBM in the early 1970s. As relational databases are still very prevalent, any developer still needs to use them.

The SQL language has clauses, which are components of statements and queries; expressions that can produce scalar values or tables; predicates, which specify conditions that can be evaluated to SQL three-valued logic; and queries, which retrieve data based on criteria and other elements.

So, the question is how to learn SQL. Here are some (free) resources that I can recommend:

SQLBolt - is an entirely free, fully interactive introductory course. All SQL basics include writing queries, filtering, joins, aggregations, and creating, updating, and deleting tables.
SQLZoo - It provides both tutorials and exercises, which is why it is equally helpful for someone just starting with SQL and programmers who know SQL but want some good practice to master it.
SQL Tutorial at FreeCodeCamp - This SQL course has more than 7 million views, and I think it's YouTube’s most popular free SQL course.
PopSQL - is an exciting tool for collaborative SQL querying. It enables multiple users to share queries, store commonly used queries in a searchable library, and provide a visual interface for analysis.
Learning SQL by Alan Beaulieu is a free e-book. This book provides helpful context regarding the language's history and current usage, offers an overview of query and table architecture, and covers more complex SQL subjects than the abovementioned courses.
Learn it by playing a game - SQL Murder Mystery, SQL Island, SQL Police Department, SQL Noir, and Schemaverse.

The image below shows an overview of SQL Language.

If you’re starting to learn SQL, I would recommend the following learning path:

Beginner: SELECT, FROM, WHERE, LEFT/INNER JOIN, GROUP BY, ORDER BY.
Intermediate: HAVING, CASE WHEN, LIKE, IN, UNION (ALL), TOP/ LIMIT.
Advanced: WITH AS, PARTITION BY, OVER, LEAD, PIVOT, Stored Procedures, and Functions.

What is the Difference Between Inner, Left, Right, and Full Join?

(INNER) JOIN - return all rows that have matching values in both tables.
LEFT (OUTER) JOIN - return all rows from the left table and those that meet the condition from the right table.
RIGHT (OUTER) JOIN - return all rows from the right table and those that meet the condition from the left table.
FULL (OUTER) JOIN - return all rows with a match in either table.

How different types of SQL joins work (Credits: Hadley Wickham & Garrett Grolemund in their book “R for Data Science,” prepared as a cheatsheet by Andreas Martinson)

SQL Queries Execution Order

We utilize SQL queries to access a collection of records stored in our database tables. Clauses are the building blocks of SQL queries. These clauses must be executed to get the proper outcomes. SQL query execution order is the name given to this sequence of operations.

SQL query execution order refers to how the requirements evaluate the query clauses or how to optimize database search results. We use clauses in a specific order known as the SQL query execution order, similar to how we plan something step by step and arrive at the result.

Here is the order in which the SQL clauses are executed:

FROM - tables are joined to get the base data.
WHERE - the base data is filtered.
GROUP BY - the filtered base data is grouped.
HAVING - the grouped base data is filtered.
SELECT - the final data is returned.
ORDER BY - the final data is sorted.
LIMIT - the returned data is limited to row count.

What is Query Optimizer?

Its primary function is to determine the most efficient way to execute a given SQL query by finding the best execution plan. The query optimizer takes the SQL query as input and analyzes it to determine how best to execute it. The first step is to parse the SQL query and create a syntax tree. The optimizer then analyzes the syntax tree to determine how to execute the query.

Next, the optimizer generates alternative execution plans, which are different ways of executing the same query. Each execution plan specifies the order in which the tables should be accessed, the join methods, and any filtering or sorting operations. The optimizer then assigns a cost to each execution plan based on factors such as the number of disk reads and the CPU time required to execute the query.

Finally, the optimizer chooses the execution plan with the lowest cost as the optimal execution plan for the query. This plan is then used to execute the query.

Top 20 SQL Query Optimization Techniques

Here is the list of the top 20 SQL query optimization techniques I found noteworthy:

Create an index on huge tables (>1.000.000) rows.
Use EXIST() instead of COUNT() to find an element in the table.
SELECT fields instead of using SELECT *
Avoid Subqueries in WHERE Clause
Avoid SELECT DISTINCT where possible.
Use WHERE Clause instead of HAVING.
Create joins with INNER JOIN (not WHERE)
Use LIMIT to sample query results.
Use UNION ALL instead of UNION wherever possible.
Use UNION where instead of WHERE ... or ... query.
Run your query during off-peak hours.
Avoid using OR in join queries.
Choose GROUP BY over window functions.
Use derived and temporary tables.
Drop the index before loading bulk data.
Use materialized views instead of views.
Avoid != or <> (not equal) operator
Minimize the number of subqueries.
Use INNER join as little as possible when you can get the same output using LEFT/RIGHT join
For retrieving the same dataset, frequently try to use temporary sources.

👉 Learn more about best practices when writing SQL Queries.

⚠️ Note that these optimization techniques are a bit generic, and something else can work better in your particular case, as the DB engine does some optimizations under the hood. You can use the EXPLAIN command or with ANALYZE to ensure exactly what is happening. This command will tell you the plan but will not execute the query. However, with EXPLAIN ANALYZE, will run the query and tell you what happened. Be careful with this command, as it modifies the state.