Cheatsheet SQL e MySQL

SELECT * FROM clients;

-- Specific tables in a JOIN
SELECT c.*, p.total
FROM clients c
JOIN orders p ON p.client_id = c.id;

SELECT * returns all columns. In production, prefer listing the columns you need to reduce traffic and avoid exposing sensitive data.

-- MySQL
SELECT CONCAT(name, ' ', surname) AS complete
FROM clients;

-- PostgreSQL / standard SQL
SELECT name || ' ' || surname AS complete
FROM clients;

-- With a formatting function
SELECT CONCAT('€', FORMAT(price, 2)) FROM products;

CONCAT() joins strings (MySQL). The || operator does the same in PostgreSQL/SQLite. If any value is NULL, the result is NULL — use COALESCE to avoid it.

-- Customers with orders
SELECT name FROM clients c
WHERE EXISTS (
    SELECT 1 FROM orders p
    WHERE p.client_id = c.id
);

-- Customers without orders
SELECT name FROM clients c
WHERE NOT EXISTS (
    SELECT 1 FROM orders p
    WHERE p.client_id = c.id
);

EXISTS checks whether the subquery returns at least one row. It is more efficient than IN for large tables because it stops at the first match. NOT EXISTS inverts the logic.

SELECT name, email, city
FROM clients;

-- With a column alias
SELECT name AS client,
       email AS contact
FROM clients;

Select only AS columns you need — faster and clearer. AS renames the column in the result (alias). The alias is optional: SELECT name client also works.

SELECT price * quantity AS total
FROM items;

SELECT price * (1 - discount/100) AS price_final
FROM products;

SELECT SUM(price * quantity) AS total_order
FROM items WHERE order_id = 42;

You can compute values directly in the query with +, -, *, / and % (module). Combine with AS to name the result.

SELECT name,
    CASE
        WHEN total > 1000 THEN 'VIP'
        WHEN total > 100 THEN 'Regular'
        ELSE 'New'
    END AS segmento
FROM clients;

-- Simple CASE (direct comparison)
SELECT status,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
    END AS state
FROM contas;

CASE adds conditional logic to the SELECT. The searched form uses WHEN condition; the simple form compares a value. ELSE is optional (without it, it returns NULL).

SELECT c.name, p.total
FROM clients c
JOIN orders p ON p.client_id = c.id;

-- Alias with AS (optional)
SELECT c.name FROM clients AS c;

A table alias shortens long names and is essential in JOINs and self JOINs. Once defined, use the alias in all references to the table within that query.

SELECT name,
       (SELECT COUNT(*) FROM orders p
        WHERE p.client_id = c.id) AS num_orders
FROM clients c;

-- Scalar subquery
SELECT name,
       (SELECT MAX(total) FROM orders) AS largest_order
FROM clients;

A subquery in the SELECT must return a single value (scalar). It runs for each row of the outer query. For better performance, prefer JOIN when possible.

-- Removes duplicate rows:
SELECT DISTINCT city
FROM clients;

-- On several columns (unique combinations):
SELECT DISTINCT city, country
FROM clients;

-- Count distinct values:
SELECT COUNT(DISTINCT city)
FROM clients;

-- DISTINCT applies to ALL
-- the SELECT columns

DISTINCT removes duplicates from the result. It applies to the set of all selected columns. COUNT(DISTINCT col) counts unique values. It has a sorting cost — use it only when needed.

SELECT DISTINCT city FROM clients;

-- Multiple columns
SELECT DISTINCT city, country FROM clients;

-- Count distinct values
SELECT COUNT(DISTINCT city) FROM clients;

DISTINCT removes duplicate rows from the result. With multiple columns, the entire combination must be unique. It can be used inside COUNT() to count unique values.

-- Combine results (no duplicates)
SELECT name FROM clients
UNION
SELECT name FROM suppliers;

-- Keep duplicates (faster)
SELECT city FROM clients
UNION ALL
SELECT city FROM suppliers;

UNION combines the results of two queries, removing duplicates. UNION ALL keeps them all (faster). Both queries must have the same number of columns and compatible types.

-- Table and column alias:
SELECT c.name AS client,
       p.total AS value
FROM clients AS c
JOIN orders AS p ON p.client_id = c.id;

-- Subquery in the FROM (derived table):
SELECT average.city, average.total
FROM (
  SELECT city, AVG(total) AS total
  FROM orders
  GROUP BY city
) AS average
WHERE average.total > 100;

Aliases (AS) shorten table and column names. A subquery in the FROM (derived table) lets you filter aggregated results. The subquery alias is mandatory.

SELECT * FROM products WHERE price > 100;

SELECT * FROM clients WHERE active = 1;

SELECT * FROM orders
WHERE data >= '2024-01-01';

WHERE filters rows before any aggregation. It accepts comparisons with =, >, <, >=, <=, <> (or !=). Strings in single quotes.

-- Starts with
SELECT * FROM clients WHERE name LIKE 'Anna%';

-- Ends with
SELECT * FROM clients WHERE email LIKE '%@gmail.com';

-- Contains
SELECT * FROM products WHERE name LIKE '%phone%';

-- Any single character
SELECT * FROM clients WHERE name LIKE 'A_a';

LIKE matches by patterns. % represents any sequence (0 or more characters) and _ represents exactly one character. Case sensitivity depends on the COLLATE.

-- Filter by part of the date
SELECT * FROM orders
WHERE YEAR(data) = 2024;

-- Filter by string length
SELECT * FROM clients
WHERE LENGTH(name) > 20;

-- Warning: functions on the column prevent indexes
-- Bad:  WHERE YEAR(data) = 2024
-- Good: WHERE data >= '2024-01-01' AND data < '2025-01-01'

You can use functions in the WHERE, but that prevents the use of indexes on the column (full scan). Prefer direct comparisons with ranges to keep performance.

SELECT * FROM products
WHERE category = 'electronic'
  AND price < 500;

-- OR with parentheses (precedence)
SELECT * FROM clients
WHERE (city = 'Lisbon' OR city = 'Porto')
  AND active = 1;

AND requires all conditions; OR requires at least one. Use parentheses to control precedence — without them, AND takes priority over OR.

-- Find null values
SELECT * FROM clients WHERE phone IS NULL;

-- Exclude nulls
SELECT * FROM clients WHERE email IS NOT NULL;

-- ERROR: never use = NULL
-- WHERE phone = NULL  ← does not work!

