I WILL CONQUER SQL

I WILL GET THE JOB I WANT

SELECT column_name

Shows the column(s) you want to see

SELECT *

Shows all columns

FROM table_name

Specifies which table to pull data from

WHERE condition

Filters rows based on a condition

DISTINCT column_name

Removes duplicate values

AS alias_name

Renames a column or table temporarily

AND

Both conditions (in WHERE) must be true

OR

At least one condition (in WHERE) must be true

NOT

Reverses a condition

N (a, b, c)

Checks if value matches any in a list

BETWEEN a AND b

Checks if value falls in a range (inclusive)

LIKE 'pattern'

Pattern matching (% = any characters, _ = one character)

IS NULL

Checks for missing values

IS NOT NULL

Checks for non-missing values

COALESCE(a, b)

Returns first non-NULL value

COUNT(column)

Counts number of rows (ignores NULLs)

COUNT(*)

Counts all rows

SUM(column)

Adds values

AVG(column)

Calculates average

MIN(column)

Finds smallest value

MAX(column)

Finds largest value

GROUP BY column

Groups rows for aggregate functions

HAVING condition

Filters grouped results (used after GROUP BY)

ORDER BY column ASC

Sorts results ascending

ORDER BY column DESC

Sorts results descending

LIMIT n

Returns only n rows

JOIN

Returns only matching rows. Aka INNER JOIN.

LEFT JOIN

Returns all rows from left table + matches

RIGHT JOIN

Returns all rows from right table + matches

ON condition

Defines how tables are joined

SELECT ... FROM (subquery)

Query inside another query

WHERE column IN (subquery)

Filters using results from another query

WHERE ≠ HAVING

WHERE filters rows, HAVING filters aggregates

NULL ≠ 'null'

Use IS NULL, never = NULL

COUNT(column) ≠ COUNT(*)

Column ignores NULLs, * does not

Error: WHERE COUNT(*) > 5

Aggregate functions cannot be used in WHERE

Use HAVING instead: HAVING COUNT(*) > 5

If you use GROUP BY, every column in SELECT must be…

• In GROUP BY, or

• Inside an aggregate function

WHERE vs HAVING

WHERE: Filters rows before grouping

HAVING: Filters groups after aggregation

WHERE age > 18       -- raw data
HAVING COUNT(*) > 5  -- aggregated data

NULL is not equal to anything — even itself.

NULL = NULL     -- ❌ false
NULL != 2       -- ❌ unknown

DISTINCT applies to the entire row, not just one column (unless only one column is selected).

SELECT DISTINCT a, b

Removes duplicate (a, b) pairs

Conditions in WHERE can turn a LEFT JOIN into an INNER JOIN

LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.id IS NOT NULL   -- removes unmatched rows

AND has higher priority than OR

a = 1 OR b = 2 AND c = 3 -- is the same as
a = 1 OR (b = 2 AND c = 3)

Use parentheses to be safe.

BETWEEN is inclusive

BETWEEN 1 AND 5 i ncludes 1 and 5

% → any number of characters
_ → exactly one character

LIKE 'A%'   -- starts with A
LIKE '%A'   -- ends with A
LIKE '_A%'  -- A is second character

Column aliases cannot be used in WHERE

SELECT salary * 12 AS annual
WHERE annual > 50000   -- ❌ invalid

Use subquery or HAVING

ORDER BY runs after SELECT, so it can use aliases

SELECT salary * 12 AS annual
ORDER BY annual   -- ✅ valid

LIMIT Applied after sorting

ORDER BY salary DESC
LIMIT 5

Top 5 highest salaries

Order of SQL execution

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

FROM A
LEFT JOIN B ON ...

• A = left table

• B = right table

Do I want to keep rows from this table even if there is NO matching row in the other table?

• YES → LEFT JOIN

• NO → INNER JOIN

EXISTS Format

SELECT c.name
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.id
);

CASE WHEN format

CASE
    WHEN o.amount >= 100 THEN 'High'
    WHEN o.amount >= 50 THEN 'Medium'
    ELSE 'Low'
END AS order_size;

Full query format template

SELECT
    c.id,
    c.name,
    COUNT(o.id) AS order_count
FROM Customers c
LEFT JOIN Orders o
    ON c.id = o.customer_id
WHERE
    c.active = 1
GROUP BY
    c.id,
    c.name
HAVING COUNT(o.id) > 3
ORDER BY
    order_count DESC
