SQL Guide: Queries, Joins, Schema Design, and Databases

SQL is the language that almost every relational database understands, and it is the one skill in this part of the site that outlasts every framework built above it. This page introduces SQL for someone who has never written a query, and it answers the choices that come first: whether SQL is still needed, SQL or NoSQL, and which database to start with.

What Is SQL, in Plain Words?

SQL, short for Structured Query Language, is the language used to define, read, change, and protect data in a relational database. A database is a set of connected ledgers: each table holds one kind of fact, and keys link related facts without copying the same details into every row. You describe the result you want, and the database decides how to fetch it.

One short query shows the shape of a read against stored orders.

SELECT id, customer_name
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

Read it as a sentence: choose these two columns, from the orders table, where the status is paid, newest first, up to ten rows. The linked guides develop each clause with complete examples.

What Is a Relational Database?

Four nouns carry almost every sentence on the pages below, and the guides assume you already have them.

A table holds one kind of fact and nothing else: customers in one, orders in another. A row is a single instance of that fact, and a column is one attribute every row of the table has. The discipline that makes the model work is keeping unrelated facts out of the same table, which is what schema design is about.

The fourth noun is what makes the model relational. A key is a column, or a set of columns taken together, whose value identifies a row, and a foreign key holds the key of a row in another table. Instead of copying a customer's name and address into every order, an order stores the customer's id, and the database refuses to store an id that does not exist. That refusal is the point, because the database enforces the relationship instead of relying on whoever wrote the last import script.

Everything else in SQL follows from those four ideas. Joins exist to follow keys back to the facts they point at. Indexes exist because finding rows by value gets expensive as tables grow. Transactions exist because one real-world action often changes several tables, and half of it is worse than none of it.

What Do People Use SQL For?

SQL has three main jobs, and most people meet it in the first one. Applications use SQL when several programs share the same customers, orders, or permissions: the web application reads and writes, a background worker updates, and an administration tool corrects. People also use SQL to ask questions of stored data, such as counts, totals, and comparisons for a report. The third job is moving and cleaning data, which covers imports, migrations, and one-off repairs.

The job	Who does it	Guides that own it
Application storage	Backend and web developers	Schema design and transactions
Reporting and analysis	Analysts, founders, and developers answering a question	Joins and window functions
Moving and cleaning data	Anyone holding a CSV file or an old database	CSV to SQL and UNION
Local and embedded data	Desktop apps, scripts, and test suites	SQLite

The reason this one skill carries across so many jobs is that the data model outlives everything built on it. A website gets rebuilt, a framework gets replaced, and an agent writes a new service, while the tables and the rules inside them stay in service. That is why the guides below spend so much time on schema design before they reach any clever query.

Do I Still Need SQL When ORMs and AI Write It for Me?

Yes, because someone still has to read what was written. An ORM, short for object-relational mapper, is a library that turns objects in your code into SQL on your behalf. The documentation for two popular ORMs says so in its own words. The Entity Framework Core documentation states that SQL queries are useful when the query you want cannot be expressed in its own query language, or when a generated query turns out to be inefficient. Django documents three separate ways to fall back to raw SQL when its model query tools do not go far enough.

Coding agents sharpen the same point for anyone using them. In the 2025 Stack Overflow survey, 84 percent of respondents used or planned to use AI tools, and 66 percent named solutions that are almost right, but not quite, as their biggest frustration. Almost right is the dangerous state for a query. A wrong join returns plausible numbers instead of an error, and my own rule on the window functions guide applies: a query that runs and returns a wrong number deserves more suspicion than one that fails.

The layer above	What it writes for you	What you still need to judge it
An ORM	Queries from objects and relations	Joins and indexes, to see why a page is slow
A coding agent	Queries and migrations from a plain request	Constraints, transactions, and whether the numbers are right
A dashboard tool	Reports from a visual builder	Grouping and filtering, to trust the totals

The classic ORM symptom is a page that issues one query for a list and then one more for every row in it. The page works in testing with ten rows and crawls with ten thousand, and the fix is usually a join or an eager-loading option. You only recognize the symptom by reading the SQL the library produced, which is a job for someone who can read SQL. My sense is that the ORM question is mostly a question about who will read that output when something goes wrong.

