<!-- https://learnsql.cpetoday.com/analyze/sql-essentials -->
# SQL essentials: read data, join it, summarize it

This guide teaches the SQL you use every day, with the LearnSQL sample databases. Run every example in the [SQL editor](/editor) or the [Playground](/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

```sql
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`.

> Diagram: The six clauses of a query in the order you write them, and the order the database runs them: FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY.

## Read rows

```sql
-- SQL Server
SELECT TOP 10 customerName, country, creditLimit
FROM classicmodels.customers
ORDER BY creditLimit DESC;
```

```sql
-- 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 BY` is the only thing that guarantees order. Without it a result set has **no** order, even if it looks sorted.
- `DISTINCT` removes duplicate rows: `SELECT DISTINCT country FROM classicmodels.customers`.

> Exercise: List the five customers with the highest credit limits. Show the customer name, the country and the credit limit, highest limit first.


## Filter with WHERE

```sql
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.

```sql
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

```sql
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 `WHEN`s from the top and stops at the first match. Without `ELSE` an unmatched row gets `NULL`.

> Exercise: 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.


## Join tables

A join puts rows from two tables side by side when a key matches. Use the **foreign keys**: the [Schema explorer](/relationships) shows them as lines.

> Diagram: Two small tables, Vendors and Invoices, are joined on VendorID. Only invoices with a matching vendor appear in the result, with the vendor name added.

```sql
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. |

> Diagram: Customers and Orders joined two ways. An inner join keeps only customers with orders. A left join keeps every customer and shows NULL for Pine Group, which has no orders.

**Find the missing ones** (an anti-join): customers with no orders.

```sql
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

```sql
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;
```

> Diagram: Six general ledger lines grouped by account become three rows, one per account, with a line count and a total for each.

- Every column in `SELECT` must be in `GROUP BY` or inside an aggregate (`SUM`, `COUNT`, `AVG`, `MIN`, `MAX`).
- `WHERE` filters **rows before** grouping. `HAVING` filters **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):

```sql
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`.

> Exercise: For each order year, show the total sales (quantity times price, rounded to cents) and the number of orders. Oldest year first.


## Text

```sql
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 `NULL`s; `a + b` in SQL Server does not (the result is `NULL`).

## Subqueries and CTEs

A **subquery** is a query inside a query.

```sql
-- 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:

```sql
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 `NULL`s 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](/analyze/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](/analyze/fundamentals) or [Power Query M](/analyze/power-query-m-basics).