To check for NULL, always use IS NULL or IS NOT NULL. The = NULL operator never works because NULL means "unknown" and any comparison with it returns UNKNOWN.

-- Greater than any value in the subquery
SELECT * FROM products
WHERE price > ANY (SELECT price FROM promotions);

-- Greater than all values
SELECT * FROM products
WHERE price > ALL (SELECT price FROM promotions);

-- SOME is a synonym for ANY
WHERE stock > SOME (SELECT minimum FROM alerts);

ANY (or SOME) returns true if the comparison is true for at least one value. ALL requires it to be true for all of them. Used with subqueries that return a single column.

SELECT * FROM products
WHERE price BETWEEN 10 AND 100;

-- Dates
SELECT * FROM orders
WHERE data BETWEEN '2024-01-01' AND '2024-12-31';

-- Negation
WHERE price NOT BETWEEN 50 AND 200;

BETWEEN checks whether the value is in the range (inclusive on both ends). It works with numbers, dates and strings. It is equivalent to >= X AND <= Y.

WHERE NOT city = 'Lisbon';
WHERE NOT active;

-- Combined
WHERE NOT (price > 100 AND stock > 0);

-- Equivalent to !=
WHERE city != 'Lisbon';

NOT negates any condition. It can be used with BETWEEN, IN, LIKE, EXISTS. In many cases, != or <> are equivalent and more readable.

SELECT * FROM clients
WHERE city IN ('Lisbon', 'Porto', 'Braga');

-- With a subquery
SELECT * FROM orders
WHERE client_id IN (
    SELECT id FROM clients WHERE active = 1
);

-- Negation
WHERE status NOT IN ('cancelado', 'devolvido');

IN checks whether the value belongs to a list. More readable than multiple ORs. It can contain a subquery. NOT IN inverts it — beware of NULL in the subquery (it returns empty).

-- MySQL: <=> operator
SELECT * FROM clients
WHERE phone <=> NULL;  -- true if it is NULL

-- COALESCE for comparison
WHERE COALESCE(phone, '') = '';

-- PostgreSQL: IS NOT DISTINCT FROM
WHERE phone IS NOT DISTINCT FROM NULL;

Normal comparisons fail with NULL. The <=> operator (MySQL) treats NULL the a comparable value. COALESCE replaces NULL with a default value before comparing.

-- Ascending (default)
SELECT * FROM products ORDER BY price ASC;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- By several columns
SELECT * FROM clients
ORDER BY city ASC, name DESC;

ORDER BY sorts the result. ASC is the default (omittable). With multiple columns, it sorts by the first and breaks ties by the second. It accepts aliases: ORDER BY total.

-- Standard SQL (PostgreSQL, Oracle, SQL Server)
SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

-- With offset
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- SQL Server
SELECT TOP 10 * FROM products ORDER BY price DESC;

FETCH FIRST is the standard alternative to LIMIT (supported in PostgreSQL, Oracle, DB2). SQL Server uses TOP. MySQL uses LIMIT. They all do the same: restrict rows.

-- Sort by a calculation
SELECT name, price * stock AS value
FROM products
ORDER BY price * stock DESC;

-- Sort by column position
SELECT name, email, city
FROM clients ORDER BY 3;

-- NULLs first/last (PostgreSQL)
ORDER BY phone NULLS FIRST;

You can sort by expressions, alias or column position (1-based). In PostgreSQL, NULLS FIRST/NULLS LAST controls where nulls go. In MySQL, NULL comes first in ASC.

-- MySQL
SELECT * FROM products ORDER BY RAND() LIMIT 5;

-- PostgreSQL
SELECT * FROM products ORDER BY RANDOM() LIMIT 5;

-- SQL Server
SELECT TOP 5 * FROM products ORDER BY NEWID();

To select random rows, each DBMS has its own function: RAND() (MySQL), RANDOM() (PostgreSQL), NEWID() (SQL Server). Slow on large tables — consider TABLESAMPLE.

-- First 10
SELECT * FROM products LIMIT 10;

-- Pagination: page 3 (10 per page)
SELECT * FROM products
ORDER BY id LIMIT 10 OFFSET 20;

-- Alternative syntax (MySQL)
SELECT * FROM products LIMIT 20, 10;

LIMIT restricts the number of rows. OFFSET skips N rows before returning. Essential for pagination. The LIMIT offset, count syntax is specific to MySQL.

SELECT columns          -- 5. projection
FROM table              -- 1. source
WHERE condition         -- 2. row filter
GROUP BY column         -- 3. grouping
HAVING group_condition  -- 4. group filter
ORDER BY column         -- 6. ordering
LIMIT n OFFSET m;       -- 7. final restriction

The logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why you cannot use a SELECT alias in the WHERE.

-- Top 3 products per category (MySQL 8+)
SELECT * FROM (
    SELECT name, category, price,
        ROW_NUMBER() OVER (
            PARTITION BY category ORDER BY price DESC
        ) AS pos
    FROM products
) ranked
WHERE pos <= 3;

To get the top N per group, use ROW_NUMBER() with PARTITION BY (window function). The subquery numbers as rows within each group; the outer filter selects the first N.

SELECT p.name AS product,
       c.name AS category
FROM products p
INNER JOIN categories c
    ON p.category_id = c.id;

-- JOIN is a synonym for INNER JOIN
SELECT * FROM a JOIN b ON a.id = b.a_id;

INNER JOIN returns only rows with a match in both tables. Unmatched rows are excluded. The word INNER is optional: JOIN alone does the same.

-- Hierarchy: employee → manager
SELECT e.name AS employee,
       c.name AS boss
FROM employees e
JOIN employees c ON e.boss_id = c.id;

-- LEFT to include those without a manager
SELECT e.name, c.name AS boss
FROM employees e
LEFT JOIN employees c ON e.boss_id = c.id;

A self JOIN joins a table to itself using different aliases. Essential for hierarchies (parent-child), trees and comparisons between rows of the same table.

-- PostgreSQL: correlated subquery in the FROM
SELECT c.name, top.total
FROM clients c
JOIN LATERAL (
    SELECT total FROM orders p
    WHERE p.client_id = c.id
    ORDER BY total DESC LIMIT 3
) top ON true;

-- MySQL 8.0.14+
SELECT c.name, t.total
FROM clients c
JOIN LATERAL (
    SELECT total FROM orders
    WHERE client_id = c.id LIMIT 3
) t ON true;

