Joins, Subqueries, Correlation and Relational Query Design

inner join

An inner join returns matching row combinations satisfying its join condition and omits unmatched rows from both inputs.

outer join

An outer join preserves unmatched rows from a designated side and supplies NULL for columns belonging to the missing match.

cross join

A cross join returns the Cartesian product, pairing every row from one input with every row from the other.

self join

A self join uses separate aliases of the same table to relate its rows to other rows in that table.

subquery

A query nested within another SQL statement and used as a scalar value, table, existence test or comparison input.

correlated subquery

A correlated subquery refers to columns of an outer query and is logically evaluated in the context of each candidate outer row.

EXISTS

EXISTS is true when its correlated or uncorrelated subquery returns at least one row.

EXISTS is true when its subquery returns at least one row:

SELECT c.customer_id, c.customer_name FROM customer AS c WHERE EXISTS ( SELECT 1 FROM order_header AS o WHERE o.customer_id = c.customer_id );

The selected expression inside EXISTS does not determine truth; only row presence matters. SELECT 1 is a convention and does not refer to a column named 1.

EXISTS acts like a semi-join. Multiple matching orders do not duplicate the customer because the outer row receives one Boolean result.

IN and NOT IN

SQL membership predicates that compare a value with a list or subquery result; NULL requires careful three-valued logic.

join cardinality

The number of rows produced by a join, determined by input sizes, key uniqueness, matching frequency and filter selectivity.

query design

The choice of relations, predicates, joins, grouping and projection needed to express the required result correctly and efficiently.

Correlated Subqueries

A correlated subquery refers to a value from an outer query row:

SELECT e.employee_id, e.employee_name FROM employee AS e WHERE e.salary > ( SELECT AVG(x.salary) FROM employee AS x WHERE x.department_id = e.department_id );

The inner average is logically associated with the current outer employee's department. Aliases make the scope of each reference explicit.

Correlation describes semantics. The optimizer may transform the query into a join or another strategy rather than literally rerunning it once per row.

Subquery or Join

Several questions can be expressed either way. Use EXISTS for presence, a join for returning related columns and pre-aggregation when the many side must become one row per key.

Compare semantics before rewriting. A join may multiply outer rows, while EXISTS does not. A left join plus IS NULL can express an anti-join, but NULL tests must use a nonnullable matched key.

Performance depends on schema, indexes, statistics and optimizer transformations. Choose the clearest correct form, then inspect the execution plan.

NOT EXISTS

NOT EXISTS is true when no qualifying subquery row exists:

SELECT d.department_id, d.department_name FROM department AS d WHERE NOT EXISTS ( SELECT 1 FROM employee AS e WHERE e.department_id = d.department_id );

This finds departments without employees and expresses an anti-join directly.

It is generally safer than NOT IN when the subquery column may contain NULL. NOT EXISTS tests row matches, while NOT IN can become UNKNOWN because of one NULL among its comparison values.

Outer Joins

An outer join retains unmatched rows from a chosen side and supplies NULL for missing columns.

A left outer join preserves all left rows:

SELECT d.department_id, d.department_name, e.employee_name FROM department AS d LEFT JOIN employee AS e ON e.department_id = d.department_id;

A department with no employee still appears once with NULL employee columns.

A right join preserves all right rows. It can usually be rewritten as a left join by swapping table order. A full outer join preserves both sides, though MySQL does not provide a direct FULL OUTER JOIN keyword and requires an intentional combination of operations.

ON Versus WHERE in Outer Joins

For an inner join, moving a related filter between ON and WHERE often yields the same rows. For an outer join it can change meaning.

FROM department AS d LEFT JOIN employee AS e ON e.department_id = d.department_id AND e.status = 'ACTIVE'

This preserves every department and matches only active employees.

If e.status = 'ACTIVE' is placed in WHERE, unmatched rows have NULL status and the predicate becomes UNKNOWN. They are removed, making the result behave like an inner join for that condition.

Use ON for restrictions on which right rows qualify as matches when unmatched left rows must remain. Use WHERE to filter the result after preservation.

Indexes for Correlation and Existence

Correlated predicates often compare a child foreign key with an outer parent key:

WHERE e.department_id = d.department_id

