Skip to content

Note sql

All posts
Nov 12, 202610 min readsql · databases · postgresql · mysql · backendRead in:ENID

SQL Explained: How Databases Store and Query Your Data

SQL became an ANSI standard in 1986 and an ISO standard in 1987, and it still runs nearly every app. This guide covers tables, rows, and primary keys, the five core commands, how JOIN connects tables, and why an index made one measured query 1,500 times faster.

Close-up of a blue server rack glowing in a dark datacenter. Photo by panumas nikhomkhai on Pexels.

Every app you use is a thin layer over a database, and SQL is the language that layer speaks. When you log in, check a bank balance, or search for a product, an SQL query runs somewhere behind the scenes to fetch exactly the rows you asked for. SQL stands for Structured Query Language, and it is the standard language for accessing and manipulating databases. It became a standard of ANSI in 1986 and of ISO in 1987, which is why the same basic commands work in PostgreSQL, MySQL, Microsoft SQL Server, and SQLite nearly forty years later. (W3Schools)

This guide explains how a relational database stores data, what the core commands do, how tables connect to each other, and why indexes matter so much. No database experience needed.

Key Takeaways

SQL is the standard language for talking to relational databases. It became an ANSI standard in 1986 and an ISO standard in 1987, so skills transfer between database systems.

Data lives in tables made of rows and columns, and every row is one record. A primary key identifies each row uniquely.

Five commands cover most daily work: SELECT reads data, INSERT adds rows, UPDATE changes them, DELETE removes them, and WHERE filters which rows are affected.

JOIN combines rows from two or more tables through a related column, so data stays in one place instead of being copied everywhere.

An index works like a book index for a table. On a 500,000 row table, one measured query dropped from 91.502 ms to 0.058 ms with an index, roughly a 1,500x speedup.

SQL is still everywhere in 2026. In the Stack Overflow 2025 Developer Survey, 59 percent of developers reported using it.

What Is SQL, Really?

SQL is a query language, not a general purpose programming language. You do not write loops or build user interfaces in it. You describe the data you want, and the database figures out how to get it. Ask for all customers from Mexico and you get rows back, without telling the database how to scan its storage.

That declarative style is the whole point. The database engine plans the query, picks the fastest path, and returns the result. The same query can run faster after the database adds an index or rewrites its plan, and your SQL stays unchanged.

SQL works with relational database management systems, or RDBMS. The data in an RDBMS is stored in database objects called tables, and a table is a collection of related data entries made up of columns and rows. A table of customers, for example, has one row per customer and one column per attribute: name, contact, address, city, country. (W3Schools)

How a Relational Database Stores Data

Think of a table as a spreadsheet with strict rules. Every column has one data type, so a date column never holds a name, and every row has the same columns. The rules are what make a database more reliable than a folder of spreadsheets.

Three ideas hold the model together:

  • Tables. One table per kind of thing: customers, orders, products. Each table keeps only its own data.

  • Rows. One row is one record. A customers table with 91 records has 91 rows.

  • Primary keys. One column, or a combination of columns, identifies each row uniquely. An order has an OrderID, a customer has a CustomerID, and nothing else in the table shares that value.

Keeping each kind of data in its own table avoids duplication. A customer address is stored once, in the customers table, and every order just references the CustomerID. Change the address in one place and every order sees the new value. That single idea, store each fact once and reference it everywhere else, is what makes relational databases scale from a side project to a bank.

The Five Commands You Actually Need

Most SQL you will ever write uses five commands, plus WHERE to filter. Here they are, with the simplest form of each:

Aspect

Detail

Command

What it does

SELECT

Reads data from a table

INSERT

Adds new rows to a table

UPDATE

Changes existing rows

DELETE

Removes rows from a table

WHERE

Filters which rows a command touches

A typical query reads like a sentence: `SELECT name, city FROM customers WHERE country = 'Mexico';` It returns the name and city of every customer whose country is Mexico, and nothing else. Add a row with `INSERT INTO customers (name, city) VALUES ('Ana Trujillo', 'Mexico City');`, change one with `UPDATE customers SET city = 'Guadalajara' WHERE name = 'Ana Trujillo';`, and remove one with `DELETE FROM customers WHERE name = 'Ana Trujillo';`.

Notice how WHERE does the safety work. An UPDATE or DELETE without WHERE touches every row in the table, which is the classic beginner mistake. Seasoned developers read the WHERE clause twice before running either command on real data.

The official tutorials from the big database vendors teach the same verbs first. The PostgreSQL documentation tutorial introduces relational concepts and the SQL language with hands-on examples, assuming general computer knowledge and no particular Unix or programming experience. The MySQL 8.4 tutorial does the same through the `mysql` client program, showing how to create a database, connect, run queries, and view results. (PostgreSQL, MySQL)

How JOIN Connects Tables

A database keeps data in separate tables, so real questions usually span more than one. Which orders belong to which customers? What did each customer buy, and when? JOIN answers that by combining rows from two or more tables based on a related column. (W3Schools)

There are four types:

  • INNER JOIN. Returns only rows that match in both tables. An order with no customer, or a customer with no orders, is left out.

  • LEFT JOIN. Returns all rows from the left table, plus matching rows from the right. Unmatched rows get NULL where the other table has no data.

  • RIGHT JOIN. The mirror image: all rows from the right table, plus matches from the left.

  • FULL JOIN. Returns all rows from both tables, with NULL filling the gaps on either side.