Access is the other half of the question. Some agent tooling now ships with a database tool, and Laravel's documentation for Laravel Boost lists a database query tool and a schema reader among the abilities it gives an agent. Whatever the product, the safe default is a database account that can only read. The PostgreSQL documentation explains that privileges are granted with GRANT and that, for most objects, only the owner or a superuser can do anything at first, so a read-only role takes a few lines to set up. Learning SQL is what lets you write those lines and review the agent's.

SQL or NoSQL?

NoSQL is a loose label for databases that do not use the relational table model, and the vague claim attached to it, "use NoSQL for scale", is too vague to design against. I would start relational whenever entities and relationships must stay valid, and reach for another model only when I can name the access pattern it serves. The MongoDB documentation gives that test for its own model: embed related data when entities have a contains relationship or a one-to-many relationship that is read in the context of the parent, and accept that the data gets duplicated.

Model	It stores	It fits when
Relational (SQL)	Tables, rows, and keys	Relationships must stay valid and reports must add up
Document	Self-contained JSON-like documents	Data is read together as one unit, and duplication is acceptable
Key-value	A value under a key, such as Redis strings, hashes, and lists	Fast lookups of caches, sessions, counters, and queues
Graph	Nodes and the links between them	The links between items are the thing you query

The choice is also less binary than it was. PostgreSQL documents a jsonb type that stores JSON in a binary form and supports indexing, and its documentation recommends jsonb for most applications. A relational database with a JSON column covers many cases that once pushed teams toward a document store. The SQL vs NoSQL comparison works through the trade-offs, the consistency questions, and using more than one data store.

Many real systems use more than one store, and the sensible pattern keeps one source of truth. The relational database holds the records that must stay correct, while a key-value store such as Redis holds a cache or a session that can be rebuilt. If losing the data would hurt, it belongs in the database with constraints.

Which Database Should a Beginner Start With: SQLite, PostgreSQL, or MySQL?

Learn SQL on SQLite, and start a new hosted application on PostgreSQL unless your host, framework, or existing application already runs MySQL or MariaDB. SQLite is a single file read through a library, with no separate server to install or secure, so the first hour goes into queries. The other three are database servers that applications connect to over a network, which is the shape of most hosted web work.

Engine	How it runs	Start with it when
SQLite	A file beside the application	You are learning, or one app or device owns the data
PostgreSQL	A database server	Constraints, JSON, or extensions carry real requirements
MySQL	A database server	Your host, framework, or existing app already targets it
MariaDB	A database server	The stack ships it, as WordPress hosting often does

The SQLite project is candid about its limits. It says that any site getting fewer than 100,000 hits a day should work fine on SQLite, and that a client-server database is better when many clients must write at once, because SQLite allows one writer at a time. The deciding factor is the shape of the deployment, and the MySQL vs PostgreSQL vs SQLite comparison walks through it.

What I like about all four is that they are open source, so I can read the license, run them on my own server, and keep the data there. The cost is that backups, upgrades, and tuning become mine, and a backup that has never been restored is only a hope. MySQL and MariaDB are close relatives and not interchangeable, which the MySQL vs MariaDB vs MaxDB comparison explains.

Will I Have to Relearn SQL When I Switch Databases?

Very little changes, and that is the main argument for learning it at all. Selections, filters, joins, inserts, updates, and deletes read almost the same on every engine, so the shop database you build in SQLite will move to PostgreSQL or MySQL with small edits. The differences gather at the edges: how generated keys are declared, how an upsert is written, which JSON and date functions exist, how text is compared and sorted, and which administrative commands manage users and backups.

The practical rule is to test on the exact engine and version you will deploy. A query that runs on MySQL can fail on PostgreSQL for reasons that have nothing to do with correctness, and a migration that passed on a laptop SQLite file can behave differently under a server's locking. Moving your practice database to a second engine is a cheap way to learn which of your habits were SQL and which were dialect. The comparisons hub lists where the engines diverge.

Who Uses Which Database, and Why?

The Stack Overflow Developer Survey gives the broad picture. PostgreSQL has been the most used database in the survey since 2023, and it moved from 45.55 percent of all respondents in 2023 to 55.6 percent in 2025. MySQL held close to 40 percent across the same three years, while SQLite climbed from 30.9 percent to 37.5 percent.