An index on employee(department_id) can make matching-row discovery efficient. Composite indexes should follow all important equality and range predicates.

EXISTS can stop logically after finding a match, but actual cost still depends on access paths and estimates. Inspect EXPLAIN rather than assuming every subquery is slow or every join is fast.

Inner Joins

INNER JOIN returns only matching row combinations. Rows on either side without a match are absent.

SELECT o.order_id, l.product_id, l.quantity FROM order_header AS o JOIN order_line AS l ON l.order_id = o.order_id;

JOIN without a modifier ordinarily means INNER JOIN. Several tables can be joined in sequence, but every join should have a clear relationship predicate and alias.

The older comma syntax places tables in FROM and join predicates in WHERE. Explicit JOIN separates relationship conditions from filtering and reduces accidental products.

Multirow Subqueries with IN

IN compares a value with the one-column result of a subquery:

SELECT employee_id, employee_name FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE region = 'WEST' );

Duplicate subquery values do not change IN's truth result. NULL can still affect three-valued logic when no equality match exists.

Use IN when membership is the natural question. A join may be preferable when columns from the matching rows are needed.

Queries Inside Queries

A subquery is a SELECT nested inside another SQL statement. Its permitted result shape depends on where it appears. A scalar context requires one value, IN accepts a one-column set, EXISTS tests row presence and FROM accepts a table-shaped result.

Subqueries help express a larger question in stages:

SELECT employee_id, employee_name, salary FROM employee WHERE salary > ( SELECT AVG(salary) FROM employee );

The inner query computes the company average and the outer query selects employees above it.

Join as Product and Selection

A theta join combines rows that satisfy a general condition. Conceptually it is a Cartesian product followed by selection.

An equijoin uses equality:

SELECT e.employee_name, d.department_name FROM employee AS e INNER JOIN department AS d ON d.department_id = e.department_id;

The join condition relates keys with the same meaning. Equal data types alone do not make columns valid join partners.

The result grain depends on relationship cardinality. If a department has many employees, one department row contributes to several joined rows.

Safe Query Reasoning

For each subquery, state its shape: scalar, one-column multirow or table. Identify outer references and confirm their aliases. Prove scalar uniqueness rather than relying on current data.

For absence, prefer NOT EXISTS when nullable values are possible. For views, document output columns, updatability, check-option behavior and security context. For temporary tables, define lifetime, cleanup and reuse.

Nested SQL is reliable when every level has a clear row grain, correlation key and result contract. The syntax may be nested, but the reasoning should remain explicit.

Building Correct Multi-Relation Queries

Begin by stating the desired result grain. List the tables needed and identify the key relationship for every join. Use explicit JOIN syntax and complete composite keys. Decide whether unmatched rows must survive before choosing inner or outer join.

Place match restrictions in ON and final result restrictions in WHERE according to intended outer-join behavior. For set operations, align column counts, meanings and compatible types. Use UNION ALL unless duplicate removal is required.

Finally, inspect table-copy results and re-create every required constraint and index. Relational operations are composable, but correctness depends on preserving identity and understanding how each stage changes row cardinality.

Natural Joins

NATURAL JOIN automatically equates columns having the same names in both inputs and usually emits one copy of each matched column.

This convenience is fragile. Adding an unrelated same-named column later silently changes the join condition. Columns with different names but the same meaning are not joined.

Production queries should state relationships explicitly with ON or, when exactly appropriate, USING:

JOIN department USING (department_id)

Explicit joins make review and schema evolution safer.

Uncorrelated Subqueries

An uncorrelated subquery does not refer to the surrounding query:

SELECT * FROM product WHERE price = (SELECT MAX(price) FROM product);

Conceptually, it can be evaluated independently. The optimizer decides whether to compute it once, transform it, cache it or combine it into another plan.

Do not infer physical execution solely from the written nesting. SQL states a logical relationship, not a procedural loop.

Join Cardinality Reasoning

Use declared keys to predict cardinality:

• many-to-one: each valid child matches at most one parent;
• one-to-many: each parent can yield several child combinations;
• one-to-one: each side matches at most one;
• many-to-many: both sides can multiply through an associative relation.

Compare expected and actual row counts at intermediate stages. Unexpected multiplication often indicates an incomplete key condition or a join performed at the wrong grain.

