Basic Queries

SELECT

SELECT is the basic query for searching data from the database.

-- Gets every column from table called people
SELECT * FROM people;
-- Gets only columns name and age from table called
SELECT name, age FROM people;
-- Gets the name column from table, but renames the column in result as 'alias'
SELECT name AS alias FROM people;
-- Gets every column from table, limits the results to only first 5 lines
SELECT * FROM people LIMIT 5;

Mathematical operations

In SELECT queries and WHERE conditions it is possible to use basic math and functions.

For example, multiply ages by 2

SELECT name, age * 2 AS double_age FROM people;

Check also Functions

DISTINCT

DISTINCT filters repetitive entries out of the result. If there is multiple entries with same name, following will give only one row per different name.

SELECT DISTINCT name FROM people;

WHERE

Adding a WHERE clause restricts query results. Only rows that match the condition are returned.

SELECT * FROM table WHERE <conditions>;

For example

SELECT age
FROM people
WHERE city = 'Rovaniemi' AND name = 'John';

Returns age for rows where:

• city is Rovaniemi
• name is John

Comparison operators

• = equal
• < less than
• > greater than
• <= less than or equal
• >= greater than or equal

Reference: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

Logical operators

Logical operators combine multiple conditions. There can one or more logical operations

• AND or && True if both conditions are true
• OR or || True if either condition is true
• NOT or ! Negates the condition
• XOR True if exactly one condition is true

Reference: https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html

You can use parentheses to help with grouping. Similar how they give priority in mathematics

-- This will give the results where a is 2 or 4 and c is always A
... WHERE (a=2 or a=4) and c = 'A'

LIKE

Simple string pattern matching

• % matches any number of characters, including zero.
• _ matches any single character.

-- This matches any rows that has name starting with J
SELECT * FROM people WHERE name LIKE 'J%';

-- This matches any rows that has firstname ending with n and has 3 charactes before
SELECT * from people WHERE name LIKE '___n';

IN

Check if value exists in a list.

SELECT * FROM people WHERE age IN (25, 31, 42);

This will show the grades where the age is 25, 31 or 42.

TIP

The list can be also populated with subquery

BETWEEN

Filters rows where a value falls within a given range. The range is inclusive — both the start and end values are included.

SELECT * FROM people WHERE age BETWEEN 20 AND 30;

This returns rows where age is between min. 20 and max 30.

BETWEEN also works with dates and text:

SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

IS NULL / IS NOT NULL

Checks whether a value is NULL (missing/unknown). You cannot use = to compare with NULL — you must use IS NULL or IS NOT NULL.

SELECT * FROM people WHERE age IS NULL;

Returns rows where age has no value.

SELECT * FROM people WHERE age IS NOT NULL;

Returns rows where age has a value.

LIMIT

LIMIT restricts how many rows are returned.

SELECT * FROM peple LIMIT 10;

Returns only the first 10 rows.

OFFSET

OFFSET skips a number of rows before starting to return results.

SELECT * FROM people LIMIT 3 OFFSET 5;

Skips the first 5 rows, then returns the next 3.

Useful for pagination — for example, showing page 3 with 10 results per page.

Aggregate functions

Aggregate functions perform a calculation on a set of rows and return a single value. Often used together with GROUP BY.

• COUNT() — number of rows, "how many rows there is in result"
• SUM() — total of numeric values in a column
• AVG() — average of numeric values in a column
• MIN() — smallest value in a column
• MAX() — largest value in a column

TIP

When asking how many results there is in database, you can't give the value of the query tool. For example MySQL Workbench shows the total results received, but it CAN be limited by the tool.

Always get the result with count()

SELECT COUNT(*) FROM people;

Returns the total number of rows in the people table.

SELECT AVG(age) FROM people;

Returns the average age.

SELECT MIN(age), MAX(age) FROM people

Returns the lowest and highest age.

SELECT name, AVG(age) FROM people GROUP BY city;

Returns the average age for each city.

GROUP BY

Group by groups the data based on a column. Basically each different entry on that column (in a result set) has its own bucket. The aggregation is done for each bucket separately and the result is returned for each of the group / bucket.

SELECT year, SUM(profit) FROM sales GROUP BY year;

WARNING

Selecting non-aggregated / non grouped column with aggregate will give random results. For example

SELECT name, AVG(age) FROM people

will give random name for the average age.

HAVING

Having filters the results after the grouping is done

SELECT city, COUNT(name)
FROM people
GROUP BY city
HAVING COUNT(name) > 5;

WARNING

If you reference non-grouped columns in the HAVING clause, the behavior is undefined.

ORDER BY

Query results can be sorted in ascending or descending order.

Keywords:

• ASC — ascending Numbers increase, text follows alphabetical order
• DESC — descending Numbers decrease, text follows reverse alphabetical order

Sorting by a single column

SELECT *
FROM people
ORDER BY age DESC;

Sorts grades from highest to lowest.

Sorting by multiple columns

You can define multiple sorting criteria.

SELECT age, name
FROM people
ORDER BY age DESC, name ASC;

Explanation:

• First sorts by grade (descending)
• If grades are equal, sorts by name (ascending)

TIP

• Sorting happens after filtering (WHERE).
• Sorting affects only the result set, not stored data.

UNION

Combines two result sets. They should have similar result columns.

SELECT city FROM people
UNION
SELECT city FROM suppliers
ORDER BY city;

Subqueries

Subqueries are queries in a query. Subqueries are done first. The order is the innermost query first and finally the main query.

IN subquery

In subquery can result multiple values

SELECT * FROM items
WHERE categoryid IN (
  SELECT id FROM categories WHERE department = 'Technology'
);

Scalar subquery

Scalar subquery returns single value

SELECT * FROM people
WHERE age = (SELECT MAX(age) FROM people);

This will first find out the max for the age column. After that it will use the value in main query.

Subquery in FROM clause

This is "derived table". Subquery returns a resultset, which has name t in this case (given in sql as alias). Then it will be used in the main query.

SELECT t.department_id, t.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) t WHERE ...;

Data is grouped and then the the select is applied to the data in subquery. It can be filtered with WHERE etc. like normal table.