LATERAL JOIN lets the subquery in the FROM reference columns from earlier tables. Ideal for "top N per group" without window functions. The ON true is required in PostgreSQL.

SELECT c.name, p.total
FROM clients c
LEFT JOIN orders p ON c.id = p.client_id;

-- Filter only those that do NOT have a match
SELECT c.name
FROM clients c
LEFT JOIN orders p ON c.id = p.client_id
WHERE p.id IS NULL;

LEFT JOIN returns all rows from the left table, even without a match (right-side columns become NULL). The WHERE p.id IS NULL trick finds orphan records.

-- Cartesian product: all combinations
SELECT c.name AS color, t.name AS size
FROM colors c
CROSS JOIN sizes t;

-- Implicit equivalent (without ON)
SELECT * FROM colors, sizes;

-- Useful: generate dates × products
SELECT d.data, p.name
FROM datas d CROSS JOIN products p;

CROSS JOIN generates the Cartesian product: each row of A combined with each row of B. Useful for generating matrices (dates × products, colors × sizes). Careful: N×M rows can be huge.

-- JOIN of AS table with itself:
-- (employees with the manager's name)
SELECT e.name AS employee,
       c.name AS boss
FROM employees e
LEFT JOIN employees c
  ON e.boss_id = c.id;

-- Different aliases (e, c) are
-- required to distinguish
-- the two "copies" of the table

SELF JOIN links a table to itself — essential for hierarchies (manager/employee, parent/child categories). Use different aliases to distinguish the two references to the same table.

-- RIGHT: all rows from the right table
SELECT c.name, p.total
FROM orders p
RIGHT JOIN clients c ON p.client_id = c.id;

-- FULL OUTER: all from both (PostgreSQL/Oracle)
SELECT *
FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- MySQL has no FULL OUTER — use UNION
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns everything from both tables. MySQL does not support FULL OUTER — simulate it with a UNION of LEFT and RIGHT.

-- When the column has the same name
SELECT * FROM orders
JOIN clients USING (client_id);

-- vs ON (more explicit)
SELECT * FROM orders p
JOIN clients c ON p.client_id = c.id;

-- NATURAL JOIN (automatic — avoid)
SELECT * FROM orders NATURAL JOIN clients;

USING is a shorthand when the join column has the same name in both tables. NATURAL JOIN does this automatically for all columns with the same name — dangerous and not very explicit.

-- JOIN with inequality conditions:
SELECT p.name, e.name AS escalao
FROM orders p
JOIN escaloes e
  ON p.total >= e.minimum
 AND p.total < e.maximum;

-- Useful to "fit" values into
-- ranges (brackets, ranges)

-- Warning: it can generate MANY rows
-- (partial Cartesian product)

A non-equi JOIN uses inequality conditions (greater, less, BETWEEN) instead of equality. Ideal for mapping values to ranges (price brackets, commissions). Watch out for the volume of rows generated.

SELECT *
FROM sales v
JOIN products p
    ON v.product_id = p.id
    AND v.year = p.year_sale;

-- JOIN with an extra condition
JOIN clients c
    ON v.client_id = c.id
    AND c.country = 'Portugal';

ON accepts multiple conditions with AND. Useful for tables with composite keys. Filter conditions in ON vs WHERE behave differently in a LEFT JOIN (filters in ON do not remove left-side rows).

SELECT p.name AS product,
       c.name AS category,
       f.name AS supplier
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN suppliers f ON p.supplier_id = f.id
WHERE p.active = 1;

You can chain several JOINs in the same query. Each JOIN adds a table. Keep the aliases organized and check that the ON conditions are correct to avoid accidental Cartesian products.

-- Count all rows
SELECT COUNT(*) FROM clients;

-- Count non-nulls of a column
SELECT COUNT(email) FROM clients;

-- Count distinct values
SELECT COUNT(DISTINCT city) FROM clients;

COUNT(*) counts all rows (including NULL). COUNT(column) ignores nulls. COUNT(DISTINCT col) counts unique values. It is the only function that never returns NULL.

SELECT city, COUNT(*) AS total
FROM clients
GROUP BY city
HAVING COUNT(*) > 10;

-- HAVING with aggregation
SELECT category, AVG(price) AS average
FROM products
GROUP BY category
HAVING AVG(price) > 50;

HAVING filters groups after the GROUP BY (WHERE filters rows before). Use WHERE for row conditions and HAVING for aggregation conditions. You can use an alias: HAVING total > 10.

-- WHERE cannot filter aggregates;
-- HAVING can (after the GROUP BY):
SELECT client_id, SUM(total) AS spent
FROM orders
GROUP BY client_id
HAVING SUM(total) > 500;

-- Logical order:
-- WHERE filters rows BEFORE
-- HAVING filters groups AFTER

SELECT category, COUNT(*) AS n
FROM products
WHERE active = TRUE        -- before
GROUP BY category
HAVING COUNT(*) >= 5;     -- after

HAVING filters aggregated results (after the GROUP BY), while WHERE filters rows before aggregation. Rule: WHERE first, HAVING after. You cannot use aggregates in the WHERE.

SELECT SUM(total) AS revenue FROM orders;

SELECT AVG(price) AS average FROM products;

-- Average with NULL treated the 0
SELECT AVG(COALESCE(discount, 0)) FROM items;

-- Conditional sum
SELECT SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)
FROM orders;

SUM adds up values and AVG computes the average. Both ignore NULL. For a conditional sum, combine with CASE. If all rows are NULL, the result is NULL (not 0).

-- MySQL / SQL Server
SELECT category, year, SUM(total)
FROM sales
GROUP BY category, year WITH ROLLUP;

-- PostgreSQL (GROUPING SETS)
SELECT category, year, SUM(total)
FROM sales
GROUP BY ROLLUP (category, year);

ROLLUP adds subtotal and grand total rows to the result. With GROUP BY ROLLUP(a, b) you get groups by (a,b), by (a) and the grand total. Subtotal rows have NULL in the aggregated columns.

-- Group by several columns:
SELECT country, city, COUNT(*) AS total
FROM clients
GROUP BY country, city
ORDER BY country, total DESC;

-- Each unique combination of
-- (country, city) becomes one row

-- With ROLLUP (subtotals + total):
SELECT country, city, COUNT(*)
FROM clients
GROUP BY country, city WITH ROLLUP;
-- (MySQL; PostgreSQL: ROLLUP(country, city))