Engine	Survey usage, 2023 / 2024 / 2025	What its users say about it
PostgreSQL	45.6 / 48.7 / 55.6 percent	Notion described growing from one Postgres database to 96 sharded instances in a 2023 post
MySQL	41.1 / 40.3 / 40.5 percent	GitHub's 2023 post counts more than 1,200 MySQL hosts in over 50 clusters
SQLite	30.9 / 33.1 / 37.5 percent	The SQLite project lists every phone, browser, and Windows 10 or 11 installation among its homes
MariaDB	17.2 percent in 2024, 22.5 percent in 2025	WordPress recommends MariaDB 10.11 or later, or MySQL 8.0

Company engineering posts add the reasons behind these numbers. Shopify's engineering blog describes its platform as many pods, each an isolated instance with its own MySQL shard beside Redis and Memcached. Uber's 2016 post describes the opposite move, from Postgres to MySQL, and gives reasons tied to its own workload: write amplification, replication that consumed too much bandwidth between data centers, and upgrades that needed extensive downtime. Those reasons describe Uber at that date and that scale. A beginner's project sits a long way below it, so treat the post as a reminder that engines trade off, and not as a verdict on either one.

A named company choosing an engine is evidence about its workload and not about yours. The reliable pattern across all of these posts is that teams pick for operations, meaning backups, failover, upgrades, and the skills they already have, and then spend years on the data model. The data model outlives every engine choice, which is why the schema design guide matters more than the logo on the server.

Where Did SQL Come From, and Is It Going Away?

IBM's history of the relational database dates the idea to 1970, when Edgar F. Codd published "A Relational Model of Data for Large Shared Data Banks". IBM's System R project began in 1973 as an industrial-strength implementation, where Donald Chamberlin and Raymond Boyce developed SQL, and DB2 shipped in 1983. The language is therefore older than the web, and it has outlived every programming fashion since.

The engines have their own histories, which matter for different reasons. The PostgreSQL project traces its code to the POSTGRES package begun at Berkeley in 1986, and it says SQL support arrived in 1994 and the PostgreSQL name in 1996. The MySQL manual says the project began as an alternative to mSQL, and that it is named after Monty Widenius's daughter, My. The interview with Monty Widenius tells the MySQL story in his own words.

On whether SQL is going away, the survey says no. SQL was used by 48.66 percent of all respondents in 2023, 51 percent in 2024, and 58.6 percent in 2025. Among professional developers it reached 61.3 percent in 2025, which put it third behind JavaScript and HTML and CSS, ahead of Python. The history also sets one expectation: the language is standardized, but each engine has its own dialect. PostgreSQL states that it conforms to at least 170 of the 177 mandatory features for SQL:2023 Core as of version 18, and the word at least tells you the rest is engine-specific.

In What Order Does SQL Run a Query?

Here is the rule the rest of SQL assumes you already know, and it is not the order you write the clauses in. A query is written starting with the columns to show, but the language defines its meaning starting with the tables. The logical sequence is to assemble rows from the tables and their joins, discard rows with the filter, collapse what is left into groups, discard groups with the group filter, work out the output columns, sort, and cut to a row limit last of all.

That order describes what a query means, and an optimizer is free to do the physical work in any order that produces the same answer faster. Three common surprises follow directly from that sequence. A column alias created in the output step usually cannot be used in the row filter, because the filter runs first. The row filter and the group filter are not interchangeable, since one filters rows before grouping and the other filters groups after. A row limit of ten does not mean the database only did ten rows of work, because the limit runs after everything else.

Once this sequence is in your head, most questions about why SQL did something answer themselves, and query plans stop looking arbitrary. The indexes and query optimization guide shows how to read a real plan with EXPLAIN, and the joins guide shows the first step of the sequence at work.

How Hard Is SQL to Learn, and Do Agents Change That?

The first week is easy, since a query reads close to English and one small table is enough to practice on. The difficulty arrives later and has little to do with syntax: thinking in sets of rows instead of loops, designing tables so that impossible states cannot be stored, and reading a plan to see why something that was fast with test data is slow with real data. An agent can type any query for you, and typing was never the hard part.

The order below follows the questions a small shop database forces on you, because each one needs the next idea.

1. Schema design: create users, products, orders, and order items with real foreign keys rather than loose integer columns.
2. Joins: answer which orders belong to this customer, which needs more than one table.
3. Indexes and EXPLAIN: find out why that query got slow at ten thousand rows.
4. Transactions: take payment and reduce stock so that neither ever happens without the other.

