NooliSELECT
Choose which columns to return.
SELECT name, email FROM users;
SQL Basics
Core SQL keywords and concepts with short examples.
Open in Nooli
Free deck. Start learning right after you install.
Deck code
3ry39cvvc9
Cards
5 of 2121 of 21WHERE
Filter rows by a condition.
SELECT * FROM users WHERE age > 18;
ORDER BY
Sort results. ASC (default) or DESC.
ORDER BY created_at DESC
LIMIT
Return only the first N rows.
LIMIT 10
DISTINCT
Remove duplicate rows from the result.
SELECT DISTINCT city FROM users;
Show 16 more cards
Or learn them all in the app
Or learn them all in the app
COUNT(), SUM(), AVG()
Aggregate functions: count rows, sum values, average values.
GROUP BY
Group rows with the same value, usually with aggregates.
SELECT city, COUNT(*) FROM users GROUP BY city;
HAVING
Filter groups after GROUP BY.
HAVING COUNT(*) > 5
WHERE vs HAVING
WHERE filters rows before grouping.
HAVING filters groups after grouping.
INNER JOIN
Return only rows that match in both tables.
FROM orders JOIN users ON orders.user_id = users.id
LEFT JOIN
Return all rows from the left table, plus matches from the right (NULL if none).
INSERT INTO
Add new rows.
INSERT INTO users (name) VALUES ('Ilia');
UPDATE
Change existing rows. Always use WHERE!
UPDATE users SET name = 'Ann' WHERE id = 1;
DELETE
Remove rows. Always use WHERE!
DELETE FROM users WHERE id = 1;
LIKE
Pattern matching. % = any characters, _ = one character.
WHERE name LIKE 'A%'
IN
Match any value in a list.
WHERE id IN (1, 2, 3)
BETWEEN
Match values in a range (inclusive).
WHERE age BETWEEN 18 AND 30
IS NULL
Check for missing values. = NULL does not work!
PRIMARY KEY
A column that uniquely identifies each row. Unique and not NULL.
FOREIGN KEY
A column that references the primary key of another table.
INDEX
A structure that speeds up lookups on a column, at the cost of slower writes and extra space.