GROUP BY with several columns creates a group for each unique combination. WITH ROLLUP (MySQL) or ROLLUP() (PostgreSQL) adds subtotal and grand total rows — useful for reports.

SELECT MIN(price), MAX(price) FROM products;

-- Most recent date
SELECT MAX(data) AS last_order FROM orders;

-- String: alphabetical order
SELECT MIN(name), MAX(name) FROM clients;

MIN and MAX return the smallest and largest value. They work with numbers, dates and strings (alphabetical order). They ignore NULL. Useful for finding bounds and extreme dates.

-- MySQL: concatenate the group's values
SELECT category,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
FROM products
GROUP BY category;

-- PostgreSQL: STRING_AGG
SELECT category,
       STRING_AGG(name, ', ' ORDER BY name)
FROM products
GROUP BY category;

GROUP_CONCAT (MySQL) joins the group's values into a string. STRING_AGG does the same in PostgreSQL. Useful for lists of tags, names or IDs. Size limit: group_concat_max_len.

SELECT city, COUNT(*) AS total
FROM clients
GROUP BY city;

-- Multiple columns
SELECT year, month, SUM(value) AS total
FROM sales
GROUP BY year, month
ORDER BY year, month;

GROUP BY groups rows with the same value and applies aggregations to each group. All columns in the SELECT that are not aggregated must be in the GROUP BY (in ONLY_FULL_GROUP_BY mode).

SELECT c.name,
       COUNT(p.id) AS num_orders,
       COALESCE(SUM(p.total), 0) AS total_spent
FROM clients c
LEFT JOIN orders p ON c.id = p.client_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

Combine JOIN with GROUP BY to aggregate related data. Use LEFT JOIN to include customers without orders. COALESCE converts NULL (no orders) to 0.

INSERT INTO clients (name, email, city)
VALUES ('Anna Silva', 'ana@mail.com', 'Lisbon');

-- With default values
INSERT INTO products (name, price)
VALUES ('Keyboard', DEFAULT);

INSERT adds rows. Always specify the columns explicitly (do not rely on order). DEFAULT uses the column's default value. The id with AUTO_INCREMENT is generated automatically.

UPDATE products
SET price = 99.90
WHERE id = 5;

-- Multiple columns
UPDATE clients
SET email = 'new@mail.com',
    updated_at = NOW()
WHERE id = 10;

UPDATE modifies existing rows. Always use WHERE to limit the affected rows — without it, it updates all of them. Test first with a SELECT using the same condition.

-- Deletes and reinserts if the key exists
REPLACE INTO products (id, name, price)
VALUES (5, 'Mouse Pro', 39.90);

-- Equivalent to:
-- DELETE + INSERT (if duplicate key)
-- normal INSERT (if it does not exist)

REPLACE (MySQL) deletes the existing row and inserts a new one if there is a key conflict. Different from an upsert: unspecified columns get default values (they are not kept). Prefer ON DUPLICATE KEY UPDATE.

INSERT INTO products (name, price, stock)
VALUES
    ('Mouse', 25.90, 100),
    ('Keyboard', 49.90, 50),
    ('Monitor', 299.00, 20);

Insert multiple rows in a single INSERT by separating the value sets with commas. Much faster than multiple individual INSERTs (fewer round-trips to the server).

-- Increase by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'electronic';

-- UPDATE with JOIN (MySQL)
UPDATE orders p
JOIN clients c ON p.client_id = c.id
SET p.discount = 10
WHERE c.vip = 1;

-- PostgreSQL
UPDATE orders SET discount = 10
FROM clients c
WHERE client_id = c.id AND c.vip = 1;

UPDATE can use the current column (price = price * 1.1) and JOINs to update based on another table. The UPDATE JOIN syntax varies between MySQL and PostgreSQL.

-- Several rows in a single INSERT:
INSERT INTO products (name, price)
VALUES
  ('Keyboard', 45.00),
  ('Mouse', 25.50),
  ('Monitor', 180.00);

-- Much faster than N INSERTs
-- (a single transaction)

-- INSERT from SELECT:
INSERT INTO products_archive
  (name, price)
SELECT name, price
FROM products
WHERE descontinuado = TRUE;

An INSERT with several rows (VALUES (...), (...)) is much faster than separate INSERTs (a single transaction). INSERT ... SELECT copies data from another table without leaving SQL.

-- Copy data between tables
INSERT INTO archive_orders
SELECT * FROM orders WHERE year < 2020;

-- With specific columns
INSERT INTO report (name, total)
SELECT name, SUM(total)
FROM orders GROUP BY name;

INSERT ... SELECT copies the results of a query into another table. The columns must be compatible in type and order. Ideal for archives, reports and summary tables.

DELETE FROM clients WHERE active = 0;

-- With JOIN (MySQL)
DELETE p FROM orders p
JOIN clients c ON p.client_id = c.id
WHERE c.blocked = 1;

-- Clean up an old table
DELETE FROM logs WHERE data < '2023-01-01'
LIMIT 10000;

DELETE removes rows. Always use WHERE — without it, it deletes everything. LIMIT in DELETE (MySQL) lets you delete in batches to avoid locking the table. In PostgreSQL, use DELETE ... USING for JOINs.

-- Update based on another table:

-- MySQL:
UPDATE orders p
JOIN clients c ON c.id = p.client_id
SET p.discount = 10
WHERE c.vip = TRUE;

-- PostgreSQL / standard SQL:
UPDATE orders
SET discount = 10
FROM clients
WHERE orders.client_id = clients.id
  AND clients.vip = TRUE;

-- The syntax varies between DBMSs!

UPDATE with JOIN updates rows based on another table. The syntax differs: MySQL uses UPDATE ... JOIN; PostgreSQL uses the FROM clause. Check your DBMS documentation.

-- MySQL: insert or update
INSERT INTO stats (product_id, views)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE
    views = views + 1;

-- PostgreSQL: ON CONFLICT
INSERT INTO stats (product_id, views)
VALUES (42, 1)
ON CONFLICT (product_id)
DO UPDATE SET views = stats.views + 1;

An upsert inserts if it does not exist, or updates if it already exists. In MySQL use ON DUPLICATE KEY UPDATE; in PostgreSQL use ON CONFLICT ... DO UPDATE. It requires a unique/primary key.

-- TRUNCATE: removes everything, resets AUTO_INCREMENT
TRUNCATE TABLE logs;

-- DELETE without WHERE: removes everything (slower)
DELETE FROM logs;