LIMIT 5;

EXISTS

Checks whether a subquery returns at least one row. Returns TRUE or FALSE.

WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.id
)

NOT EXISTS

Checks whether a subquery returns no rows.

CASE WHEN

Creates conditional logic in SQL (like if / else). Evaluates conditions top to bottom.

IF vs CASE WHEN

• Use CASE WHEN for conditional logic in queries.

• Use IF() only if you’re explicitly in MySQL and writing something quick.

PRIMARY KEY

Uniquely identifies each row in a table.
Cannot be NULL. No duplicates.

FOREIGN KEY

Column that references a primary key in another table.
Used to connect tables in joins.

INNER JOIN vs LEFT JOIN

INNER JOIN returns only matched rows.
LEFT JOIN preserves all rows from the left table.

LEFT JOIN + WHERE trap

Conditions on the joined table in WHERE can remove unmatched rows.
Use ON for joined-table filters.

ON vs WHERE

ON defines how tables match.
WHERE filters rows after the join.

NOT IN

Checks values not in a list or subquery.

Fails if subquery contains NULL.

EXISTS vs IN

EXISTS checks for presence of rows (safer with NULLs).
IN compares values directly.

Window function

Performs calculations across related rows without collapsing rows.

COUNT(*) OVER (PARTITION BY column)

GROUP BY vs WINDOW FUNCTION

GROUP BY collapses rows.
Window functions keep original rows.

CASE WHEN in aggregation

Used for conditional counts or sums.

SUM(CASE WHEN condition THEN 1 ELSE 0 END)

Conditional aggregation

Counts or sums based on conditions inside aggregates.

SELECT DISTINCT vs GROUP BY

DISTINCT removes duplicate rows.
GROUP BY aggregates data.

Ambiguous column

Occurs when column name exists in multiple tables.
Fix with table.column.

Alias best practice

Always alias tables in joins.
Use alias.column everywhere.

LIMIT without ORDER BY

Results are not guaranteed to be consistent.

TOP vs LIMIT

TOP → SQL Server
LIMIT → MySQL / PostgreSQL

COUNT(*) with LEFT JOIN

Counts all rows, including those with NULL joined values.

COUNT(column) with LEFT JOIN

Counts only non-NULL values from the joined table.

Scalar subquery

Subquery that returns exactly one value.

SELECT (SELECT MAX(salary) FROM Employees)

Correlated subquery

A subquery that uses values from the outer query and runs once per outer row. Like a join that runs one row at a time.

SELECT c.name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.id
);

LENGTH(column)

Returns the number of characters in a string. Filter questions (“names longer than 10 chars”)

LOWER(column)

Converts text to lowercase.

UPPER(column)

Converts text to uppercase.

TRIM(column)

Removes leading and trailing spaces. Ex. “ Zoe “ becomes “Zoe”.

SUBSTRING(column, start, length)

Extracts part of a string.

CONCAT(a, b)

Joins strings together.

CONCAT(first_name, ' ', last_name)

CURRENT_DATE

Returns today’s date.

NOW()

Returns current date and time.

DATEDIFF(date1, date2)

Returns number of days between dates.

DATEDIFF(end_date, start_date)

Ex. “Users active in last 30 days”

YEAR(date)

Extracts year from a date.

MONTH(date)

Extracts month from a date.

ROUND(number, decimals)

Rounds a number. Ex. ROUND(price, 2)

ABS(number)

Returns absolute value. Ex. ABS(balance)

NULLIF(a, b)

Returns NULL if a = b, else returns a.

NULLIF(total, 0)

!= vs <>

Both mean “not equal.”

salary != 50000
salary <> 50000

LIKE vs =

= matches exact value

LIKE matches patterns

REPLACE(column, old, new)

Replaces all occurrences of a substring. Used for cleaning formatted text.

REPLACE(phone, '-', '')

CAST(value AS type)

Converts a value to a different data type.

CAST(price AS INT)

FLOOR()

rounds down

CEILING()

rounds up

ORDER BY multiple columns

Sort priority matters.

ORDER BY score DESC, name ASC

LIMIT vs. OFFSET

LIMIT = how many rows to return
OFFSET = how many rows to skip first

OFFSET format template

SELECT *
FROM Users
ORDER BY id
LIMIT 5 OFFSET 5;

• Skip 5, take 5

• Rows: 6–10