Temporary Table Design

Temporary tables are useful when:

• intermediate results are reused across statements;
• a process has several procedural stages;
• an index on materialized intermediate rows helps later work;
• the result needs independent manipulation.

They also incur creation, storage and population work. They can complicate connection pooling because session lifetime may outlast one logical request.

Do not assume materialization is automatically faster than one well-optimized query. Measure representative data and inspect plans.

Derived Tables

A subquery in FROM is a derived table and must have an alias in MySQL:

SELECT d.department_name, x.mean_salary FROM department AS d JOIN ( SELECT department_id, AVG(salary) AS mean_salary FROM employee GROUP BY department_id ) AS x ON x.department_id = d.department_id;

The derived table has its own columns and row grain. Here it produces one row per department before the outer join.

The optimizer may merge or materialize it according to query rules and cost.

Views

A view is a named stored query:

CREATE VIEW active_customer AS SELECT customer_id, customer_name, email FROM customer WHERE status = 'ACTIVE';

Applications can query the view as a table. An ordinary view principally stores its definition, not an independent permanent copy of every result row.

Views can simplify repeated joins, expose a stable interface, hide columns and restrict rows. They do not automatically improve performance; the underlying query still has to be planned and executed.

Duplicate Semantics

Relational set operations remove duplicates by definition, while SQL distinguishes duplicate-eliminating and ALL variants.

Join multiplicity is not accidental duplication when several relationships truly exist. One order joined to four lines should produce four rows at line grain.

Before adding DISTINCT, state what one result row represents and which columns define identity. DISTINCT can conceal a missing join condition without repairing the query's meaning.

Intersection and Difference

Intersection returns rows present in both compatible results. Set difference returns left rows absent from the right.

In SQL products supporting them, the keywords are INTERSECT and EXCEPT; some products use MINUS for difference. Availability varies by MySQL version, so equivalent logic often uses EXISTS or NOT EXISTS.

SELECT c.customer_id FROM customer AS c WHERE NOT EXISTS ( SELECT 1 FROM order_header AS o WHERE o.customer_id = c.customer_id );

NOT EXISTS handles nullable comparison columns more predictably than NOT IN.

Subqueries in the Select List

A scalar subquery may produce an output value:

SELECT d.department_id, d.department_name, ( SELECT COUNT(*) FROM employee AS e WHERE e.department_id = d.department_id ) AS employee_count FROM department AS d;

This is readable for a small number of metrics. Several correlated aggregates may repeat related work. A grouped derived table joined once can be clearer and easier to optimize.

Common Table Expressions

A common table expression gives a query result a name for one statement:

WITH department_pay AS ( SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS mean_salary FROM employee GROUP BY department_id ) SELECT d.department_name, p.employee_count, p.mean_salary FROM department AS d JOIN department_pay AS p ON p.department_id = d.department_id;

CTEs can improve readability and can be referenced where allowed. A recursive CTE can traverse hierarchies or generate sequences by combining an anchor query with a recursive member.

A CTE's written form does not guarantee materialization; inspect the plan when performance matters.

Semi-Joins and Anti-Joins

A semi-join returns left rows for which at least one right match exists, without returning or multiplying by right columns. SQL normally expresses it with EXISTS:

An anti-join returns left rows with no match and uses NOT EXISTS. These forms state existence directly and avoid duplicates that a join plus DISTINCT could create.

Updating and Deleting Through Joins

MySQL supports multi-table or joined update and delete forms. Their power increases risk because join multiplication and predicates determine affected rows.

First write a SELECT returning target primary keys. Verify cardinality, duplicate target matches and outer-join meaning. Then apply the modification in a transaction when supported and inspect affected rows.

Never assume LIMIT or observed display order identifies “the first” matching row without a deterministic order and supported statement semantics.

INSERT SELECT

INSERT ... SELECT copies query results into an existing compatible table:

INSERT INTO order_archive (order_id, customer_id, ordered_at, total_amount) SELECT order_id, customer_id, ordered_at, total_amount FROM order_header WHERE ordered_at < '2025-01-01';

The target's constraints, defaults, generated columns and conversions apply. The operation does not automatically delete source rows. An archival move needs a transaction and carefully ordered deletion where engine and design permit.