-- TRUNCATE does not fire triggers
-- TRUNCATE cannot have a WHERE
-- TRUNCATE is DDL (not transactional in MySQL)

TRUNCATE removes all rows quickly and resets the AUTO_INCREMENT. It does not accept WHERE, does not fire triggers and in MySQL is not transactional. Use DELETE when you need conditions or rollback.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(8,2) DEFAULT 0.00,
    stock INT UNSIGNED DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    created_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE defines columns with type and constraints. The PRIMARY KEY identifies each row. DEFAULT sets the default value. AUTO_INCREMENT (MySQL) generates sequential IDs automatically.

CREATE TABLE contas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    balance DECIMAL(10,2) CHECK (balance >= 0),
    type ENUM('personal', 'company') DEFAULT 'personal',
    created_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraints: NOT NULL (required), UNIQUE (no duplicates), CHECK (validation), DEFAULT (default value), ENUM (allowed values). They ensure integrity at the database level.

-- Create a table from a query
CREATE TABLE clients_vip AS
SELECT name, email, total_spent
FROM clients
WHERE total_spent > 1000;

-- Structure only (no data)
CREATE TABLE new LIKE clients;

CREATE TABLE AS SELECT (CTAS) creates a table with AS results of a query. It does not copy indexes or constraints — only data and types. LIKE copies the full structure (MySQL).

-- Integers
TINYINT (1 byte: -128 to 127)
SMALLINT (2 bytes)
INT (4 bytes: -2B to 2B)
BIGINT (8 bytes)

-- Decimals
DECIMAL(10,2)  -- exact (money!)
FLOAT          -- approximate (4 bytes)
DOUBLE         -- approximate (8 bytes)

Use DECIMAL for monetary values (exact). FLOAT/DOUBLE are approximate (rounding errors). INT is enough for most IDs. UNSIGNED doubles the positive limit.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    total DECIMAL(8,2),
    FOREIGN KEY (client_id)
        REFERENCES clients(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The PRIMARY KEY identifies each row (unique + not null). The FOREIGN KEY links to another table. ON DELETE CASCADE deletes orders if the customer is deleted. ON DELETE SET NULL sets NULL instead of deleting.

-- ENUM: fixed values
status ENUM('pending', 'paid', 'shipped')

-- JSON (MySQL 5.7+, PostgreSQL)
data JSON,
SELECT data->>'$.name' FROM table;

-- UUID (PostgreSQL)
id UUID DEFAULT gen_random_uuid()

-- BOOLEAN (alias of TINYINT(1) in MySQL)
active BOOLEAN DEFAULT TRUE

ENUM restricts to fixed values (efficient but rigid). The JSON type stores flexible documents. The UUID is a globally unique identifier (128 bits). BOOLEAN in MySQL is TINYINT(1) internally.

CHAR(10)       -- fixed size (codes, acronyms)
VARCHAR(255)   -- variable size (names, emails)
TEXT           -- up to 64KB (descriptions)
MEDIUMTEXT     -- up to 16MB
LONGTEXT       -- up to 4GB

-- PostgreSQL
VARCHAR(n), TEXT, CHAR(n)

CHAR has a fixed size (pads with spaces). VARCHAR stores only what is needed. TEXT for long texts (no practical limit). In PostgreSQL, TEXT and VARCHAR have equal performance.

-- Add a column
ALTER TABLE clients ADD phone VARCHAR(20);

-- Remove a column
ALTER TABLE clients DROP COLUMN fax;

-- Modify the type (MySQL)
ALTER TABLE products MODIFY price DECIMAL(10,2);

-- PostgreSQL
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10,2);

-- Rename a column
ALTER TABLE clients RENAME COLUMN name TO name_complete;

ALTER TABLE modifies the structure: add (ADD), remove (DROP), change type (MODIFY/ALTER COLUMN) or rename columns. On large tables it can be slow (it recreates the table).

-- Create a table from a query:
CREATE TABLE clients_vip AS
SELECT id, name, email
FROM clients
WHERE total_spent > 1000;

-- Copy structure + data from another:
CREATE TABLE clients_backup AS
SELECT * FROM clients;

-- Structure only (no data):
CREATE TABLE new AS
SELECT * FROM clients WHERE 1 = 0;

-- The new table does NOT inherit indexes,
-- constraints or primary keys

CREATE TABLE AS SELECT (CTAS) creates a table from AS result of a query, copying structure and data. It does not inherit indexes, constraints or keys — add them afterwards if needed.

DATE        -- '2024-01-15'
TIME        -- '14:30:00'
DATETIME    -- '2024-01-15 14:30:00'
TIMESTAMP   -- like DATETIME + time zone
YEAR        -- 2024

-- PostgreSQL
DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL

DATE stores only the date. DATETIME stores date and time. TIMESTAMP converts to UTC when saving (good for multi-timezone apps). Choose the most restrictive type that covers the need.

-- Drop a table (irreversible!)
DROP TABLE IF EXISTS temp_logs;

-- Rename a table
RENAME TABLE clients TO clients_antigos;

-- MySQL: multiple
RENAME TABLE a TO a_new, b TO b_new;

-- PostgreSQL
ALTER TABLE clients RENAME TO clients_antigos;

DROP TABLE removes the table and all the data. IF EXISTS avoids an error if it does not exist. RENAME changes the name without losing data. Careful: DROP is irreversible (no ROLLBACK on DDL in MySQL).

-- Exists only in the current session:
CREATE TEMPORARY TABLE tmp_totals (
  client_id INT,
  total DECIMAL(10,2)
);

INSERT INTO tmp_totals
SELECT client_id, SUM(total)
FROM orders GROUP BY client_id;

-- Use like any table:
SELECT * FROM tmp_totals WHERE total > 100;

-- Deleted automatically when
-- the session/connection ends
DROP TEMPORARY TABLE IF EXISTS tmp_totals;

TEMPORARY tables exist only in the current session and are deleted when the connection closes. Ideal for complex intermediate results. They are only visible to whoever created them.

CREATE VIEW clients_ativos AS
SELECT id, name, email
FROM clients
WHERE active = 1;

-- Use like a table
SELECT * FROM clients_ativos WHERE city = 'Lisbon';

-- Drop
DROP VIEW IF EXISTS clients_ativos;

A VIEW is a saved query used the a virtual table. It does not store data (it runs the query when queried). Useful for simplifying complex queries and restricting access to columns.

START TRANSACTION;

