Downloads

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.

Order you write Database run order SELECT FROM WHERE GROUP BY HAVING ORDER BY 1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 SELECT 6 ORDER BY A SELECT alias exists only after SELECT runs. So WHERE cannot use it, but ORDER BY can.
The database runs a query in a different order from the one you write it in.

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;

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.

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

Watch out

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).

Tip

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.

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.

Vendors VendorID Vendor V-101 Harbor Supplies V-102 Pine Office V-103 Atlas Print Invoices Invoice VendorID Amount INV-1041 V-102 4,200 INV-1042 V-101 1,150 INV-1043 V-104 980 join on VendorID Result: matched rows only Invoice Vendor Amount INV-1041 Pine Office 4,200 INV-1042 Harbor Supplies 1,150 INV-1043 has no vendor V-104, so it drops out.
An inner join keeps the rows whose keys match. Made-up rows, for illustration.
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.
Customers ID Customer C1 Atelier Co C2 Harbor Ltd C3 Pine Group Orders Order Customer ID O1 C1 O2 C2 O3 C1 INNER JOIN: matches only Customer Order Atelier Co O1 Harbor Ltd O2 Atelier Co O3 LEFT JOIN: every customer Customer Order Atelier Co O1 Harbor Ltd O2 Atelier Co O3 Pine Group NULL
INNER JOIN keeps matches only. LEFT JOIN keeps every row on the left and fills the gaps with NULL.

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:

  1. Join on the declared key, and use the full key when the key has two columns (orderdetails has orderNumber and productCode).
  2. Count before and after. If a join makes the row count grow, one side is not unique on that key.
  3. Put filters on the right table in the ON, not the WHERE, for a LEFT JOIN. A WHERE on 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;
GL lines (6 rows) Account Amount Rent 2,000 Software 450 Rent 2,000 Travel 310 Software 450 Rent 2,000 GROUP BY SUM, COUNT Result: 3 rows Account Lines Total Rent 3 6,000 Software 2 900 Travel 1 310 Six lines became three rows. Each row is one group.
GROUP BY turns many rows into one row per group. Each aggregate, such as SUM or COUNT, works on one group.

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.

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.