Relational Algebra

Relational algebra is a formal collection of operations that take relations as input and produce relations as output. Because results are relations, operations can be composed into larger expressions.

Classical relational algebra uses set semantics: duplicate tuples are absent and tuple order is irrelevant. SQL adopts many relational ideas but usually works with duplicate-preserving results unless DISTINCT or a set operation removes duplicates. SQL also includes NULL and three-valued logic.

Relational algebra helps explain what a query means independently of a particular execution plan.

Cartesian Product

The Cartesian product pairs every row of one relation with every row of another. If A has m rows and B has n rows, A × B contains m × n pairs.

SELECT * FROM department CROSS JOIN location;

Four departments and three locations produce twelve combinations. A cross product is correct when every combination is required, such as constructing a scheduling grid.

An accidental missing join condition can create a huge cross product and duplicate facts throughout a query.

Scalar Subqueries

A scalar subquery must return at most one row and exactly one column:

SELECT product_name, price, (SELECT AVG(price) FROM product) AS mean_price FROM product;

One returned row supplies its value. Zero rows produce NULL. More than one row causes an error because SQL cannot choose one value arbitrarily.

An aggregate without GROUP BY is often useful because it returns one summary row even over empty input. A nonaggregate scalar subquery needs a uniqueness guarantee, such as a predicate on a candidate key.

Composite-Key Joins

When identity uses multiple columns, join on the complete key:

SELECT * FROM shipment_item AS s JOIN order_line AS l ON l.order_id = s.order_id AND l.line_number = s.line_number;

Joining only on order_id pairs each shipment item with every line of the order and creates false combinations. Foreign-key definitions and join conditions should align.

Views and Security

A view can expose selected columns and rows while direct base-table access is withheld. This supports least-privilege design:

GRANT SELECT ON sales.active_customer TO report_user;

However, a view does not replace complete authorization. Definer and invoker security context, underlying privileges, functions, indirect inference and administrative rights all matter.

Sensitive data may still be inferred from counts or combinations. Security requires roles, privilege review, controlled routines, auditing and application access policies together.

Updatable Views

Some simple views map each view row directly to one base-table row and can support INSERT, UPDATE or DELETE. Views involving aggregation, GROUP BY, DISTINCT, UNION or many complex joins generally cannot map an update unambiguously.

Updatability is governed by exact MySQL rules. A view being queryable does not imply that every column can be modified.

Even for an updatable view, base-table constraints and privileges remain relevant.

CREATE TABLE AS SELECT

CREATE TABLE high_value_order AS SELECT order_id, customer_id, total_amount FROM order_header WHERE total_amount >= 10000;

CREATE TABLE ... AS SELECT creates a table from the query's result columns and copies result rows. It does not automatically reproduce every primary key, foreign key, index, default, generated property or constraint from source tables.

Inspect and add required schema metadata after creation. A query-result copy is a new physical snapshot, not a synchronized view.

Views, Derived Tables, CTEs and Temporary Tables

These constructs differ in lifetime and storage:

• a view is a persistent schema object containing a query definition;
• a derived table exists as part of one statement;
• a CTE is a named expression scoped to one statement;
• a temporary table is a session-scoped table containing intermediate rows.

Use a view for a reusable logical interface, a derived table or CTE for statement structure and a temporary table when stored intermediate state is genuinely needed across operations.

View Column Names

Output columns should have stable unique names:

CREATE VIEW order_summary AS SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_amount FROM customer AS c LEFT JOIN order_header AS o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name;

Explicit aliases become the view interface. Changing them can break dependent queries even when underlying tables remain unchanged.

Avoid SELECT * in durable view definitions because added base columns can alter exposed structure.

Temporary Tables

A temporary table stores intermediate rows for the current session:

CREATE TEMPORARY TABLE recent_order AS SELECT order_id, customer_id, total_amount FROM order_header WHERE ordered_at >= CURRENT_DATE - INTERVAL 30 DAY;

It can be indexed and queried by several subsequent statements. It is normally removed when the session ends and can be dropped earlier explicitly.

Temporary table scope is not the same as transaction scope. A commit does not necessarily remove it.

AUTO_INCREMENT

MySQL AUTO_INCREMENT generates numeric surrogate identifiers:

CREATE TABLE ticket ( ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200) NOT NULL );

Omitting ticket_id requests generation. LAST_INSERT_ID() can report the generated value for the current connection under its defined semantics.

Generated numbers can have gaps and should not encode row count or legal document sequencing. They provide identity, not consecutive business numbering.

When copying rows, decide whether to preserve existing identifiers or generate new ones. New identifiers require remapping every related foreign key.

Rename

Rename gives a relation or its attributes another name. It is essential when the same relation appears more than once.

SQL aliases perform this role:

SELECT e.employee_name, m.employee_name AS manager_name FROM employee AS e LEFT JOIN employee AS m ON m.employee_id = e.manager_id;

Aliases create separate references to the same table. They do not copy data.

Copying a Table Definition

MySQL can create a new table similar to an existing one:

CREATE TABLE archived_order LIKE order_header;

CREATE TABLE ... LIKE copies much of the table definition, including columns and indexes, subject to documented limitations. It does not copy rows:

INSERT INTO archived_order SELECT * FROM order_header WHERE ordered_at < '2025-01-01';

Name columns explicitly when schemas may evolve or column order is not guaranteed to remain aligned.

UNION ALL

UNION ALL concatenates compatible results without duplicate elimination:

SELECT event_time, 'ORDER' AS source FROM order_event UNION ALL SELECT event_time, 'PAYMENT' FROM payment_event;

If the same row appears in both operands, both copies remain. This often matches event streams and is usually cheaper than UNION.

ORDER BY for the combined result belongs after the final operand. Parenthesized subqueries are needed when individual operand ordering or limits have a specific purpose.

Self-Joins

A self-join relates rows in one table to other rows of the same table:

Aliases distinguish the employee role from the manager role. The left join retains top-level employees whose manager_id is NULL.

Self-joins also support predecessor relationships, category hierarchies and pair comparisons. Recursive queries are more appropriate for unknown hierarchy depth.

Union

UNION combines compatible query results and removes duplicate rows:

SELECT email FROM customer UNION SELECT email FROM supplier;

Each operand must return the same number of columns in corresponding positions with compatible types. Output column names ordinarily come from the first query.

Duplicate removal requires additional work. Use UNION ALL when duplicates are meaningful or the inputs are known to be disjoint.

ANY and ALL

ANY, also written SOME, asks whether a comparison is true for at least one subquery value:

salary > ANY (SELECT salary FROM employee WHERE department_id = 10)

ALL asks whether it is true for every value:

salary > ALL (SELECT salary FROM employee WHERE department_id = 10)

Empty-set behavior follows logic: a comparison with ANY over no values is false, while a comparison with ALL over no values is true. NULL values can produce UNKNOWN, so the input domain should be understood.

Comparisons to MIN or MAX sometimes express the same intent more clearly, but empty and NULL behavior must be checked before rewriting.

WITH CHECK OPTION

Consider a view exposing active customers:

CREATE VIEW active_customer AS SELECT customer_id, customer_name, status FROM customer WHERE status = 'ACTIVE' WITH CHECK OPTION;

The check option prevents an insert or update through the view from creating a row that no longer satisfies its predicate. Without it, an update could make a row disappear from the view immediately after modification.

LOCAL and CASCADED behavior matters when views are built on other views. Use the required option and understand every underlying predicate.

Projection

Projection chooses attributes and is commonly written with pi:

π department_id, employee_name (EMPLOYEE)

Its SQL counterpart is the select list:

SELECT department_id, employee_name FROM employee;

Classical projection removes duplicate tuples because relations are sets. SQL SELECT normally preserves duplicates, so SELECT DISTINCT is closer when set projection semantics are required.

Projection should include the identifiers needed to distinguish results. Selecting only a nonunique label may intentionally collapse identities under DISTINCT.

Selection

Selection chooses tuples satisfying a predicate. It is commonly written with the Greek sigma:

σ status = 'ACTIVE' (EMPLOYEE)

The SQL counterpart is WHERE:

SELECT * FROM employee WHERE status = 'ACTIVE';

Selection changes the number of rows but retains the relation's attributes. Several conditions may be combined with logical operators. In SQL, NULL can make a predicate UNKNOWN, which WHERE removes along with FALSE.