Have you ever found yourself opening a database application, typing “SELECT * FROM users;”, and feeling like you’re casting some kind of magical spell?
I’ve spent the past year learning SQL through trial and error, from error messages to baffling JOINs, and that embarrassing feeling of trying to use “WHERE COUNT(*) > 5” and realizing you don’t know why it doesn’t work. This is the explanation of SQL I wish somebody would have given me from day one.
This is not a “Learn SQL in 24 hours” sort of article. It is an honest guide on what SQL is, where it came from, and what aspects specifically give newbies trouble, based on scientific evidence and not guesswork.
What SQL Is (and What It Isn’t)
Structured Query Language, or SQL, is a programming language used for communicating with relational databases. This is the definition of SQL. The more truthful definition is that SQL is a declarative language. You state your intentions, and the database does the job. When you use the statement like SELECT name FROM users WHERE age > 18;, you don’t create a loop here. You don’t tell the computer to look at rows one by one. SQL doesn’t care about the “how” here.
It is very powerful, but it can also be confusing for beginners since we are accustomed to imperative programming languages, which contain step-by-step instructions.
Important caveat: While SQL is standardized, each of the big databases (MySQL, PostgreSQL, SQLite, SQL Server, Oracle) has its own unique flavor of SQL. The basic syntax is universal. The corner cases are not. Switch from MySQL to PostgreSQL and you will see what I mean.
A brief History (For Some Background)
SQL did not spring forth from nowhere and from a single individual.
In June 1970, Dr. E.F. Codd, a researcher at IBM, published a paper entitled “A Relational Model of Data for Large Shared Data Banks,” which appeared in Communications of the ACM. In his paper, Codd theorized about organizing data in terms of relations (tables), which would consist of rows and columns, which was not the norm then. This was just theory.
Following on from Codd’s work, IBM came up with a language to implement his ideas. They named the language SEQUEL (Structured English Query Language), but then they had to change it to SQL because of trademark reasons. Donald Chamberlain et al. of IBM Research defined SEQUEL in 1974, and it was implemented by IBM for the first time in an experimental product called SEQUEL-XRM.
In 1979, Relational Software Inc., now known as Oracle Corp., released the first SQL-based commercial database.
The time for standardization then arrived. The first SQL standard was published by ANSI in 1986, with ISO adopting it in 1987. Significant improvements were made, including the addition of referential integrity in SQL-89 and significant expansion of SQL-92, and the standard is still being developed through SQL:1999, SQL:2003, and up to the latest ISO/IEC 9075, which was published in 2023 but is now being reviewed for its revision.
What does it mean for you? When you hear “standard SQL,” it usually means agreed-upon features. When you use a particular database, you use both standard SQL and vendor extensions. That is not a bug; that is how the system works.
The Beginner Traps That Nobody Warns You About
This is where I stop citing documents and begin to reveal the actual problems.
In a 2024 study on misconceptions about SQL among students, some persistent errors were noted that are made even by those who have already been educated in their courses. These are not the “stupid mistakes.” This is a structure misunderstanding due to SQL syntax and semantics.
Trap 1: COUNT(column) vs. COUNT(*)
A typical beginner’s query may be like this:
sql
SELECT city, COUNT(city)
FROM stores
GROUP BY city;What would COUNT(city) yield? The number of non-null values in the city column for every group. If your city column does not have any NULLS, then you will get the row count. However, if you group by city and have NULL cities (which should never occur if city is the grouping field, but let’s be accurate), then NULL cities will be excluded from the count.
According to the study, there was a student who had grouped data using a particular column, and then he or she tried to count that particular column, thinking that it was the number of stores. Why did he or she do so? Well, he or she could not say since he or she had simply memorized the approach without comprehension.
What should you do? Use COUNT(*) if you want to count rows. And use COUNT(column) if you actually want to count non-null values of the particular column.
Trap 2: Aggregation Functions Belong in the SELECT or HAVING clause, not WHERE.
The below SQL statement is wrong:
sql
SELECT city, COUNT(*)
FROM stores
WHERE COUNT(*) > 5
GROUP BY city;This error is committed due to the fact that WHERE operates on row-wise before grouping. At this stage WHERE operates, there are no groups; hence, COUNT(*) has nothing to operate on. You require HAVING:
sql
SELECT city, COUNT(*)
FROM stores
GROUP BY city
HAVING COUNT(*) > 5;This is called an inconsistency misconception by the study since WHERE and HAVING look alike in the sense that they are both places for setting conditions, although WHERE is applied before JOINing while HAVING is after JOINing. The problem vanishes once you get used to remembering the order of operations, which is FROM → WHERE → GROUP BY → HAVING → SELECT.
Trap 3: JOIN Is Not An Equality Relation
JOIN is initially thought of by many students as the match between rows with columns A and B being equal. This is actually an INNER JOIN with an equality relation. But that’s not how JOINs should be defined.
CROSS JOIN returns a Cartesian product (i.e., all pairs) with no equality condition. LEFT JOIN returns all rows of the left-hand side without matching right-hand-side rows. FULL OUTER JOIN returns all rows of both sides without matching rows.
According to the study, sometimes students try to use the template “equate the keys” when they are supposed to give all possible combinations, which requires a CROSS JOIN with no equality. The main takeaway: first figure out what results you want, and then pick the type of JOIN.
Trap 4: Semicolons in Subqueries
This looks correct to many beginners:
sql
SELECT name
FROM products
WHERE price > (SELECT AVG(price) FROM products;);The semicolon inside the subquery is wrong in almost all database management systems. A semicolon marks the end of the whole statement. The subquery is part of the statement; therefore, there should be no semicolon at all.
Rule of thumb: One semicolon for one statement, only at the end.
Things That SQL Is Good At (and Not So Good At)
SQL shines when it comes to:
- Extracting structured data from relational tables.
- Data aggregation (summing, averaging, counting, grouping).
- Combining data from different related tables.
- Filtering based on specific criteria.
There are several shortcomings of SQL that are important to note beforehand:
- Scalability issues: Extremely complex analysis queries might be hard to write and optimize. This is not a problem of SQL per se but rather of the way we use it.
- Differences between vendors: Window functions, JSON processing capabilities, and date processing vary greatly depending on the DBMS. Make sure you always consult the specific database documentation.
- NULL value issues: NULL is not zero, NULL is not an empty string, and NULL does not equal anything, not even NULL. NULL = NULL will return NULL, not TRUE. Use IS NULL and IS NOT NULL.
SQL will not disappear either. For decades, it was the main language used for relational databases, and the ISO standard is constantly updated. It’s not a waste of time to learn it. However, to learn it properly, with all its flaws and inconsistencies, will save you many months of struggle.
A Useful First Move
If you are starting from scratch today, here is what you should do:
- Set up SQLite (a single executable file without a server) or use any SQL playground available online.
- Create a table consisting of five rows with some random data. Don’t create twelve tables right away.
- Execute SELECT * and see what happens.
- Add a WHERE condition. Make a deliberate mistake. See the error message.
- Add GROUP BY. Count one thing. Count another thing. See the difference.
The aim here is not to remember the syntax. The aim here is to develop a model of how the database understands your commands.
And whenever you encounter an error, you will pay close attention to the error message. Error messages in SQL are quite candid compared to error messages in other programming languages.
Sources and Further Reading
- Codd, E. F. “A Relational Model of Data for Large Shared Data Banks.” Communications of the ACM, June 1970. As cited by SQL documentation in Oracle.
- PostgreSQL Documentation: Chapter 59 “SQL”. The document that gives a detailed history of SEQUEL and the standardization of SQL.
- ISO/IEC 9075-1:2023, Information technology Database languages: SQL: Part 1: Framework. International Organization for Standardization
- PostgreSQL Documentation: “SQL Language” (JOIN types)
Conclusion
SQL is neither magic nor an impossible task. It is a declarative language with a real story behind it, from Codd’s relational model in 1970 until today’s revision of the ISO standard, and with quirks that practically everyone who starts learning SQL gets caught by at some point. These errors mentioned above (COUNT/COUNT(*), WHERE/HAVING, JOIN, and semicolons) are not just stupid mistakes. Published studies have shown that even course students make such structural errors. It might not protect you from them, but knowing about them beforehand would make their detection easier when your query doesn’t work.
What must be done is simple and humble: load up SQLite or get access to an online playground, create just one little table, and intentionally break your queries. Look at the errors. Try to form a picture of how the database is interpreting your language, not just learning it as rote syntax. SQL has been the lingua franca of relational databases for years and is an evolving standard, which means that time spent learning it will not be wasted provided that you learn it with all its limitations in mind.
Explore Our Programming Category. And if you are reading it up to here, leave a sweet comment to motivate us to write blog everyday.
