Laravel 13 Advanced Query Builder: Subqueries and SQL Expressions

With whereExists, selectSub, and joinSub, Laravel 13's query builder handles reports like "customers without orders this month" or "each customer's latest order" in a single query, without N+1 problems or messy raw SQL.

What the Advanced Query Builder Can Do for You

Laravel's query builder is the fluent API for working with the database: you start with DB::table('users') and chain methods to build almost any query, on every engine the framework supports. When a listing gets complicated, the temptation is to hand-write SQL or run one query per row; subqueries exist so you do not have to do either.

Beyond Basic SELECTs: DB::table's Fluent API

The fluent API is not just for simple SELECTs. Every method can nest inside another: where you once needed a separate query in PHP, you now pass a closure or a builder as an argument and Laravel generates the subquery for you. The result is readable code that follows the framework's syntax instead of scattered SQL fragments.

The Problems Subqueries Solve: N+1 and Duplicated Queries

The classic mistake is the N+1: load a hundred customers and run an extra query per row to find each one's latest order. With subqueries, that information travels in the same main query. You also eliminate duplicated logic when the same aggregate appears in several places: define it once and reuse it.

Subqueries in the SELECT: selectSub and Aggregates

The most common pattern is adding a computed column to the SELECT.

Adding a Computed Column with selectSub

With selectSub you pass a closure that defines the subquery and the alias of the column: for example, the date of each customer's latest order. Laravel turns it into a subquery inside the SELECT and the result arrives as another column of the row. It is the declarative way to solve "the most recent of each group" without raw SQL.

Eloquent Sugar: withSum, withCount, withAvg, and withMax

When you work with models and relationships, Eloquent offers shortcuts for the most common aggregates: withSum, withCount, withAvg, withMax, and withMin add the computed column without writing the subquery by hand. They are ideal for listings with totals: each customer's accumulated spending or the number of orders per category comes out of a single query.

ofMany: Each Customer's Latest (or Most Expensive) Order

To keep the full record holding the maximum or minimum value of a column within each group, use ofMany on the relationship: it brings each customer's most recent order, or the most expensive one, with all its fields. If you also order by date, latestOfMany does exactly that with "latest" semantics.

Filtering with Subqueries: whereExists and whereNotExists

When the filter depends on the existence of related records, these two clauses are the answer.

Example: Customers Without Orders This Month

whereNotExists takes a closure with the relationship query and Laravel generates the NOT EXISTS clause: rows for which no order exists this month. Its counterpart whereExists does the same in positive, for example customers with at least one purchase in the last thirty days. Both avoid the counting JOIN and duplicated rows.

whereExists with Closures and Conditions Inside the Subquery

Inside the closure you have a full builder: you can filter by dates, limit, order, or add more conditions. That flexibility turns whereExists into the tool for business queries that seemed to need hand-written SQL, while keeping all the chaining and parameter binding.

joinSub: Joining with a Subquery

It is not only about filtering rows: sometimes you want to join already preprocessed data.

A Derived Table as a Join Alias

joinSub takes a closure, an alias, and the ON conditions, and treats the subquery result as another table: a derived table. It is the pattern for pre-aggregating before joining, for example totals per category computed only over paid orders, then crossed with the categories table.

When to Use joinSub Instead of a Real Table

Use it when the real table does not have the shape you need: aggregating, filtering, or transforming columns before the JOIN avoids pulling extra rows or running heavy calculations afterward. If the same derived table repeats across several queries, consider a view or a dedicated model; for a one-off report, joinSub is simpler.

A Subquery as the Base Table

The same trick works for the FROM clause.

from with a Closure: Querying the Result of Another Query

Instead of a table name, from accepts a subquery with its alias: you query the result of another query. It is useful when you need filters and aggregates at two levels, for example totals over an already filtered set, without creating temporary tables or views just for one report.

Raw SQL Expressions: When and How

Sometimes the fluent API does not cover a database function; that is where raw expressions come in, with their rules.

selectRaw, whereRaw, orderByRaw, and havingRaw

selectRaw adds computed columns with SQL, whereRaw and havingRaw filter with native conditions, and orderByRaw sorts with expressions the API cannot translate. Use them for engine-specific functions or complex ordering logic, always with the smallest possible surface and surrounded by the fluent API.

Security: PDO Parameter Binding and Why You Should Never Concatenate

The query builder uses PDO parameter binding: values passed as bindings are escaped automatically, so you do not need to sanitize them by hand. The risk appears when you concatenate user input inside a raw expression; in that case you are building SQL with unsanitized input. Fixed rule: values as bindings, never glued into the string.

DB::raw for Database Functions the API Doesn't Cover

DB::raw wraps any SQL fragment so you can use it inside another method. If an engine function does not exist in the fluent API, this is the correct escape hatch; if you only need a simple computed value, prefer selectRaw or Eloquent aggregates so raw SQL does not spread through the codebase.

Unioning Queries

To combine results from several queries into a single response, the query builder offers union and unionAll. The first removes duplicates and the second keeps them, which is usually faster. It is the pattern for listings that mix different sources, like events from two separate tables ordered by date, keeping the same number of columns.

Performance: Efficient Subqueries and EXPLAIN

A well-written subquery is fast, but it is worth verifying against the execution plan.

Correlated vs Non-Correlated Subqueries

Non-correlated subqueries run once and their result is reused; correlated ones depend on each row of the outer query and can get slow with large tables. When you can choose, frame the problem as a non-correlated one or as an aggregate in the SELECT, which the optimizer handles better.

Indexing WHERE and ON Columns, and Checking the Plan with EXPLAIN

Before considering a query done, run EXPLAIN and check whether the columns in the WHERE and ON clauses are indexed. A missing index turns an elegant subquery into a full scan; a well-placed one is usually the difference between milliseconds and seconds in reports.

Conclusion

Laravel 13's advanced query builder turns reports that seemed to demand raw SQL into declarative, safe queries: selectSub and Eloquent aggregates for computed columns, whereExists for existence filters, joinSub for derived tables, and unions to combine results, with PDO binding protecting every value. Write your first whereNotExists query this week and you will feel the difference. Keep reading the blog for more Laravel 13 tutorials.