How To Learn SQL?
Discover more from Tech World With Milan Newsletter
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
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 much prevalent, the need to use them is still present for any developer.
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 some 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 free e-book - This book provides some 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 courses mentioned above.
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 which 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.
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 determining 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 to use, and any filtering or sorting operations to be performed. 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.
Could you try to 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.
Tools & Resources
SQLFlow - a great tool to visualize SQL queries.
SQL Basics Cheat Sheet by LearnSQL.
How To Learn SQL?
Could you also write about NoSQL specifically MongoDB?
This is really great article