Subqueries — whereIn with subquery, fromSub()

Advanced5 min read·lv-13-006

sql

Concept

Subqueries embed one query inside another, allowing complex data access patterns that would otherwise require multiple round-trips or application-level merging.

whereIn with subquery: Filter rows where a column matches values from another query.

php

->whereIn('user_id', fn($q) => $q->from('admins')->select('user_id'))

fromSub(Closure|Builder, string $alias): Use a subquery as the table in FROM. Allows operating on pre-aggregated data.

selectSub(Closure|Builder, string $alias): Embed a subquery as a selected column. The subquery must return a single row/column.

whereExists(Closure): Correlated subquery returning WHERE EXISTS (SELECT ...). More efficient than whereIn for large sets because it short-circuits.

whereNotExists(Closure): Inverse — rows where the subquery returns no results.

Correlated subqueries: Subqueries that reference the outer query's table. Used in whereExists, selectSub, and whereRaw. The inner query runs for each outer row.

Performance notes:

whereIn(subquery) with many values: the DB computes the inner query once, then filters. Usually fine.
whereExists: More efficient when the inner set is large, because it stops at first match.
selectSub (correlated): Runs once per outer row — can be N+1 in SQL. Use with caution on large datasets; consider JOINs instead.

Code Example

php

<?php
use Illuminate\Support\Facades\DB;

// whereIn with subquery — users in the 'premium' plan
$premiumUsers = DB::table('users')
    ->whereIn('id', function($q) {
        $q->from('subscriptions')
          ->select('user_id')
          ->where('plan', 'premium')
          ->whereNull('cancelled_at');
    })
    ->get();

// whereNotIn with subquery — users who have never ordered
$neverOrdered = DB::table('users')
    ->whereNotIn('id', function($q) {
        $q->from('orders')->select('user_id')->distinct();
    })
    ->get();

// whereExists — users with at least one completed order (efficient)
$buyers = DB::table('users')
    ->whereExists(function($q) {
        $q->from('orders')
          ->whereColumn('orders.user_id', 'users.id')
          ->where('orders.status', 'completed');
    })
    ->get();

// fromSub — query a derived table
$averagesByCategory = DB::table(function($q) {
    $q->from('products')
      ->select('category', DB::raw('AVG(price) as avg_price'))
      ->groupBy('category');
}, 'category_averages')
->where('avg_price', '>', 50)
->get();

// selectSub — embed correlated subquery as column
$users = DB::table('users')
    ->select('id', 'name')
    ->selectSub(function($q) {
        $q->from('orders')
          ->selectRaw('COUNT(*)')
          ->whereColumn('user_id', 'users.id');
    }, 'order_count')
    ->selectSub(function($q) {
        $q->from('orders')
          ->selectRaw('SUM(total)')
          ->whereColumn('user_id', 'users.id');
    }, 'total_spent')
    ->get();
// Caution: 2 correlated subqueries run per user row

Previousjoins — join(), leftJoin(), rightJoin(), crossJoin()Query Builder NextgroupBy(), having(), havingRaw()Query Builder