After those four, the next steps depend on what you build. Each guide below answers one follow-up question, and none of them needs the others.

• Window functions: ranking and comparing rows inside a group, the usual next step for reports.
• UNION and UNION ALL: stacking result sets that share the same columns.
• CSV to SQL: bringing outside data in through a staging table.
• SQL interview questions: rehearsing the reasoning an interviewer probes.
• A second engine: moving the same shop database to PostgreSQL or MySQL to see which parts were dialect.

Agents change the cost of the first draft and leave the cost of review unchanged, which is the same bargain they offer in every language. The table lists what to hand over and what to keep.

Hand it to the agent	Keep for yourself
A first draft of a query from a plain-English request	Checking the result against a count you know is right
A draft migration that adds a column or table	Reading it for locks, rollback, and data loss before it runs
An explanation of a slow query	Running EXPLAIN on real data and trusting the plan
A script to import a file	The staging table and the validation before the real write

I learned query plans in the early 2000s on shared hosting, with nothing but a plain EXPLAIN for guidance, and that habit of asking the database what it actually did has aged better than any tool I used then. Treat an agent as a contractor: right project, narrow task, a read-only account, and a diff you read before it runs. The terminal AI agents guide covers those permission habits.

What Mistakes Do Beginners Make with SQL?

• Concatenating input into SQL: use prepared statements and bound values, as the PDO guide shows.
• Assuming row order: add an explicit ORDER BY with a stable tie-breaker.
• Skipping constraints: enforce stored rules in the database as well as the application, as the schema design guide explains.
• Adding speculative indexes: inspect a real query plan and include the write cost, as the indexes guide shows.
• Assuming full portability: run migrations and queries on the exact database engine and version.

Should Rules Live in the Database or in My Code?

Put every rule that all writers must obey in the database, and keep the friendly wording in the application. The writers are more than your web application: a migration, an import script, a background worker, a second service, or a person in a database client can all write to the same tables. A rule kept only in application code is a rule that only that application keeps.

The rule	Where it lives	Why
An order must point at a customer who exists	A foreign key in the database	Every writer is held to it, including the next import script
Two accounts cannot share an email address	A unique constraint, plus a friendly message in the application	Two requests arriving together can both pass an application check
Taking payment and reducing stock happen together	A transaction around both writes	Half of a real-world action is worse than none of it
The wording of an error message	The application	Only the application knows who is reading it

This is the position I defend most often, because the alternative fails slowly and out of sight. An application check is fine for the message, and the constraint is what stays true when somebody forgets the check. I think of the constraint as the cheaper insurance, since it costs one line of schema and covers every writer. The schema design guide covers keys and constraints, and the transactions guide covers the rule about writes that belong together.

Can I Run the Database Myself?

You can, and I would encourage it, as long as you secure it and keep backups you have restored at least once. A database you run yourself keeps the data on hardware you choose and keeps the configuration in readable files. The price is that patching, upgrades, backups, and failure at three in the morning are yours.

Option	You look after	The question to ask first
A SQLite file	Copying the file safely, and backups	Does only one application write to it?
A server you run	Users, patching, upgrades, backups, and restores	Who is on call when it fails?
A managed database service	Your schema and queries; the provider runs the server	Can I leave with a full dump if the terms change?

Whichever you choose, give each application its own restricted database user and keep the master credentials away from ordinary requests. Each engine documents its own backup route, including the SQL dump for PostgreSQL, the backup and recovery chapter for MySQL, mariadb-dump for MariaDB, and the backup API for SQLite. The engine guides linked below turn those routes into steps, and the DevOps hub covers the server skills around them.

What Can't SQL Do?

SQL describes what you want and leaves out how to get it. The query planner chooses the access path from statistics about the data, so the same query can get slower as a table grows without a line of it changing. Several engines offer hints to force an index or a join order, but they pin a decision to today's data and quietly rot as the data changes. Reading the query plan beats both guessing and overriding.

It also cannot protect you from a schema that permits bad data. A constraint the database does not enforce will eventually be violated by something: a migration, a background job, a second application, an afternoon in a database client. Schema design exists because rules kept only in application code are rules that only that application keeps.

The third boundary is the one that causes incidents. SQL cannot tell your values apart from your query text once they have been concatenated into one string. The remedy is a prepared statement that sends the query and the values separately, and it is covered in both the PDO guide and application security.

