SELECT e1.name AS employee, e2.name AS manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Use different aliases for same table

Join table to itself using foreign key

LEFT JOIN for optional relationships (CEO has no manager)

Can use INNER JOIN if all rows have relationships

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

FOREIGN KEY

Enforces referential integrity by requiring a matching row in another table.

FOREIGN KEY (department_id) REFERENCES departments(department_id)

COUNT

Counts rows or non-NULL values depending on the argument.

COUNT(*)

GROUP BY

Collects rows into groups so aggregate functions can compute one result per group.

GROUP BY department_id

HAVING

Filters groups after aggregation has been computed.

HAVING COUNT(*) > 5

DISTINCT

Removes duplicate values from a projection or aggregate input set.

COUNT(DISTINCT customer_id)

ROWS / RANGE

Defines how a window frame is sliced around the current row.