UPDATE contas SET balance = balance - 100 WHERE id = 1;
UPDATE contas SET balance = balance + 100 WHERE id = 2;

-- If everything went well:
COMMIT;

-- If there was an error:
ROLLBACK;

A transaction groups atomic operations: either all succeed (COMMIT) or none apply (ROLLBACK). It guarantees consistency (e.g. transfers). It requires a transactional engine (InnoDB in MySQL).

WITH RECURSIVE hierarquia AS (
    -- Anchor: root level
    SELECT id, name, boss_id, 1 AS level
    FROM employees WHERE boss_id IS NULL

    UNION ALL

    -- Recursion: children
    SELECT f.id, f.name, f.boss_id, h.level + 1
    FROM employees f
    JOIN hierarquia h ON f.boss_id = h.id
)
SELECT * FROM hierarquia ORDER BY level;

The recursive CTE (WITH RECURSIVE) references itself. It has an anchor part (base case) and a recursive one. Ideal for hierarchies, trees and graphs. It requires UNION ALL between the parts.

-- Common Table Expression:
WITH sales_2024 AS (
  SELECT client_id, SUM(total) AS total
  FROM orders
  WHERE YEAR(data) = 2024
  GROUP BY client_id
)
SELECT c.name, v.total
FROM sales_2024 v
JOIN clients c ON c.id = v.client_id
WHERE v.total > 1000;

-- More readable than nested
-- subqueries; you can chain several:
-- WITH a AS (...), b AS (...)

CTE (WITH name AS (...)) creates a named temporary table for AS query — far more readable than nested subqueries. You can chain several CTEs separated by commas. Supported in all modern DBMSs.

-- Simple VIEW (updatable)
CREATE VIEW vw_products AS
SELECT id, name, price FROM products WHERE active = 1;

-- INSERT/UPDATE through AS VIEW
INSERT INTO vw_products (name, price) VALUES ('New', 10);
UPDATE vw_products SET price = 15 WHERE id = 5;

-- With WITH CHECK OPTION (validation)
CREATE VIEW vw_ativos AS
SELECT * FROM clients WHERE active = 1
WITH CHECK OPTION;

Simple VIEWs (without JOIN, GROUP BY, DISTINCT) are updatable. WITH CHECK OPTION prevents inserting/updating rows that do not meet the VIEW's condition.

START TRANSACTION;

INSERT INTO orders (client_id, total) VALUES (1, 50);
SAVEPOINT after_order;

INSERT INTO items (order_id, product_id) VALUES (99, 5);

-- Error on the second INSERT: roll back only to the savepoint
ROLLBACK TO after_order;

-- The order stays, the item does not
COMMIT;

SAVEPOINT creates an intermediate point in the transaction. ROLLBACK TO reverts only up to that point (it does not undo everything). Useful for batch operations where one error should not cancel the previous work.

SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    AVG(salary) OVER (PARTITION BY department) AS average_dept
FROM employees;

-- ROW_NUMBER vs RANK vs DENSE_RANK
ROW_NUMBER()  -- 1,2,3,4 (no ties)
RANK()        -- 1,1,3,4 (skips)
DENSE_RANK()  -- 1,1,2,3 (does not skip)

Window functions calculate over a "group" without collapsing rows. PARTITION BY defines the group; ORDER BY the order. RANK, ROW_NUMBER and DENSE_RANK differ in how they handle ties.

-- Aggregation WITHOUT grouping rows:
SELECT name, department, salary,
  AVG(salary) OVER (
    PARTITION BY department
  ) AS average_dept,
  salary - AVG(salary) OVER (
    PARTITION BY department
  ) AS diff
FROM employees;

-- Ranking:
SELECT name,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC
  ) AS position
FROM employees;

-- OVER() defines the "window";
-- PARTITION BY splits into groups

Window functions calculate aggregates over a group of rows without collapsing them (unlike GROUP BY). OVER (PARTITION BY ...) defines the window. It includes ROW_NUMBER, RANK, LAG, LEAD. MySQL 8+ and PostgreSQL.

-- Simple index
CREATE INDEX idx_email ON clients(email);

-- Composite index
CREATE INDEX idx_cat_price ON products(category, price);

-- Unique index
CREATE UNIQUE INDEX idx_cpf ON clients(cpf);

-- Drop
DROP INDEX idx_email ON clients;  -- MySQL
DROP INDEX idx_email;               -- PostgreSQL

Indexes speed up WHERE, JOIN and ORDER BY. The composite index works for the first column (or both in order). UNIQUE also guarantees uniqueness. Indexes take up space and slow down INSERT/UPDATE.

-- In the WHERE (IN)
SELECT name FROM clients
WHERE id IN (SELECT client_id FROM orders);

-- In the FROM (derived table)
SELECT category, average FROM (
    SELECT category, AVG(price) AS average
    FROM products GROUP BY category
) t WHERE average > 50;

-- Correlated
SELECT name, (
    SELECT COUNT(*) FROM orders p
    WHERE p.client_id = c.id
) AS num_orders FROM clients c;

Subqueries can be in the WHERE (filter), in the FROM (derived table) or in the SELECT (scalar). The correlated one references the outer query (runs per row). Prefer JOIN when possible for performance.

SELECT month, total,
    LAG(total, 1) OVER (ORDER BY month) AS prev_month,
    LEAD(total, 1) OVER (ORDER BY month) AS next_month,
    SUM(total) OVER (ORDER BY month) AS cumulative,
    total * 100.0 / SUM(total) OVER () AS percentage
FROM sales_mensais;

LAG/LEAD access previous/following rows. SUM() OVER (ORDER BY) calculates running totals. The empty OVER () applies to the whole table. It replaces complex self-JOINs.

EXPLAIN SELECT * FROM products
WHERE category = 'electronic' AND price > 100;

-- MySQL: JSON format
EXPLAIN FORMAT=JSON SELECT ...;

-- PostgreSQL: with real metrics
EXPLAIN ANALYZE SELECT ...;

EXPLAIN shows how the query will be executed: whether it uses an index, how many rows it examines, the scan type. type: ALL = full scan (bad). type: ref/range = uses an index (good). ANALYZE runs it and shows real times.

WITH sales_mensais AS (
    SELECT MONTH(data) AS month, SUM(total) AS total
    FROM orders
    WHERE YEAR(data) = 2024
    GROUP BY MONTH(data)
)
SELECT month, total,
       total - LAG(total) OVER (ORDER BY month) AS difference