Which Guides Teach the Core of SQL?

These guides carry the ideas that every other SQL page assumes, listed in the order a small project usually needs them.

• SQL schema design answers how to give each fact one home, with keys, constraints, and safe migrations.
• SQL joins answers how to read across tables with inner and left joins, and how rows match or fail to match.
• SQL indexes and query optimization answers why a query is slow and how to read a real plan before adding an index.
• SQL transactions and ACID answers how several writes commit together, and what rollback, locks, and retries do.
• SQL UNION and UNION ALL answers how to stack compatible result sets, and when a union hides a schema problem.
• SQL window functions answers how to rank and compare rows within a group without collapsing them.
• SQL common table expressions answers how WITH and WITH RECURSIVE name a subquery and walk a hierarchy.
• Common SQL interview questions answers what an interviewer probes on joins, grouping, indexes, constraints, and transactions.

Which Guide Shows Me How to Do One Specific Thing?

• Import a CSV file into SQL: through a staging table and a validation step before the real write.
• Decide whether images belong in a database: or in object storage, weighed by size, atomicity, and recovery.
• Build database search with PHP and SQL: without concatenating input into queries.
• Combine result sets: with UNION and UNION ALL, without confusing unions with joins.
• Group related writes in a transaction: and handle rollback, locks, and retries.

Which Guides Help Me Choose and Run a Database?

Start with the SQL databases hub for where PostgreSQL, MySQL, SQLite, and MariaDB fit, and with the SQL comparisons hub, which separates workload decisions from popularity rankings.

• PostgreSQL: roles, JSONB, indexes, backups, and upgrades.
• MySQL: restricted users, InnoDB, backups, and major upgrades.
• SQLite: command-line inspection, WAL mode, backup, and migration.
• MariaDB: MySQL compatibility, storage engines, and configuration files.
• MySQL vs PostgreSQL vs SQLite: choosing by deployment shape, write concurrency, and recovery.
• MySQL vs MariaDB vs MaxDB: why these three are separate routes and not substitutes.
• SQL vs NoSQL: relational against document, key-value, and graph models.

Which Guides Connect SQL to an Application?

• PHP and MySQL with PDO answers how to bind values safely and handle database errors from PHP.
• PHP and MySQL applications connects the database ideas to a complete web request.
• C# frameworks covers Entity Framework Core, the ORM that sits between a .NET application and its database.
• Backend development places the database beside the other server-side choices.

Frequently Asked Questions

What should a beginner learn first in SQL?

Start with SELECT, FROM, WHERE, ORDER BY, and LIMIT on one small table. Then learn joins, grouping, keys, constraints, indexes, and transactions in that order. Each step adds one new way to read, connect, protect, or speed up stored facts.

Do all SQL databases use the same syntax?

They share common syntax for selections, filters, joins, inserts, updates, and deletes, but their dialects differ at the edges. Generated keys, upserts, JSON functions, date arithmetic, collations, and administrative commands must be tested on the exact engine and version.

Which SQL database should a beginner use?

SQLite is the shortest route to local practice because it needs no separate server. PostgreSQL, MySQL, or MariaDB is a better starting point when the goal is a hosted client-server application and the chosen framework or provider supports that engine.

Can application validation replace SQL constraints?

It cannot, because another script, worker, migration, or administration tool can write to the same database. Application validation can provide better messages, but use SQL constraints for rules every writer must obey, then use transactions when several related changes must succeed together.

What is the difference between a primary key and a foreign key?

A primary key identifies a row uniquely within its own table. A foreign key is a column that points at another table's primary key, and the database enforces that the target actually exists. One names a row, the other links to one.

Do you still need SQL if you use an ORM?

You do, because an ORM writes SQL for you and you will be the one reading it when a page is slow or a result is wrong. Understanding joins, indexes, and transactions is what lets you tell whether the generated query is reasonable.

What is NoSQL and when would you use it instead?

NoSQL covers databases that do not use the relational table model, such as document and key-value stores. They suit data that is read together as one unit or very high-volume access patterns. Choose relational when the data has clear relationships and you need constraints to protect them.

Where to Start

Create one small shop database in SQLite and query it: users, products, orders, and order items, with real foreign keys. Answer the four questions in the learning order above against it, because each one forces the next concept, and open schema design the first time the data itself fights you. Four questions on one small database teach more than four guides read in a row.