SELECT s.name, c.name FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id;

Junction table contains foreign keys to both entities

Requires two joins to connect entities

Junction table can have additional metadata

Composite primary key on both foreign keys

Index both foreign keys separately

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)

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY

DISTINCT

Removes duplicate values from a projection or aggregate input set.

COUNT(DISTINCT customer_id)

Junction table contains foreign keys to both entities

Requires two joins to connect entities

Junction table can have additional metadata

Composite primary key on both foreign keys

Index both foreign keys separately

Name junction table clearly: student_courses or enrollments

Add composite primary key on both foreign keys

Index both foreign keys

Add metadata columns (enrollment_date, status)

Use meaningful column names