FROM sales_mensais;

The CTE (WITH) creates a named temporary table for AS query. More readable than nested subqueries. It can be referenced multiple times. Supported in MySQL 8+, PostgreSQL, SQL Server.

-- MySQL
PREPARE stmt FROM 'SELECT * FROM clients WHERE id = ?';
SET @id = 42;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

-- In the application (PHP/PDO)
-- $stmt = $pdo->prepare('SELECT * FROM clients WHERE id = ?');
-- $stmt->execute([$id]);

Prepared statements separate SQL from data: they prevent SQL injection and allow reusing the execution plan. The ? is the placeholder. In practice, use the language driver (PDO, psycopg2) instead of raw SQL.

UPPER('hello')         -- 'HELLO'
LOWER('HELLO')         -- 'hello'
LENGTH('hello')        -- 5 (bytes in MySQL)
CHAR_LENGTH('hello')   -- 5 (characters)
TRIM('  hello  ')      -- 'hello'
SUBSTRING('hello', 1, 2)  -- 'he'
REPLACE('hello', 'l', 'L') -- 'heLLo'
LEFT('hello', 2)       -- 'he'
RIGHT('hello', 2)      -- 'lo'

Text manipulation functions. LENGTH counts bytes; CHAR_LENGTH counts characters (important with accents/UTF-8). SUBSTRING(str, pos, len) is 1-based. TRIM removes spaces at the ends.

-- Explicit type conversion
SELECT CAST('123' AS INT);
SELECT CAST(price AS CHAR) FROM products;
SELECT CAST('2024-01-15' AS DATE);

-- MySQL: CONVERT
SELECT CONVERT(name, CHAR) FROM clients;

-- PostgreSQL: :: (shorthand)
SELECT '123'::INT, price::TEXT FROM products;

CAST converts between types explicitly (SQL standard). CONVERT is an alternative (MySQL). In PostgreSQL, the :: operator is more concise. Implicit conversions can cause loss of precision.

-- MySQL
SELECT * FROM clients
WHERE email REGEXP '^[a-z]+@[a-z]+\\.com$';

-- PostgreSQL (~ operator)
SELECT * FROM clients
WHERE email ~ '^[a-z]+@[a-z]+\.com$';

-- MySQL 8: REGEXP_LIKE
WHERE REGEXP_LIKE(phone, '^[0-9]{9}$');

REGEXP (MySQL) or ~ (PostgreSQL) matches by regular expressions. More powerful than LIKE but slower. Use ^ (start), $ (end), [0-9] (class), {n} (repetition).

NOW()                -- current date and time
CURDATE()            -- date only
YEAR('2024-03-15')   -- 2024
MONTH('2024-03-15')  -- 3
DAY('2024-03-15')    -- 15
DATEDIFF('2024-12-31', '2024-01-01')  -- 365
DATE_ADD(NOW(), INTERVAL 7 DAY)       -- +7 days
DATE_FORMAT(NOW(), '%d/%m/%Y')        -- '15/03/2024'

Functions for working with dates. DATEDIFF returns the difference in days. DATE_ADD/DATE_SUB add/subtract intervals. DATE_FORMAT formats the output (MySQL). In PostgreSQL use TO_CHAR.

-- MySQL: IF(condition, true_value, false_value)
SELECT IF(price > 100, 'expensive', 'cheap') FROM products;

-- SQL Server: IIF
SELECT IIF(stock > 0, 'available', 'expired') FROM products;

-- SQL standard: CASE (works everywhere)
SELECT CASE WHEN price > 100 THEN 'expensive' ELSE 'cheap' END
FROM products;

IF() (MySQL) and IIF() (SQL Server) are 3-argument conditionals. CASE is the universal SQL standard and more flexible (multiple conditions). Prefer CASE for portability.

-- Current date/time:
SELECT NOW();            -- date + time
SELECT CURDATE();        -- date only (MySQL)

-- Extract parts:
SELECT YEAR(data), MONTH(data), DAY(data)
FROM orders;

-- Date arithmetic (MySQL):
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF('2024-12-31', NOW());

-- PostgreSQL:
SELECT NOW() + INTERVAL '7 days';
SELECT EXTRACT(YEAR FROM data);

-- Format (MySQL):
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');

Date functions: NOW()/CURDATE() give the current date; YEAR()/MONTH() extract parts; DATE_ADD/INTERVAL do arithmetic. The syntax varies between MySQL and PostgreSQL.

ROUND(3.14159, 2)  -- 3.14
CEIL(4.1)          -- 5
FLOOR(4.9)         -- 4
ABS(-10)           -- 10
MOD(10, 3)         -- 1
POWER(2, 10)       -- 1024
SQRT(144)          -- 12
RAND()             -- random 0-1

ROUND rounds to N decimals. CEIL rounds up, FLOOR down. MOD returns the remainder of the division. ROUND uses "banker's" rounding in some DBMSs (0.5 → nearest even).

-- MySQL
SELECT GROUP_CONCAT(name SEPARATOR '; ')
FROM products GROUP BY category;

-- PostgreSQL
SELECT STRING_AGG(name, '; ' ORDER BY name)
FROM products GROUP BY category;

-- SQL Server
SELECT STRING_AGG(name, '; ')
FROM products GROUP BY category;

Functions that concatenate the values of a group into a string. GROUP_CONCAT (MySQL), STRING_AGG (PostgreSQL/SQL Server). They accept a custom separator and internal ordering.

-- Concatenate strings:
SELECT CONCAT(name, ' ', surname) AS complete
FROM clients;

-- || operator (SQL standard):
SELECT name || ' ' || surname FROM clients;

-- String length:
SELECT LENGTH(name) FROM clients;

-- Uppercase/lowercase:
SELECT UPPER(name), LOWER(email) FROM clients;

-- Substring:
SELECT SUBSTRING(name, 1, 3) FROM clients;

-- TRIM removes spaces:
SELECT TRIM('  hello  ');

String functions: CONCAT() or the || operator join strings; LENGTH() gives the size; UPPER/LOWER change the capitalization; SUBSTRING() extracts parts; TRIM() removes spaces.

-- Returns the first non-NULL
SELECT COALESCE(phone, mobile, 'without contact')
FROM clients;

-- MySQL: IFNULL (only 2 args)
SELECT IFNULL(discount, 0) FROM products;

-- PostgreSQL: NULLIF (inverse)
SELECT NULLIF(stock, 0) FROM products;
-- Returns NULL if stock = 0 (avoids division by zero)