A small worked example makes the difference concrete. Take a pets table with 5 rows and an owners table with 4 rows. An INNER JOIN returns the 4 matched rows. A LEFT JOIN adds Hopper, a pet with no owner, with NULL in the owner columns. A FULL JOIN returns 6 rows, including Hopper with a NULL owner and Louise, an owner with a NULL pet. (freeCodeCamp)

In practice, INNER JOIN and LEFT JOIN cover the vast majority of real queries. RIGHT JOIN is rare, and FULL JOIN is rarer still, because most questions are either "give me the matches" or "give me everything on this side, matches or not."

Why Indexes Make Queries Fast

Without an index, a database answers a query by scanning every row, checking each one against the WHERE clause. That works fine on a small table and gets slow fast as the table grows. An index is a separate lookup structure, like the index at the back of a book, that lets the database jump straight to the matching rows.

PostgreSQL uses a B-tree as its default index type. A B-tree has 3 levels, root node, internal nodes, and leaf nodes, and a traversal typically requires only 3 to 4 steps even for tables with millions of rows. (freeCodeCamp)

The measured difference is dramatic. On a 500,000 row customers table, a query without an index did a sequential scan in 91.502 ms and removed 499,999 rows by filter. The same query with an index on email did an index scan in 0.058 ms, roughly a 1,500x improvement. (freeCodeCamp)

Indexes are not free, though. Every INSERT, UPDATE, and DELETE has to update the index as well as the table, and each index takes disk space. The rule of thumb is simple: index the columns you filter and join on, and leave the rest alone.

SQL vs NoSQL: When to Pick Which

Not every database is relational, and SQL is not the only way to store data. Document stores like MongoDB keep flexible JSON-like records and suit data whose shape changes often. The November 2026 DB-Engines ranking, which tracks 442 database systems, puts Oracle first with a score of 1096.15, followed by MySQL at 823.23, Microsoft SQL Server at 693.53, and PostgreSQL at 689.69. MongoDB, the top non-relational system, sits fifth at 366.14. The top 4 are all relational systems. (DB-Engines)

Aspect

SQL databases

NoSQL databases

Data model

Tables with fixed columns

Documents, key-value, graphs

Schema

Defined up front, enforced

Flexible, changes per record

Best for

Orders, accounts, inventories

Logs, feeds, fast-changing content

Query language

SQL, standard since 1986

Varies by system

Examples

PostgreSQL, MySQL, SQLite

MongoDB, Redis, Cassandra

The practical answer for most projects is SQL first. If your data has clear relationships, like users, orders, and products, a relational database fits naturally. Reach for NoSQL when the data genuinely has no stable shape, or when you need a specialized model like a graph. Many real systems use both, SQL for the core records and something else for the parts that do not fit a table.

If you are building an API that sits on top of a database, the same thinking applies. Our guide to REST APIs explains how the web layer and the data layer talk to each other.

Why SQL Still Matters in 2026

New database ideas appear every year, but SQL keeps showing up in the numbers. The Stack Overflow 2025 Developer Survey collected over 49,000 responses from 177 countries, across 62 questions covering 314 different technologies, in its fifteenth year. SQL was used by 59 percent of respondents, behind only JavaScript at 66 percent and HTML/CSS at 62 percent. PostgreSQL ranked highest among database technologies for developers who want to use it next year, at 47 percent, and 66 percent of its current users want to keep using it, for the third year in a row. (Stack Overflow)

That staying power is not nostalgia. SQL survives because the problem it solves has not changed: apps need to store structured data, ask precise questions about it, and get reliable answers. The standard is old, the implementations keep improving, and the skill transfers from one database to the next with barely any relearning.

Frequently Asked Questions

Is SQL a programming language?

Not in the usual sense. SQL is a query language: you describe the data you want, and the database works out how to fetch it. It has no user interfaces, no loops in the everyday sense, and no general purpose application logic. That is why it pairs with a programming language like Python, JavaScript, or Java, which handles the app while SQL handles the data.

Do I need to learn SQL to become a developer?

Almost certainly yes. In the Stack Overflow 2025 Developer Survey, 59 percent of developers reported using SQL, making it one of the most widely used technologies in the industry. Backend, data, and full stack roles expect it, and even frontend developers run into it when an API needs a new field. The basics take a weekend; the fluency comes from real queries on real data.

What is the difference between SQL and MySQL?

SQL is the language, and MySQL is one database system that speaks it. The same distinction applies to PostgreSQL, Microsoft SQL Server, and SQLite: they are products, and SQL is the standard they implement. Learning SQL once lets you work with any of them, with only small dialect differences to pick up.

Which database should I start with?

PostgreSQL or MySQL are both good first choices. Both are free and open source, so you can install either one and start practicing in minutes. PostgreSQL is the most wanted database among developers, with 47 percent saying they want to use it next year, and its documentation is excellent for beginners. MySQL is the most widely deployed of the two and shows up in countless tutorials. Either way, the SQL you learn transfers to the other.

Conclusion

SQL earns its place because it is small, standard, and honest. A handful of commands covers nearly everything: SELECT to read, INSERT to add, UPDATE to change, DELETE to remove, WHERE to filter, and JOIN to connect tables. Indexes turn slow scans into instant lookups, and the relational model keeps each fact in exactly one place.

If you have never written a query, start with a small table of your own, ten rows of books, movies, or expenses, and ask it questions. The first time a JOIN pulls two tables together into one answer, the model clicks, and it never really unclicks.

Baca versi Bahasa Indonesia dari artikel ini di SQL Dijelaskan: Cara Database Menyimpan dan Mencari Datamu.

Tags: sql · databases · postgresql · mysql · backend

All posts