-- One-to-Many:

CREATE TABLE orders (

order_id INTEGER PRIMARY KEY,

customer_id INTEGER NOT NULL,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

-- Many-to-Many:

CREATE TABLE enrollments (

student_id INTEGER NOT NULL,

course_id INTEGER NOT NULL,

PRIMARY KEY (student_id, course_id),

FOREIGN KEY (student_id) REFERENCES students(student_id),

FOREIGN KEY (course_id) REFERENCES courses(course_id)

);

One-to-Many: FK in "many" side

One-to-One: FK with UNIQUE constraint

Many-to-Many: Junction table with two FKs

Self-Referencing: FK references same table PK

CASCADE options control delete/update behavior

Full foreign key support, CASCADE options

InnoDB engine required for foreign keys

Full foreign key support with CASCADE

Core references in this topic include WHERE, =, IS NULL / IS NOT NULL. 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.

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

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.