COALESCE returns the first non-NULL value in the list (SQL standard). IFNULL is MySQL (only 2 args). NULLIF(a, b) returns NULL if a=b — useful to avoid division by zero.

-- Session information
SELECT DATABASE();        -- current DB
SELECT USER();            -- user
SELECT VERSION();         -- DBMS version
SELECT LAST_INSERT_ID();  -- last AUTO_INCREMENT

-- List tables
SHOW TABLES;              -- MySQL
\dt                       -- PostgreSQL (psql)

-- Describe a table
DESCRIBE clients;        -- MySQL
\d clients               -- PostgreSQL (psql)

System functions return information about the session and structure. LAST_INSERT_ID() returns the last generated ID. SHOW TABLES/DESCRIBE are MySQL commands; in PostgreSQL use \dt and \d in psql.

-- Bad: brings everything (unnecessary columns, more I/O)
SELECT * FROM clients;

-- Good: only what is needed
SELECT id, name, email FROM clients;

-- In JOINs, never use *
SELECT c.name, p.total
FROM clients c JOIN orders p ON c.id = p.client_id;

SELECT * brings unnecessary columns, increases network traffic and prevents covering index optimizations. Always list the needed columns. In JOINs, it can bring columns from the wrong tables.

-- Line comment (MySQL, PostgreSQL)
# Line comment (MySQL only)

/* Block
   comment
   (all DBMSs) */

-- Tip: document complex queries
/* Monthly report: sum sales by category
   Excludes returns (status != 3) */
SELECT category, SUM(total) FROM sales
WHERE status != 3 GROUP BY category;

Use -- for line comments (SQL standard), # (MySQL only) and /* */ for blocks. Document complex queries with the "why" and not the "what". Useful for temporarily disabling parts.

-- A large OFFSET is SLOW
-- (it scans and discards N rows):
SELECT * FROM orders
ORDER BY id LIMIT 20 OFFSET 100000;

-- Keyset pagination is FAST
-- (uses the index directly):
SELECT * FROM orders
WHERE id > 100000        -- last id seen
ORDER BY id
LIMIT 20;

-- Save the last id of the page
-- and pass it on the next request

A large OFFSET forces scanning and discarding thousands of rows (slow). Keyset pagination (WHERE id > last) uses the index directly — constant speed on any page. Ideal for feeds and long listings.

-- NEVER concatenate input:
-- "SELECT * FROM users WHERE id = " + input  ← DANGER

-- ALWAYS use prepared statements:
SELECT * FROM users WHERE id = ?;
SELECT * FROM users WHERE email = ?;

-- Validate and escape on the application side
-- Use an ORM (Eloquent, SQLAlchemy, etc.)

Never concatenate user input into SQL — it allows SQL injection. Always use prepared statements with placeholders (? or :name). Validate types on the server. Prefer an ORM with a query builder.

-- N+1 problem: 1 query + N queries
SELECT * FROM clients;           -- 1
SELECT * FROM orders WHERE client_id = 1;  -- N
SELECT * FROM orders WHERE client_id = 2;  -- N...

-- Solution: JOIN (1 query)
SELECT c.name, p.total
FROM clients c
LEFT JOIN orders p ON c.id = p.client_id;

-- Or: IN (2 queries)
SELECT * FROM clients;
SELECT * FROM orders WHERE client_id IN (1,2,3,...);

The N+1 problem occurs when you run 1 query for the list + N for the related records. Solution: use JOIN or WHERE IN with the IDs. In ORMs, use eager loading (with() in Eloquent).

-- See the execution plan:
EXPLAIN SELECT * FROM orders
WHERE client_id = 42;

-- What to look for:
--   type: ALL  -> scans everything (bad!)
--   type: ref/eq_ref -> uses index (good)
--   rows: estimate of rows read

-- With ANALYZE (MySQL 8+):
EXPLAIN ANALYZE SELECT ...;
-- shows real times

-- If type=ALL on a frequent filter,
-- an index on that column is missing

EXPLAIN shows how the query will be executed. type: ALL means a full scan (missing index); ref/eq_ref indicate index use. EXPLAIN ANALYZE gives real times. The #1 optimization tool.

-- Index WHERE and JOIN columns
CREATE INDEX idx_email ON clients(email);

-- Composite index: order matters
CREATE INDEX idx_cat_price ON products(category, price);
-- Works for: WHERE category = X
-- Works for: WHERE category = X AND price > Y
-- Does NOT work for: WHERE price > Y (alone)

-- Check whether it uses the index
EXPLAIN SELECT * FROM products WHERE category = 'x';

Index columns used in WHERE, JOIN and ORDER BY. In composite indexes, the order matters: it works for the first column (prefix). Do not index everything — each index slows down INSERT/UPDATE.

-- Bad: VARCHAR for everything
code VARCHAR(255)  -- if it is always 10 chars

-- Good: restrictive type
code CHAR(10)      -- fixed size
price DECIMAL(8,2)   -- not FLOAT for money
active BOOLEAN        -- not VARCHAR('yes'/'not')
data TIMESTAMP       -- not VARCHAR('2024-01-15')

Choose the most restrictive type: CHAR for fixed size, DECIMAL for money, BOOLEAN for flags, TIMESTAMP for dates. Correct types = less space, smaller indexes, faster comparisons.

-- DANGER: affects ALL rows
UPDATE products SET active = 0;
DELETE FROM logs;

-- SAFE: test first
SELECT COUNT(*) FROM products WHERE category = 'x';
UPDATE products SET active = 0 WHERE category = 'x';

-- Tip: use a transaction to test
START TRANSACTION;
DELETE FROM logs WHERE data < '2023-01-01';
-- Check the result, then:
-- COMMIT; or ROLLBACK;

Without WHERE, UPDATE/DELETE affects all rows. Always test with a SELECT first. Use transactions so you can roll back. In production, make backups before mass operations.

-- Tables: plural, snake_case
clients, orders, categories_product

-- Columns: singular, snake_case
name, email, created_em, client_id

-- Keys: singular_table_id
client_id, product_id

-- Indexes: idx_table_column
idx_clients_email, idx_orders_data

-- Constraints: fk_, uk_, chk_
fk_orders_client, uk_clients_email

Use snake_case and descriptive names. Tables in plural (clients), FKs the table_id. Indexes with the idx_ prefix. Avoid reserved words (order, group, select). Consistency > personal preference.