SQL essentials
SELECT, WHERE, joins, GROUP BY, CASE, dates, subqueries and CTEs, with the SQL Server and MariaDB differences.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Write SELECT queries with WHERE, CASE, joins and GROUP BY.
- Work with dates and text on SQL Server and on MariaDB.
- Use subqueries and CTEs to keep multi-step queries readable.
- Avoid the common mistakes, and tell the SQL Server and MariaDB syntax apart.
This guide teaches the SQL you use every day, with the LearnSQL sample databases. Run every example in the SQL editor or the Playground. The examples use classicmodels, a model-car wholesaler: customers, orders, orderdetails, products, productlines, employees, offices and payments.
SQL Server is the primary engine here, and MariaDB is the second. They share most of SQL but not all of it. A table at the end lists the differences. Every query in this guide is a read-only SELECT.
The shape of a query
SELECT column_list FROM table WHERE row_filter GROUP BY grouping_columns HAVING group_filter ORDER BY sort_columns
You write it in that order. The database runs it in a different order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. That is why you cannot use a SELECT alias in WHERE, but you can in ORDER BY.
Read rows
-- SQL Server SELECT TOP 10 customerName, country, creditLimit FROM classicmodels.customers ORDER BY creditLimit DESC;
MariaDB version
-- MariaDB SELECT customerName, country, creditLimit FROM customers ORDER BY creditLimit DESC LIMIT 10;
SELECT *is fine for a look, bad for a report. Name the columns you need.- A SQL Server table lives in a schema. In the sample databases the schema is named after the database, so write
classicmodels.customers. ORDER BYis the only thing that guarantees order. Without it a result set has no order, even if it looks sorted.DISTINCTremoves duplicate rows:SELECT DISTINCT country FROM classicmodels.customers.
Try it
The five biggest credit limits
List the five customers with the highest credit limits. Show the customer name, the country and the credit limit, highest limit first.
Runs on classicmodels (SQL Server). Only read-only SELECT queries run here.
On SQL Server, TOP 5 goes right after SELECT. ORDER BY decides which five come out first.
Filter with WHERE
SELECT customerName, country, creditLimit
FROM classicmodels.customers
WHERE country = 'USA'
AND creditLimit >= 100000
AND state IN ('CA', 'NY')
AND customerName LIKE 'A%';
| Test | Example |
|---|---|
| Equal, not equal | =, <> (or !=) |
| Range | BETWEEN 1000 AND 5000 (includes both ends) |
| List | IN ('CA', 'NY') |
| Pattern | LIKE 'A%' (starts with A), LIKE '%shop%', _ is one character |
| Combine | AND, OR, NOT. Use parentheses when you mix AND and OR. |
NULL is not a value
NULL means unknown. Comparing anything to NULL with = gives unknown, not true, so the row is dropped.
SELECT customerName FROM classicmodels.customers WHERE state IS NULL; -- right SELECT customerName FROM classicmodels.customers WHERE state = NULL; -- returns nothing
Handle it on purpose: state IS NOT NULL, or give a default with COALESCE(state, 'n/a') (works in both engines).
Aggregates such as SUM and AVG ignore NULL; COUNT(*) counts rows and COUNT(column) counts non-null values.
Calculate and label with CASE
SELECT customerName,
creditLimit,
CASE WHEN creditLimit >= 100000 THEN 'High'
WHEN creditLimit >= 50000 THEN 'Medium'
ELSE 'Low' END AS creditBand
FROM classicmodels.customers;
CASE checks its WHENs from the top and stops at the first match. Without ELSE an unmatched row gets NULL.
Try it
Customers in each credit band
Count the customers in each credit band. High is a limit of 100000 or more, Medium is 50000 up to 100000, and Low is under 50000. Show the band and the number of customers.
Runs on classicmodels (SQL Server). Only read-only SELECT queries run here.
Put the same CASE expression in GROUP BY that you use in SELECT. Then COUNT(*) counts the rows in each band.
Join tables
A join puts rows from two tables side by side when a key matches. Use the foreign keys: the Schema explorer shows them as lines.
SELECT o.orderNumber, o.orderDate, c.customerName FROM classicmodels.orders AS o JOIN classicmodels.customers AS c ON c.customerNumber = o.customerNumber;
| Join | Keeps |
|---|---|
INNER JOIN (or JOIN) |
Only rows that match on both sides |
LEFT JOIN |
Every row on the left, with NULL where nothing matches |
RIGHT JOIN |
The mirror image. Most people swap the tables and use LEFT. |
FULL OUTER JOIN |
Everything from both sides (SQL Server; MariaDB has no FULL join) |
CROSS JOIN |
Every pairing. Rarely what you want. |
Find the missing ones (an anti-join): customers with no orders.
SELECT c.customerName FROM classicmodels.customers AS c LEFT JOIN classicmodels.orders AS o ON o.customerNumber = c.customerNumber WHERE o.orderNumber IS NULL;
Three habits prevent most join bugs:
- Join on the declared key, and use the full key when the key has two columns (
orderdetailshasorderNumberandproductCode). - Count before and after. If a join makes the row count grow, one side is not unique on that key.
- Put filters on the right table in the
ON, not theWHERE, for aLEFT JOIN. AWHEREon the right table turns it into an inner join.
Summarize with GROUP BY
SELECT p.productLine,
COUNT(*) AS lines,
SUM(d.quantityOrdered) AS units,
SUM(d.quantityOrdered * d.priceEach) AS sales
FROM classicmodels.orderdetails AS d
JOIN classicmodels.products AS p ON p.productCode = d.productCode
GROUP BY p.productLine
ORDER BY sales DESC;
- Every column in
SELECTmust be inGROUP BYor inside an aggregate (SUM,COUNT,AVG,MIN,MAX). WHEREfilters rows before grouping.HAVINGfilters groups after it:HAVING SUM(...) > 100000.COUNT(DISTINCT customerNumber)counts unique customers.
Dates
| Goal | SQL Server | MariaDB |
|---|---|---|
| Today | CAST(GETDATE() AS date) |
CURDATE() |
| Year of a date | YEAR(d) |
YEAR(d) |
| First of the month | DATEFROMPARTS(YEAR(d), MONTH(d), 1) |
DATE_FORMAT(d, '%Y-%m-01') |
| Add days | DATEADD(day, 30, d) |
DATE_ADD(d, INTERVAL 30 DAY) |
| Days between | DATEDIFF(day, a, b) |
DATEDIFF(b, a) |
| Format as text | FORMAT(d, 'yyyy-MM') |
DATE_FORMAT(d, '%Y-%m') |
Group sales by month (SQL Server):
SELECT DATEFROMPARTS(YEAR(o.orderDate), MONTH(o.orderDate), 1) AS month,
SUM(d.quantityOrdered * d.priceEach) AS sales
FROM classicmodels.orders AS o
JOIN classicmodels.orderdetails AS d ON d.orderNumber = o.orderNumber
GROUP BY DATEFROMPARTS(YEAR(o.orderDate), MONTH(o.orderDate), 1)
ORDER BY month;
The sample dates are shifted every day so the latest order is today. Do not hard-code a date; ask the data for its own maximum: SELECT MAX(orderDate) FROM classicmodels.orders.
Try it
Sales and orders by year
For each order year, show the total sales (quantity times price, rounded to cents) and the number of orders. Oldest year first.
Runs on classicmodels (SQL Server). Only read-only SELECT queries run here.
Join orders to their lines first, then group by the year of the order date. COUNT(DISTINCT ...) counts each order once, however many lines it has.
Text
UPPER(x) LOWER(x) LTRIM(x) RTRIM(x) LEN(x) -- SQL Server LENGTH(x) -- MariaDB SUBSTRING(x, 1, 3) REPLACE(x, 'a', 'b') CONCAT(a, ' ', b)
CONCAT ignores NULLs; a + b in SQL Server does not (the result is NULL).
Subqueries and CTEs
A subquery is a query inside a query.
-- customers who spend more than the average payment SELECT customerNumber, SUM(amount) AS paid FROM classicmodels.payments GROUP BY customerNumber HAVING SUM(amount) > (SELECT AVG(amount) FROM classicmodels.payments);
A CTE (WITH) names a step so a long query reads top to bottom:
WITH line_sales AS (
SELECT o.customerNumber, d.quantityOrdered * d.priceEach AS sales
FROM classicmodels.orders AS o
JOIN classicmodels.orderdetails AS d ON d.orderNumber = o.orderNumber
)
SELECT TOP 5 customerNumber, SUM(sales) AS total
FROM line_sales
GROUP BY customerNumber
ORDER BY total DESC;
Use EXISTS to test for related rows: WHERE EXISTS (SELECT 1 FROM classicmodels.orders o WHERE o.customerNumber = c.customerNumber). It handles NULLs better than NOT IN, which returns nothing if the list contains a NULL.
Combine result sets
UNION ALL stacks results with the same columns and keeps duplicates. UNION removes duplicates (and is slower). INTERSECT and EXCEPT exist in SQL Server (MariaDB has INTERSECT and EXCEPT from 10.3).
SQL Server and MariaDB at a glance
| SQL Server | MariaDB | |
|---|---|---|
| Limit rows | TOP n or OFFSET 0 ROWS FETCH NEXT n ROWS ONLY |
LIMIT n |
| Quote a name | [Order Details] |
`Order Details` |
| Null default | ISNULL(x, 0), COALESCE |
IFNULL(x, 0), COALESCE |
| String join | + or CONCAT |
CONCAT |
| Current time | GETDATE(), SYSDATETIME() |
NOW() |
| Integer divide | 5 / 2 is 2 |
5 / 2 is 2.5000, use DIV |
| Case | Depends on collation, usually case-insensitive | Depends on collation, usually case-insensitive |
Common mistakes
| Mistake | Fix |
|---|---|
| Totals too big after a join | A join multiplied rows. Check key uniqueness, or aggregate each side first, then join. |
WHERE x = NULL |
Use IS NULL. |
HAVING used to filter rows |
Use WHERE for rows, HAVING for aggregates. |
Using SELECT alias in WHERE |
Repeat the expression, or wrap in a CTE. |
Invalid column name |
Check the schema prefix and the spelling, or an alias used before it exists. |
LEFT JOIN returns the same as INNER |
A WHERE on the right table removed the unmatched rows. Move it to ON. |
Where next
SQL deep end covers window functions, recursive queries, indexes and execution plans, and patterns such as deduplication and gaps. Then take what you built to a report in DAX fundamentals or Power Query M.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.