SQL Basics: From Zero to Writing Real Queries

SQL is 50 years old and still the most important query language you'll ever learn. Whether you're building web apps, analyzing data, or just trying to understand what's in a database, being comfortable with SQL will save you hours. The good news is that most of what you need day-to-day is a fairly small set of concepts.

Let's go from zero to writing real queries.

What SQL Is (and Why It's Still Relevant)

SQL — Structured Query Language — is a declarative language for working with relational databases. You describe what you want, not how to get it. The database engine figures out the execution plan.

A relational database stores data in tables: rows and columns, like a spreadsheet but with enforced structure and relationships. SQL lets you query, insert, update, and delete data, and combine information from multiple tables in one operation.

Every major relational database — PostgreSQL, MySQL, SQLite, SQL Server, Oracle — speaks SQL with minor dialect differences. We'll flag the important ones as we go.

SELECT: Getting Data Out

The most fundamental query is SELECT ... FROM ...:

SELECT name, email FROM users;

This returns every row from the users table, but only the name and email columns. To get all columns:

SELECT * FROM users;

SELECT * is fine for exploration, but in production code always name your columns explicitly. It makes queries resilient to schema changes and clearer to anyone reading them later.

WHERE: Filtering Rows

Add a WHERE clause to filter which rows you get back:

SELECT name, email FROM users WHERE active = true;

SELECT * FROM orders WHERE total > 100 AND status = 'paid';

You can combine conditions with AND, OR, and NOT. String matching uses LIKE with % as a wildcard:

SELECT * FROM products WHERE name LIKE 'Widget%';

ORDER BY and LIMIT

Sort results with ORDER BY:

SELECT name, created_at FROM users ORDER BY created_at DESC;

DESC for descending (newest first), ASC (the default) for ascending. Use LIMIT to cap how many rows you get back:

SELECT * FROM orders ORDER BY total DESC LIMIT 10;

This gives you the 10 highest-value orders — a pattern you'll use constantly.

Aggregates: Counting and Summarizing

Aggregate functions collapse multiple rows into a single value:

SELECT COUNT(*) FROM users;            -- total user count
SELECT SUM(total) FROM orders;         -- total revenue
SELECT AVG(total) FROM orders;         -- average order value
SELECT MAX(total), MIN(total) FROM orders;

GROUP BY

GROUP BY lets you apply aggregates to groups of rows:

SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;

This returns one row per distinct status value with the count of orders in each group.

HAVING

WHERE filters rows before aggregation. HAVING filters after:

SELECT customer_id, SUM(total) as revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500;

This returns only customers whose total order value exceeds 500. You can't do that with WHERE because the aggregate doesn't exist yet when WHERE runs.

JOINs: Combining Tables

JOINs are where SQL gets powerful. If your orders table stores a user_id but you also want the user's name, you join the two tables together.

INNER JOIN

Returns only rows that have a match in both tables:

SELECT users.name, orders.total, orders.created_at
FROM orders
INNER JOIN users ON orders.user_id = users.id;

If an order has no matching user, it won't appear. If a user has no orders, they won't appear either.

LEFT JOIN

Returns all rows from the left table, with matching rows from the right table — and NULL where there's no match:

SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name;

This lists every user and their order count, including users who've never placed an order (they get 0 after COUNT).

RIGHT JOIN

The mirror image of LEFT JOIN — all rows from the right table. In practice, most developers rewrite RIGHT JOIN as a LEFT JOIN with the tables swapped, because it's easier to read.

A simple way to remember it: the "outer" side of the join is the one that can contribute NULL rows. LEFT means the left table is outer.

Modifying Data

INSERT

INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

You can insert multiple rows in one statement:

INSERT INTO tags (name) VALUES ('javascript'), ('typescript'), ('node');

UPDATE

UPDATE users SET active = false WHERE last_login < '2024-01-01';

Always use a WHERE clause with UPDATE. Without one, you update every row in the table. Most SQL clients will warn you, but not all do.

DELETE

DELETE FROM sessions WHERE expires_at < NOW();

Same rule: always scope your DELETE with WHERE. A bare DELETE FROM users is catastrophic in production.

Subqueries

A subquery is a query nested inside another query — useful for composing operations:

SELECT name, email FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE total > 1000
);

This returns users who have at least one order over 1000. You could also do this with a JOIN, and in most cases the JOIN version is faster — but subqueries are often easier to read when you're first composing a query.

Common Beginner Mistakes

Not using aliases. Once you're JOINing multiple tables, column names can collide. Use table.column or AS aliases.

Forgetting NULL handling. WHERE age = NULL doesn't work. NULL comparisons require IS NULL and IS NOT NULL. This trips up almost everyone at first.

Assuming ORDER BY without LIMIT is free. Sorting a million rows you don't need is expensive. If you only want the top 10, say so.

Using SELECT * in production joins. When you join two tables that both have an id column, SELECT * returns both. Explicit column lists avoid the ambiguity.

Dialect Differences

The SQL you write for PostgreSQL is close to what works in MySQL, but not identical. Key differences to watch for:

• String concatenation: || in PostgreSQL/SQLite, CONCAT() function in MySQL
• Date/time functions: NOW() works in both PostgreSQL and MySQL; SQLite uses datetime('now')
• Boolean literals: true/false in PostgreSQL; 1/0 in MySQL and SQLite
• LIMIT vs TOP: SQLite/PostgreSQL/MySQL use LIMIT; SQL Server uses SELECT TOP n
• Full-text search: Completely different implementations between engines

For learning purposes, SQLite is the easiest to get started with — it's a single file, no server needed. PostgreSQL is the best choice when you want to graduate to production work.

You can format and pretty-print your SQL queries using our SQL Formatter tool, which makes long queries much easier to read and review. If you're working with data that came from a spreadsheet, CSV to JSON can help you reshape it before importing.

For a deeper dive into how databases actually execute your queries, check out Understanding Database Indexes — it's essential reading once your tables grow past a few thousand rows.

SQL is one of those skills that compounds. The more you write it, the more naturally you think in sets and relations. Start with simple SELECT queries, add JOINs when you're comfortable, and you'll be writing useful real-world queries within a day of practice.