What is SQL?

SQL is the standard language for interacting with relational databases. It lets you create tables, insert data, query information, and manage database structures—all with a declarative syntax that describes what you want, not how to get it.

sql

SELECT name, email, created_at
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 10;

Despite being over 50 years old, SQL remains the most widely used database language. Every major database speaks it: PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and more.

The Four Operations (CRUD)

SQL operations map to the classic CRUD pattern:

Operation	SQL Command	Description
Create	`INSERT`	Add new rows
Read	`SELECT`	Query existing data
Update	`UPDATE`	Modify existing rows
Delete	`DELETE`	Remove rows

Basic Queries

SELECT - Reading Data

sql

-- Get all columns
SELECT * FROM products;

-- Get specific columns
SELECT name, price FROM products;

-- Filter with WHERE
SELECT * FROM products WHERE price > 100;

-- Sort results
SELECT * FROM products ORDER BY price DESC;

-- Limit results
SELECT * FROM products LIMIT 10;

INSERT - Adding Data

sql

-- Single row
INSERT INTO users (name, email)
VALUES ('Ada Lovelace', 'ada@example.com');

-- Multiple rows
INSERT INTO users (name, email)
VALUES
  ('Grace Hopper', 'grace@example.com'),
  ('Margaret Hamilton', 'margaret@example.com');

UPDATE - Modifying Data

sql

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

-- Update all rows (careful!)
UPDATE products
SET price = price * 1.1;

DELETE - Removing Data

sql

-- Delete specific rows
DELETE FROM sessions
WHERE expires_at < NOW();

-- Delete all rows (very careful!)
DELETE FROM temp_data;

JOINs - Combining Tables

JOINs let you query data across related tables:

sql

-- Get orders with customer names
SELECT
  orders.id,
  orders.total,
  customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Join Type	Returns
`INNER JOIN`	Only matching rows from both tables
`LEFT JOIN`	All rows from left table, matches from right
`RIGHT JOIN`	All rows from right table, matches from left
`FULL JOIN`	All rows from both tables

Aggregate Functions

sql

-- Count rows
SELECT COUNT(*) FROM orders;

-- Sum values
SELECT SUM(total) FROM orders WHERE status = 'completed';

-- Average
SELECT AVG(price) FROM products;

-- Group by category
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

Where You'll See This

Web applications - Almost every backend queries a database
Data analysis - Business intelligence and reporting
ETL pipelines - Extract, transform, load operations
ORMs - Sequelize, Prisma, SQLAlchemy generate SQL under the hood
Database migrations - Schema changes and data transformations

Common Gotchas

⚠️SQL Injection

Never concatenate user input directly into SQL queries. Use parameterized queries or prepared statements. "SELECT * FROM users WHERE id = " + userId is a security disaster.

ℹ️NULL Comparisons

NULL = NULL is NULL, not true. Use IS NULL and IS NOT NULL for null checks. This trips up everyone at least once.

UPDATE/DELETE without WHERE - Will affect ALL rows. Always double-check your WHERE clause.
Case sensitivity - SQL keywords are case-insensitive, but string comparisons may not be (depends on database).
Index usage - LIKE '%term' can't use indexes. LIKE 'term%' can.
N+1 queries - Querying in a loop instead of using JOINs kills performance.
Type coercion - WHERE id = '123' might not use the index on integer id.

SQL Dialects

While SQL is standardized, each database has its own extensions:

Feature	PostgreSQL	MySQL	SQLite
Auto-increment	`SERIAL`	`AUTO_INCREMENT`	`AUTOINCREMENT`
String concat	`\|\|`	`CONCAT()`	`\|\|`
JSON support	Native	Native	JSON extension
Full-text search	`tsvector`	`FULLTEXT`	FTS5
Upsert	`ON CONFLICT`	`ON DUPLICATE KEY`	`ON CONFLICT`

In Code

javascript

// Node.js with pg (PostgreSQL)
const { Pool } = require('pg');
const pool = new Pool();

// Parameterized query (safe from SQL injection)
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

python

# Python with psycopg2
import psycopg2

conn = psycopg2.connect(database="mydb")
cur = conn.cursor()

# Parameterized query
cur.execute(
    "SELECT * FROM users WHERE email = %s",
    (email,)
)
users = cur.fetchall()

Try It

Format SQL

"A SQL query walks into a bar, approaches two tables, and asks... 'Can I join you?'"